I am trying too average some test scores and sometimes one of those numbers
can be zero. I read that the AVERAGE function will ignore blank cells, so I
tried to use an IF statement to change the zeros to blank cells. Here is what
I tried.
=AVERAGE(IF(D109:F109=0,"",D109:F109))
Any help would be greatly appreciated.
Thanks
Greg Ruff

Enter as an array (Ctrl-Shift-Enter)

=AVERAGE(IF(D109:F1090,D109:F109))

"grinlrar" wrote:

I am trying too average some test scores and sometimes one of those numbers
can be zero. I read that the AVERAGE function will ignore blank cells, so I
tried to use an IF statement to change the zeros to blank cells. Here is what
I tried.
=AVERAGE(IF(D109:F109=0,"",D109:F109))
Any help would be greatly appreciated.
Thanks
Greg Ruff

Hi grinlrar,

Pls try this
=SUMIF(D109:F109,"0",D109:F109)/COUNTIF(D109:F109,"0")

"grinlrar" wrote:

I am trying too average some test scores and sometimes one of those numbers
can be zero. I read that the AVERAGE function will ignore blank cells, so I
tried to use an IF statement to change the zeros to blank cells. Here is what
I tried.
=AVERAGE(IF(D109:F109=0,"",D109:F109))
Any help would be greatly appreciated.
Thanks
Greg Ruff

This is simple and not sure if it is what you are looking for but
anyway.

=SUM(G1:G20)/20

Assuming that your test scores are in column G and you only have 20
scores.
this will account for the Zeros also


--
pknivens
------------------------------------------------------------------------
pknivens's Profile: http://www.excelforum.com/member.php...o&userid=27675
View this thread: http://www.excelforum.com/showthread...hreadid=472055

Thanks for everyone's help. Actually I realized the formula is working except
if all three numbers that it is trying to average are zeros, so you get the
Divided by zero error.
I put this project together several months ago and am just now picking it
back up again so it is hard to remember what I was thinking back then.
I can live with the errors on the individual spreadsheets because I know
that it is actutally a zero. My problem now, is that the data from many
spreadsheets are averaged together to give an office wide percentage and if
one contains an error, it creates an error in the office wide spreadsheet.
Is there a way to make it ignore an error cell in a group of cells that are
being averaged together?

"grinlrar" wrote:

I am trying too average some test scores and sometimes one of those numbers
can be zero. I read that the AVERAGE function will ignore blank cells, so I
tried to use an IF statement to change the zeros to blank cells. Here is what
I tried.
=AVERAGE(IF(D109:F109=0,"",D109:F109))
Any help would be greatly appreciated.
Thanks
Greg Ruff