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, IT Business Analyst & Project Coordinator, Mitsubishi Electric Trane US
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, IT Business Analyst & Project Coordinator, Mitsubishi Electric Trane US
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, IT Business Analyst & Project Coordinator, Mitsubishi Electric Trane US
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
Check out the Formula Handbook template!