Can I have blank fields treated as zero in functions
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
 All Categories
 14 Welcome to the Community
 Smartsheet Customer Resources
 62.4K Get Help
 364 Global Discussions
 202 Industry Talk
 430 Announcements
 4.4K Ideas & Feature Requests
 137 Brandfolder
 129 Just for fun
 128 Community Job Board
 446 Show & Tell
 28 Member Spotlight
 1 SmartStories
 284 Events
 35 Webinars
 7.3K Forum Archives
Want to practice working with formulas directly in Smartsheet?
Check out the Formula Handbook template!
Check out the Formula Handbook template!