average cells, show 0 if nothing to average

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!

Give this a try:

=IF(ISERR(AVERAGE(G67:G76))=TRUE,0,AVERAGE(G67:G76))

"Kycajun" wrote:

> 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?

"tim m" wrote:

> Give this a try:
>
> =IF(ISERR(AVERAGE(G67:G76))=TRUE,0,AVERAGE(G67:G76))
>
> "Kycajun" wrote:
>
> > 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!

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.

"Kycajun" wrote:

> 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?
>
> "tim m" wrote:
>
> > Give this a try:
> >
> > =IF(ISERR(AVERAGE(G67:G76))=TRUE,0,AVERAGE(G67:G76))
> >
> > "Kycajun" wrote:
> >
> > > 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!

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???

"shaunap" wrote:

> 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.
>
> "Kycajun" wrote:
>
> > 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?
> >
> > "tim m" wrote:
> >
> > > Give this a try:
> > >
> > > =IF(ISERR(AVERAGE(G67:G76))=TRUE,0,AVERAGE(G67:G76))
> > >
> > > "Kycajun" wrote:
> > >
> > > > 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!

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)

"Kycajun" <[email protected]> wrote in message
news:[email protected]...
> 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?
>
> "tim m" wrote:
>
> > Give this a try:
> >
> > =IF(ISERR(AVERAGE(G67:G76))=TRUE,0,AVERAGE(G67:G76))
> >
> > "Kycajun" wrote:
> >
> > > 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!

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!

"Bob Phillips" wrote:

> 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)
>
> "Kycajun" <[email protected]> wrote in message
> news:[email protected]...
> > 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?
> >
> > "tim m" wrote:
> >
> > > Give this a try:
> > >
> > > =IF(ISERR(AVERAGE(G67:G76))=TRUE,0,AVERAGE(G67:G76))
> > >
> > > "Kycajun" wrote:
> > >
> > > > 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!
>
>
>

"Kycajun" wrote:

> 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!
>
> "Bob Phillips" wrote:
>
> > 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)
> >
> > "Kycajun" <[email protected]> wrote in message
> > news:[email protected]...
> > > 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?
> > >
> > > "tim m" wrote:
> > >
> > > > Give this a try:
> > > >
> > > > =IF(ISERR(AVERAGE(G67:G76))=TRUE,0,AVERAGE(G67:G76))
> > > >
> > > > "Kycajun" wrote:
> > > >
> > > > > 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!
> >
> >
> >

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.

"jpreman" wrote:

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.

>
>
> "Kycajun" wrote:
>
> > 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!
> >
> > "Bob Phillips" wrote:
> >
> > > 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)
> > >
> > > "Kycajun" <[email protected]> wrote in message
> > > news:[email protected]...
> > > > 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?
> > > >
> > > > "tim m" wrote:
> > > >
> > > > > Give this a try:
> > > > >
> > > > > =IF(ISERR(AVERAGE(G67:G76))=TRUE,0,AVERAGE(G67:G76))
> > > > >
> > > > > "Kycajun" wrote:
> > > > >
> > > > > > 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!
> > >
> > >
> > >
>
>
>
>