Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
count if equal to not working?
Hello everyone,
I have several colums (A to F) and hundreds or rows of data, strickly numbers. My rows start from the 19th row and can carry on for several hundred. I'm only interested in certain range of data so in cel A1 and A2 I indicate the starting point and the finishing point of the data I wish to work with. I use the indirect function. I am interested in finding the average in that range of rows I wish to work with. Lets say from 20 to 50. H1=average(indirect(A20):indirect(A50). Everything works fine. Lets say my average is 13.33251. I only want to work with 3 digits after the decimal so my value now becomes 13.333. I would now like to rank my data by percent deviation from my average. This I can do no problem, H2=H1+(H1*0.1) for 10% deviation H3=H1+(H1*0.2) for 20% deviation .. .. H6=H1+(H1*0.5) for 50% deviation H10=sumproduct(--(indirect(A20):indirect(A50)H1),--(indirect(A20):indirect(A50)<=H2)) ctrl+sht+ent for array. What I cannot seem to count is how many times my average value appears in my range. countif(indirect(A20):indirect(A50),"="&H1) I can see one cell that matches my average of 13.333 but its not getting counted its being sum in my 10% cell formula. My only conclusion is because I've rounded up my cell to the third decimal point. If I use 4 decimal point that cell that should be counted becomes 13.33211. I'm hoping some one can help, maybe there's a better function to use. Thank you all for your time and patience. Still_poor |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
count if equal to not working?
Where are you rounding, I don't see a ROUND function?
If you mean displayed as then that won't change the underlying value -- Regards, Peo Sjoblom "Still_poor" wrote in message ... Hello everyone, I have several colums (A to F) and hundreds or rows of data, strickly numbers. My rows start from the 19th row and can carry on for several hundred. I'm only interested in certain range of data so in cel A1 and A2 I indicate the starting point and the finishing point of the data I wish to work with. I use the indirect function. I am interested in finding the average in that range of rows I wish to work with. Lets say from 20 to 50. H1=average(indirect(A20):indirect(A50). Everything works fine. Lets say my average is 13.33251. I only want to work with 3 digits after the decimal so my value now becomes 13.333. I would now like to rank my data by percent deviation from my average. This I can do no problem, H2=H1+(H1*0.1) for 10% deviation H3=H1+(H1*0.2) for 20% deviation . . H6=H1+(H1*0.5) for 50% deviation H10=sumproduct(--(indirect(A20):indirect(A50)H1),--(indirect(A20):indirect(A50)<=H2)) ctrl+sht+ent for array. What I cannot seem to count is how many times my average value appears in my range. countif(indirect(A20):indirect(A50),"="&H1) I can see one cell that matches my average of 13.333 but its not getting counted its being sum in my 10% cell formula. My only conclusion is because I've rounded up my cell to the third decimal point. If I use 4 decimal point that cell that should be counted becomes 13.33211. I'm hoping some one can help, maybe there's a better function to use. Thank you all for your time and patience. Still_poor |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Count if does not equal | Excel Worksheet Functions | |||
I want to count the highest number of equal cells | Excel Worksheet Functions | |||
Count number of values equal to MAX of a column | Excel Discussion (Misc queries) | |||
COUNT IF NOT EQUAL TO ZERO | Excel Worksheet Functions | |||
Count cells with length not equal to 7 | Excel Worksheet Functions |