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

Average range: R6:R102


  • 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!

  • 1
    Try ">"&A3 as last condition.
    – Jacob Jan
    Aug 17, 2018 at 19:28
  • @JacobJanTuinstra, this works! user0 also came up with a solution that got rid of using cell A3. both solutions work great. thank you! Aug 17, 2018 at 21:14

1 Answer 1


=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)
  • you're the best, @user0! thank you yet again :) Aug 17, 2018 at 21:13

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.