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

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