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
--
What year did you say this was

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?

"Joeredmm" wrote:

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
--
What year did you say this was

maby COUNTA


"Joeredmm" skrev:

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
--
What year did you say this was

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


--
Regards,

Peo Sjoblom


"Joeredmm" wrote in message
...
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
--
What year did you say this was

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.
--
What year did you say this was


"tim m" wrote:

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?

"Joeredmm" wrote:

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
--
What year did you say this was

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.
--
What year did you say this was


"Peo Sjoblom" wrote:

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


--
Regards,

Peo Sjoblom


"Joeredmm" wrote in message
...
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
--
What year did you say this was

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




"Joeredmm" wrote in message
...
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.
--
What year did you say this was


"tim m" wrote:

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?

"Joeredmm" wrote:

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
--
What year did you say this was

That worked perfectly. Thanks
--
What year did you say this was


"Peo Sjoblom" wrote:

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




"Joeredmm" wrote in message
...
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.
--
What year did you say this was


"tim m" wrote:

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?

"Joeredmm" wrote:

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
--
What year did you say this was