average cells, show 0 if nothing to average

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am averaging the following:

=AVERAGE(G67:G76), this works fine (simple formula). However, if nothing is
entered in all of these cells, I would like my results cell to display 0.
Currently if nothing is entered for G67 thru G76, it results in #DIV/0!. I
just want it to show 0. I know this is simple, but I am dancing all around
it. Any suggestions would be appreciated. Thanks!
 
Closer. Typing that in currently displays a blank cell. So the error is
gone, but it is not displaying 0 if nothing is entered. Any suggestions?
 
Is it a completely blank cell or a dash in the centre of the cell? If it's a
dash then it's most likely your formatting setting. Change the format of the
cell and you should get a 0. I tried Tim's formula in my own sheet and got a
0. Another way to do the same formula is as follows

=IF(ISERROR(AVERAGE(G67:G76)),0,AVERAGE(G67:G76))

If it's a completely blank cell you've stumped me.
 
I tried that formula as well and still receive an empty cell as my result.
The cells G67:G76 would be completely blank. No dash or anything. Thanks
for the response. Anyone else???
 
Shouldn't do, it should show 0, unless you have zeroes suppressed
(Tools>Options>General>Zero Values).

--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)
 
Genius!!! I sure did, as was my intention, yet forgot when trying to
implement this forumula. Thanks to all for replying as all of your answers
were correct, just user error!
 
Kycajun said:
Genius!!! I sure did, as was my intention, yet forgot when trying to
implement this forumula. Thanks to all for replying as all of your answers
were correct, just user error!
Click to expand...

You may try this too.

=IF(SUM(G67:G76)=0,0,AVERAGE(G67:G76)

For some reason if you believe it is incorrect, kindly let me know.
 
:

You may try this too.

=IF(SUM(G67:G76)=0,0,AVERAGE(G67:G76)

For some reason if you believe it is incorrect, kindly let me know.
 
Back
Top