Countif Help

[Curtis]

cell c2 = 80%

Column of data is c5:c27


I need to calculate the % of sales greater than the value in cell c2.

thanks

 

[Ron Coderre]

This formula returns the percentage of values
in C5:C27 that are greater than the value in C2.
Note: it divides by the count of numeric values in C5:C27:

=COUNTIF(C5:C27,">"&C2)/COUNT(C5:C27)

Format the result as Percent

Is that something you can work with?
--------------------------

Regards,

Ron
Microsoft MVP (Excel)
(XL2003, Win XP)

 

[Curtis]

Thanks that worked

I need to now determine the % that fails within a range says between the
value in c2 and the value in d2

Thanks

 

[Curtis]

Lastly the formula needs to exclde blank cell ( however the cells do contain
a formula)

 

[Luke M]

=(COUNTIF(C5:C27,">"&C2)-COUNTIF(C5:C27,">="&D2))/COUNT(C5:C27)

 

[Curtis]

if c2= 9% and d2=8% it returns a negative value?

 

[Peo Sjoblom]

Switch places between D2 and C2


--


Regards,


Peo Sjoblom

 

[Curtis]

That will work for this cell but then another cell will be negative

 

[David Biddulph]

The formula was designed for a lower limit in C2 and an upper limit in D2.
If you haven't got the data arranged that way, and you have C2 and D2 as
limits that might be either way round, then replace C2 in the formula by
MIN(C2,D2), and replace the original occurrence of D2 in the formula by
MAX(C2,D2).