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!

Improve this question
2
  • 1
    Try ">"&A3 as last condition.
    – Jacob Jan
    Commented 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!
    – que syrah sarah
    Commented Aug 17, 2018 at 21:14

1 Answer 1

Reset to default
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
1
  • you're the best, @user0! thank you yet again :)
    – que syrah sarah
    Commented 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.