I have a list of numbers (2000 lines). I need to count the numbers of the values in different ranges. eg <= $10.00, $10.01 to $100.00, $100.01 to $500.00, etc. The formula =COUNTIF(H2:H2001,"<=10") works. It gives me 257. But for the next range, formula =COUNTIF(H2:H2001,AND(">10","<=100")) doesn't work. What's the problem? How can I get this to work? TIA Try the formula =COUNTIF(H2:H2001,">=10")-COUNTIF(B2:B5,">100") "Gordon" wrote: > I have a list of numbers (2000 lines). I need to count...

Hello Everyone, I am looking for a formula to count unique occurances of column (A) where column (B) equals a certain value. Any help would be appreciated. I have the formula =SUMPRODUCT(('data'!A3:A6<>"")/COUNTIF('data'!A3:A6,'data'!A3:A6 &"")), which gives me a count of the unique values in 'data'!A, now I would like to break it down over a value in 'data'!B the result would look like this: smith, john, blue smith, john, red jones, james, yellow jones, james, green williams, john, orange williams, john, teal Uniq...

Hoping someone has an idea on this one. I recently upgraded a clients small network (4 comps) to OS X 10.5 The machines were a mix of 10.3 and 10.2 machines. I cloned all machines to ext hard drives and did an erase and install of Leopard on each. While there were certainly a few issues around the upgrade, mostly everything went fine...then the client tried to open his excel files, consisting of client databases and other essential information. No bueno. The excel files were stripped of their extensions at some point and finder calls them "unix executable files." Excel will not...

I have an excel file that has selection boxes (boxes set up as lists using the data validation feature). I have set conditional formatting to hide certain selection boxes based on cell values. The problem is that the conditional formatting does not automatically recalculate as needed when cell values change. Any thoughts on how to fix this? ...

Hi. I am working on a worksheet witch draws data from another application. All drawn data appears fine on the worksheet but the date columns. They are left aligned (instead of the deafult right) and if I try to format them in a different way by selecting them right-clicking and choosing Format Cells but nothing happens. Even if I try to format them in any other option given nothing happens. The cells remain in the dd/mm/yyyy format that they are (and right aligned of course). What can I do? I am at a dead end here. I need them to show up as dates so I can use a sumproduct function on t...

I'm setting up a conditional formula to use in a conditional format, where depending on a couple of different conditions, the cell with the date entered will be color-coded either red, yellow or green. In this particular formula, I'm using the following variables: - cell C7 = a percentage (70%) - cell CI7 = the number of days calculated from today (19) I've constructed the formula as such (makes sense to me, but Excel doesn't like it) =IF(AND(CI7<=30,D7>=70%),(OR(AND(CI7>=90,D7<=50%)))). Essentially, I'm asking Excel to evaluate two conditi...

Is there a way to conditionally format a cell based on the value in another cell? i.e.: If B1=>0, A1 formats to green or any other chosen format. Thanks of any suggestions! Dewayne Sure. Select A1. Format > Conditional Formatting. Select "Formula Is" on the drop-down and put: =B1>=0 Click the "Format" radio button and format as desired. HTH Jason Atlanta, GA >-----Original Message----- >Is there a way to conditionally format a cell based on the >value in another cell? i.e.: > >If B1=>0, >A1 formats to green or any other chosen forma...

I don't understand your question. Does this have anything to do with Excel? -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "Angie" <Angie@discussions.microsoft.com> wrote in message news:8B85D26B-00F0-4FF8-8716-9B37ED3CBBFA@microsoft.com... > ...

Hi Guys, Sorry if I sound real stupid but is there anyway that we can control the column formatting in Excel 97 like let's say column A = GENERAL(6), column B = GENERAL(4), column C = TEXT(18) etc? Appreciate any form of advice, thanks!! I replied in the programming group. Are you seeking a programming answer? It's usually best to only post to one group, and include the remark "Please tell me if I should ask this in another group." On Thu, 28 Aug 2003 00:56:47 -0700, "Daryl" <daryl.ho@tnt.com> wrote: >Hi Guys, > >Sorry if I sound real stupid b...

I want to add the sales (column B) of the people in column A. A B James 23 Randy 43 Paul 12 James 40 Paul 50 ....and get this result: C D James 63 Randy 43 Paul 62 Only the result in column D needs to be calculated. Any help much appreciated. Since you state that: "Only the result in column D needs to be calculated" I'll assume that you have already entered the list of unique names in Column C. So, in D1 enter this: =Sumif(A$1:A$5,C1,B$1:B$5) And copy down. -- HTH, RD ---------------------------------...

in Access 2007 I some report fields that are to display quantities and amounts the range of these numbers could be large (100,000 or $345,999) or small (0.375 or $0.0625) is there a way to have the field formatted to adapt to the range of the value being displayed for example if the value has no significant decimals then display the value without decimal places (512.00 becomes 512) or if the value has many decimals limit the display to only 2 decimals (0.63712938 becomes 0.64) -- Jim How do you propose to explain to Access how to tell that a number "has no significa...

What I have is a column with 6 characters that can either be all alpha or 1 alpha with 5 numeric characters. What I need to do is examine cell and if the last five characters are numberic compute the cost. How would I make a condition to do that. part # QTY ID Rate Extension 23456 23 JJones .34 39485 20 J23494 .59 11.80 96874 30 J23494 1.19 35.70 16349 50 DDavis .59 in your extension column enter =if(isnumber(value(right(ID,4))),Qty*Rate,"") "Daniell" wrote: > Wh...

