Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
average price of 4 columns of figures but some cells can be blank | Excel Worksheet Functions | |||
average price of 4 columns of figures but some cells can be blank | Excel Worksheet Functions | |||
How to find the Average of cells containing a value | Excel Discussion (Misc queries) | |||
Another Average non-adjacent cells question... | Excel Discussion (Misc queries) | |||
Average the Last Five Cells in a Column | Excel Worksheet Functions |