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

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.

--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire


"Lise" wrote:

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

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

--
Biff
Microsoft Excel MVP


"Lise" wrote in message
...
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

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
---------------
Jacob Skaria


"Lise" wrote:

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