format a range of cells like c6 through h6 with a fill color of green if the value in cell k6 is positive and with a fill color of red if it is negative? How would that be done? One way: Select C6:H6, with C6 active. Choose Format/Conditional Formatting... Set the dropdowns and inputboxes to read: CF1: Formula is =$K6>0 Format1: <pattern>/<green> CF2: Formula is =$K6<0 Format2: <pattern>/<red> In article <BVKFh.65924$1H5.60595@newsfe16.phx>, az-willie <sclause@npole.com> wrote: > format a range of cells like c6 t...

I am trying a "conditional formatting" on a cell that contains formula, but it didn't work. "If cell value is equal to 0 then font - white" This doesn't work, stays always. If i use this condition on a cell without formula it works just fine. Thank -- si ----------------------------------------------------------------------- sit's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=262 View this thread: http://www.excelforum.com/showthread.php?threadid=26784 Hi are you sure your formula returns an exact zero?. Could you post the formul...

I want to find the cosine of an angle, but the computer doesn't know the value I am referencing is in the form of a degree. can anyone help me!! you have to change it to radians in your formula =cos(Val*pi()/180) "justin" wrote: > I want to find the cosine of an angle, but the computer doesn't know the > value I am referencing is in the form of a degree. > > can anyone help me!! This may help you: Insert a degree symbol Help You can easily insert a degree symbol in your Office documents. Most fonts that Microsoft Windows® users have installed inclu...

Hello, I try to use: (H2312:H2323=H2322)*(D2307:D2322=4) =SUM(IF(AND(H2312:H2323=H2322;D2307:D2322=4);1;0)) and more combinations for example with sumproduct (of course I try to hit CTRL+SHIFT+ENTER combination), but it doesn't work (I only get error or 0 which is incorrect value). I have two columns - one with time (for example: 2010-09-01) and second with number. I want to count cells using two criterias. Unfortunately I can't create pivot table (workbook is shared) to solve my problem, so please help me. Try: =sumproduct(--(H2312:H2323=H2322),--(D2307:D2322=4)) Adjust the ...

Hi I need to use one condition to judge whether or not a cell is 90-95% of target or 100-105% of target. The other two conditions will be used, so i need to get this into one condition. Any help would be great. Thanks KCi, Use "Formula is." =OR(AND(A2>=Target*90%, A2 <= Target*95%),AND(A2>=Target*100%, A2<=Target*105%)) -- Earl Kiosterud mvpearl omitthisword at verizon period net ------------------------------------------- "KCi" <KCi@discussions.microsoft.com> wrote in message news:5216D751-611A-4C11-8930-02A76DBAB0E9@microsoft.com... > Hi I...

When my users edit their Out of office reply through OWA the formatting is lost. I.e they use the font arial with size 12, after they save the settings and then go back to the same page all the formatting is lost. Anyone have any ideas? ...

Is there a way to make certain entries in a combo box bold or change the background color of those items? Thank you, QB "QB" <QB@discussions.microsoft.com> wrote in message news:EC0B760D-179E-4658-8F5A-AC0C11328DF6@microsoft.com... > Is there a way to make certain entries in a combo box bold or change the > background color of those items? You mean, in the list, when you drop it down? No, an Access combo box can't do that. You can highlight items, sort of, by basing the combo box on a query that builds the display text and includes asterisks o...

A customer asked me this question about Pivot Tables and I want to make sure I give him the right info. 1)When my customer updates pivot tables the formatting always changes even though he has selected the check box stating to keep formatting. 2) I tried this out and it seems to keep most of the formatting like color, bolding and dollar signs but it did not keep the bordering. 3) Is there a way to have it keep all formatting for a report and have it maintain the integrity of the formatting even though he updates the data? Any help would be greatly appreciated ! Jugglertwo Set t...

Good afternoon. I am designing a report where 3 or 4 text paragraphs only need to appear on the very first page. My idea is to create a page footer and to put a formula of macro to do so... Can anyone share idea /code with me to have this conditional formatting in place? Thanks a bunch for your help Michel ...

I'm familiar with subtracting start time from finish time to get elapsed time, and formatting the elapsed time as a time function, to include d:h:mm for elapsed times of more than 24 hours. But I don't seem to be able to work out a formating that will, e.g., report 30 hours for the elapsed time for a number 1.25 [days]. Will formatting do it, or do I have to write my own parsing function? Thanks, Fred Holmes Format it as [hh]:mm, this handles more than 24 hours. -- HTH RP (remove nothere from the email address if mailing direct) "Fred Holmes" <fsh@his.com> wr...

I have an Outlook task form with some script in it to put a date stamp etc into the body of the task. The problem is that I want to make the date stamp bold. Trolling thru the net, I found references to Item.HTMLBody - but I cant seem to get this to work for a TASK - only for an email item. i.e. Item.HTMLBody = "<HTML><b/>Date Stamp.</b><BODY> Other Text.</BODY></HTML>" works for an email but NOT for a task. Once my task is created I can however enter text manually and make it bold manually. Any help is much appreciated. ...

Dear All, Could anyone help me with the following question: I have a spreadsheet with a column of cells that are coloured (colored in purple whilst a project is incomplete but which are then coloure white again when the project is finished. I have another column i which the completion date is written (once it is finished). Is there way I can use conditional formatting to have the first column becom uncoloured conditional on the other cells? Thus, I want cell B1 to b white IFF cell D1 is not empty. I am using Excel 97 (for my sins). Any help very gratefully received! Many thanks! Wil ...

Have just recently upgraded customer from v7.5 to V9. They have specialised inventory item numbers that include dimensions but when they print their SOP sales invoices - they only require the first 6 digits printed. We achieved this a while ago by putting a format on the field (XXXXXX). Since the upgrade one user has experienced a printing problem as the full item number appears - we have now establised that it is only if the user uses a smartlist to select the invoice and drills back to the invoice to print. If they use the inquiry screen to select the invoice - all is ok. Anyone any i...