Can I have blank fields treated as zero in functions
Options
Hi,
I am trying to average a range of cells. Two questions:
 Most important  in some cases the cell is empty. That should be treated as a zero but the AVG function is ignoring the blanks and only taking the average of the populated cells.
 If there happens to be text in the field (there should not be), then the field can be ignored.
Thanks,
Suzanne
Best Answer

You could do a SUM divided by a COUNTIFS to specify exactly what should and should not count towards the average.
=SUM(range to sum)/COUNTIFS(range to sum, NOT(ISTEXT(@cell)))
The COUNTIFS portion will count every cell that is not text meaning it will count rows that have a number or a blank.
Answers

You could do a SUM divided by a COUNTIFS to specify exactly what should and should not count towards the average.
=SUM(range to sum)/COUNTIFS(range to sum, NOT(ISTEXT(@cell)))
The COUNTIFS portion will count every cell that is not text meaning it will count rows that have a number or a blank.

This is great, thanks so much Paul.

Happy to help! 👍️
Help Article Resources
Categories
Want to practice working with formulas directly in Smartsheet?
Check out the Formula Handbook template!
Check out the Formula Handbook template!