When using AVG formula, is there a way to have blank entries in the columns without getting a #divid
When using AVG formula, is there a way to have blank entries in the columns without getting a #divideby error?
Best Answer
-
OK so in your situation, there are no populated values at all! Got it. In that case, you just want to add IFERROR to your formula, to specify a replacement value in case of an error:
=IFERROR(AVG([02/15/22]@row, [02/28/22]@row), "")
This says, if the formula results in an error, just leave the cell blank (that's the empty pair of quotes at the end.) You could also specify a value, another formula, or some other message (such as "Hey, help a poor formula out, would ya? Give me some values to average!")
Here I added another formula as the IFERROR value:
Here I added a more meaningful error message:
Regards,
Jeff Reisman
Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages
If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!
Answers
-
What formula are you using for AVG? When I use it, it ignores blank cells.
Blanks included in my range:
Regards,
Jeff Reisman
Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages
If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!
-
=AVG([02/15/22]@row, [02/28/22]@row)
-
OK so in your situation, there are no populated values at all! Got it. In that case, you just want to add IFERROR to your formula, to specify a replacement value in case of an error:
=IFERROR(AVG([02/15/22]@row, [02/28/22]@row), "")
This says, if the formula results in an error, just leave the cell blank (that's the empty pair of quotes at the end.) You could also specify a value, another formula, or some other message (such as "Hey, help a poor formula out, would ya? Give me some values to average!")
Here I added another formula as the IFERROR value:
Here I added a more meaningful error message:
Regards,
Jeff Reisman
Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages
If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!
-
This helped! Thank you!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.7K Get Help
- 433 Global Discussions
- 136 Industry Talk
- 468 Announcements
- 4.9K Ideas & Feature Requests
- 143 Brandfolder
- 147 Just for fun
- 64 Community Job Board
- 466 Show & Tell
- 32 Member Spotlight
- 2 SmartStories
- 298 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!