Google Sheets – Fix AVERAGEIFS ERROR ‘Divide by Zero’

formulasgoogle sheets

I'm trying to calculate an average # days an account has been open if all 3 specific conditions are met.

Average range: R6:R102

Conditions:

  • A6:A102 = "x"
  • S6:S102 = "* EWS *" (contain part of EWS)
  • B6:B102 = past 3 years
    For last condition, the easiest solution was to put a formula in A3 (where A3= =TODAY()-1095) and then reference it in my AVERAGEIFS formula.

=AVERAGEIFS(R6:R102, A6:A102, "x", S6:S102, "*EWS*", B6:B102, ">A3")

Formula worked fine until I added condition #3. Now, I get a DIVIDE BY ZERO error. Why does it give me an error? Should I be making the last condition a date between today and 3yrs ago?

appreciate any insight you can provide. thank you!

Best Answer

=TODAY()-1095 doesn't count for leap years etc. so:

=AVERAGEIFS(R4:R102, A4:A102, "x", 
                     S4:S102, "*EWS*", 
                     B4:B102, ">"&MONTH(TODAY())&"/"&DAY(TODAY())&"/"&YEAR(TODAY())-3)
Related Topic