Copilot for Microsoft 365 Tech Accelerator
Feb 28 2024 07:00 AM - Feb 29 2024 10:30 AM (PST)
Microsoft Tech Community

AVERAGEIFS eliminate zeros DIV/0 error

Copper Contributor

I'm trying to average using multiple criteria now because it is taking into account the zeros if I just use AVERAGEIF (see screenshot where top formula was working, but including zeros). I tried an AVERAGEIFS but am struggling to figure out where my error is dividing by zero.

 

How can I average if Tuesday (or Thursday) but eliminate those zeros?

 

*Note, working on the "Daily Totals" tab if you can into the excel sheet itself.

Screenshot 2024-01-05 at 8.34.48 AM.png

3 Replies

If there are no data satisfying the conditions, it's not possible to calculate an average. You can use IFERROR for this:

 

=IFERROR(AVERAGEIFS($F$2:$F$42,$B$2:$B$42,"Tuesday",$F$2:$F$42,">0"), "")

 

HansVogelaar_0-1704466091483.png

Ah, yes. I wish Mac had the feature on Excel like Microsoft does where it goes step by step through the equation to show you how it is calculating each piece.

Or if you do not want to use the IFERROR function (which could hide other errors in the data), you could use this formula, which checks for a positive sum before attempting the AVERAGEIFS:

=IF( SUMIFS($F$2:$F$42, $B$2:$B$42,"Tuesday")>0, AVERAGEIFS($F$2:$F$42, $B$2:$B$42,"Tuesday", $F$2:$F$42,">0"), "-" )

 (Additional spaces included for readability.)