Avoiding "0" answer to average formula where there is no data

BenG0706

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}))

Gia Thinh

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

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}))

BenG0706

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}))