2

I'm working on a formula to average the duration of several matches in a tournament. Specifically, I'm trying to find the average duration for specific pairings, which has led me to the following formula:

=AVERAGEIF(C2:E,AND(C2="Alice",E2="Bob"),AC2:AC)

The names of the competitors are in columns C and E, hence C2:E for the Criterion Range.

The AND function is looking to count only the matches where Alice and Bob are competing (Alice is always listed before Bob in their matches, so this catches all of them). I could generalise this to look at cells AE2 and AF2 (where Alice and Bob are listed again, to show that the following data is their average match length), but I'm using strings in this example for legibility.

Lastly, AC2:AC covers all the match lengths, and so this is the range I want to average. These are generally between 10 and 60 minutes, but some last more than an hour. They are all nonzero.

I have checked that all entries in column AC are formatted as a duration - however, they are all calculated by a formula (summing the length of the best-2-of-3 rounds for the total match length), so the snag might potentially be there somehow.

This all seems correct to me, and sure enough, a simple AVERAGE(AC2:AC) will give me the average time for all the matches, since there aren't any problems with the data. However, putting it all together in the AVERAGEIF formula returns a DIV/0 error, and I cannot fathom why. What's wrong with the way I've made this formula? I initially thought it might have been some sort of parsing issue with trying to average a duration, but the regular average handles it just fine, so I'm at a loss for what the mistake is here.

1
  • Welcome! May be =AVERAGEIFS(AC2:AC, C2:C,"Alice", E2:E,"Bob") or =AVERAGEIFS(AC2:AC, C2:C,$AE$2, E2:E,$AF$2)?
    – JohnSUN
    Commented Aug 31, 2022 at 15:09

1 Answer 1

0

Try the following

=INDEX(AVERAGEIF(C2:C&E2:E,"="&C2&E2,F2:F))

enter image description here

1
  • Please remember that as per site guidelines when an answer addresses your question, you should accept it and even upvote it so others can benefit as well. Commented Aug 31, 2022 at 15:11

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.