Countif

[Lise]

I have a column which calculates the average of a range of cells (column w) I
then have the following formula =COUNTIF(W3:W500,100%) which is working
correctly however when I change this to =COUNTIF(W3:W500,95%) or
=COUNTIF(W3:W500,89%) the calculation will not work - What am I doing wrong
please??

Thanks as always

 

[Shane Devenshire]

Hi,

Whats in the range W3:W500? And what exactly are you trying to return with
the formulas that don't work?

Also remember that your criteria are exact which means that if there is
something with a value of .94999 = 94.999% it will not be found.

 

[T. Valko]

Your percentages may not be exactly even. For example, 95% might actually be
95.003%.

Try it like this:

=COUNTIF(W3:W500,">=95%")-COUNTIF(W3:W500,">=96%")

Format as General

 

[Jacob Skaria]

Lise

Another way is
=SUMPRODUCT(--(TEXT(W3:W500,"0.00")="0.95"))

As suggested by Biff when you use the COUNTIF formula dont forget to format
the formula cell to General..
=COUNTIF(W3:W500,">=95%")-COUNTIF(W3:W500,">=96%")


If this post helps click Yes