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

# AVERAGEIFS eliminate zeros DIV/0 error

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.

3 Replies

# Re: AVERAGEIFS eliminate zeros DIV/0 error

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"), "")

# Re: AVERAGEIFS eliminate zeros DIV/0 error

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.

# Re: AVERAGEIFS eliminate zeros DIV/0 error

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"), "-" )``