2

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!

  • 1
    Try ">"&A3 as last condition. – Jacob Jan Tuinstra Aug 17 '18 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! – que syrah sarah Aug 17 '18 at 21:14
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)
| improve this answer | |
  • you're the best, @user0! thank you yet again :) – que syrah sarah Aug 17 '18 at 21:13

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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