Countif

[Guest]

I have a formula in a worksheet that I use to calculate the number of cells
to divide by. I use it to average percentages of the six cells which
represent monthly averages.

=SUM(H2:H7)/COUNTIF(H2:H7,"<>0")

this is the formula. The problem is the formula still returns 6 even when
some of the cells contain 0.

I am using Excel 2002

 

[Guest]

I just did a test and the formula seemed to work to me, when you say the
formula returns 6, you are just talking about the countif part and not the
whole formula correct? Because if you had a 6 and all the rest as zeros the
result would be 6 no?

 

[Guest]

maby COUNTA


"Joeredmm" skrev:

 

[Peo Sjoblom]

If you mean that the COUNTIF part returns 6 then maybe you should check the
zero cells, if they are part of another formula you might have the cells
formatted as currency with 2 decimals and if your formula returns

0.0000001

it will be included in COUNTIF while the cell (thanks to the formatting)
will display 0.00

 

[Guest]

The cells are percentages positive and negative of production values from a
mine plant. Five of the cells have data and the sith has a 0 that is derived
from this formula in each cell

=IF(G7=0,"0",(G7-G6)/G6)

if there is no data from the other worksheets the cell returns a zero value,
otherwise the value of the month will be returned.

Since there are five cells that are not equal to 0 they should return true
values and be counted. The formula returns six so it appears to be counting
the sixth cell showing a zero value. All of the cells are formatted to
percentages.

 

[Guest]

I just changed the formatting of the cells from percentage to numbers and
multiply by 100 to bring back to a percentage value and the formula works now.

If anyone comes up with a fix where I can use the percentage formatting let
me know.

 

[Peo Sjoblom]

Change the formula to

=IF(G7=0,0,(G7-G6)/G6)

Don't use quotations for numbers, that formula produces a text zero


--
Regards,

Peo Sjoblom

 

[Guest]

That worked perfectly. Thanks