Avoiding "0" answer to average formula where there is no data
Hi Folks:
I'm setting up formulas to average data from another sheet where certain criteria is met. The rub is that there may not be data entered yet that meets the criteria. When that occurs, i'm getting a 0 as the answer. I would instead like to have a blank. I have the formulas currently set to return a blank when the answer is "0."
The issue is that in some circumstances there could be data in the columns where the average is in fact zero. I think this is unlikely, but it's still a concern.
Is there a way to return a blank only when there is no data that meets the criteria? My current formula is:
=IF(AVERAGEIF({KPIs Range 1}, Alabama2, {KPIs Range 6}) = 0, "", AVERAGEIF({KPIs 1}, Alabama2, {KPIs Range 6}))
Best Answer
-
Hi @BenG0706,
If there may not be data entered yet that meets the criteria then COUNTIF({KPIs Range 1}, Alabama2) = 0.
Try this:
=IF(COUNTIF({KPIs Range 1}, Alabama2) = 0, "", AVERAGEIF({KPIs 1}, Alabama2, {KPIs Range 6}))
Gia Thinh Technology - Smartsheet Solution Partner.
Answers
-
Hi @BenG0706,
If there may not be data entered yet that meets the criteria then COUNTIF({KPIs Range 1}, Alabama2) = 0.
Try this:
=IF(COUNTIF({KPIs Range 1}, Alabama2) = 0, "", AVERAGEIF({KPIs 1}, Alabama2, {KPIs Range 6}))
Gia Thinh Technology - Smartsheet Solution Partner.
-
Hi Gia:
Thanks for your reply. That solution did not solve it exactly, but it led me to a solution that worked. I had to use COUNTIFS since there were two ranges and two sets of criteria that were needed. The formula ended up looking like this:
=IF(COUNTIFS({KPIs Range 1}, Alabama2, {KPIs Range 4}, ISNUMBER(@cell)) = 0, "", AVERAGEIF({KPIs Range 1}, Alabama2, {KPIs Range 4}))
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.5K Get Help
- 402 Global Discussions
- 213 Industry Talk
- 450 Announcements
- 4.7K Ideas & Feature Requests
- 141 Brandfolder
- 135 Just for fun
- 56 Community Job Board
- 454 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 296 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!