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!

average cells, show 0 if nothing to average
 

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!

average cells, show 0 if nothing to average
 

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!

average cells, show 0 if nothing to average
 

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!

average cells, show 0 if nothing to average
 

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!

average cells, show 0 if nothing to average
 

Shouldn't do, it should show 0, unless you have zeroes suppressed
(ToolsOptionsGeneralZero Values).

--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)

"Kycajun" wrote in message
...
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!

average cells, show 0 if nothing to average
 

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
(ToolsOptionsGeneralZero Values).

--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)

"Kycajun" wrote in message
...
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!

average cells, show 0 if nothing to average
 

"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
(ToolsOptionsGeneralZero Values).

--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)

"Kycajun" wrote in message
...
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.

average cells, show 0 if nothing to average
 

"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
(ToolsOptionsGeneralZero Values).

--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)

"Kycajun" wrote in message
...
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!