Forum Discussion
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.
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.)
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"), "")
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.
