Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
Kycajun
 
Posts: n/a
Default 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!
  #2   Report Post  
Posted to microsoft.public.excel.misc
tim m
 
Posts: n/a
Default 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!

  #3   Report Post  
Posted to microsoft.public.excel.misc
Kycajun
 
Posts: n/a
Default 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!

  #4   Report Post  
Posted to microsoft.public.excel.misc
shaunap
 
Posts: n/a
Default 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!

  #5   Report Post  
Posted to microsoft.public.excel.misc
Kycajun
 
Posts: n/a
Default 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!



  #6   Report Post  
Posted to microsoft.public.excel.misc
Bob Phillips
 
Posts: n/a
Default 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!



  #7   Report Post  
Posted to microsoft.public.excel.misc
Kycajun
 
Posts: n/a
Default 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!




  #8   Report Post  
Posted to microsoft.public.excel.misc
jpreman
 
Posts: n/a
Default 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.
  #9   Report Post  
Posted to microsoft.public.excel.misc
jpreman
 
Posts: n/a
Default 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!







Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
average price of 4 columns of figures but some cells can be blank bigdaddy3 Excel Worksheet Functions 3 February 14th 06 04:10 PM
average price of 4 columns of figures but some cells can be blank bpeltzer Excel Worksheet Functions 0 February 13th 06 09:37 PM
How to find the Average of cells containing a value Shirley Munro Excel Discussion (Misc queries) 1 February 9th 06 09:56 PM
Another Average non-adjacent cells question... [email protected] Excel Discussion (Misc queries) 2 February 1st 06 06:48 PM
Average the Last Five Cells in a Column Warrior Princess Excel Worksheet Functions 3 March 16th 05 03:12 PM


All times are GMT +1. The time now is 02:56 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"