CODEWITHSUNDEEP
excel
user3521423
user3521423

Reputation: 11

Calculate Average Across Multiple Pairs/Permutations

Not sure how to use AVERAGEIFS or a combination of SUMIFS and COUNTIFS to efficiently solve this, or some other function.

Basically, assume I have the following dataset of trip times between certain points

Start   End   Trip Time(Minutes)

A       B     12
A       B     8
B       A     9
B       A     2
A       C     15
C       A     5
C       B     11
C       B     9
B       C     7
A       B     16
A       D     18
D       C     21
E       A     11
X       Y     19

There could be n number of points in the dataset, but assume we are only interested in the average trip time of all trip pairs between 4 cities (A,B,C,D). i.e. AB, BA, AC, CA, BD, DB, etc. but not AA, BB, CC, DD.

How can I go about averaging the trip time between all these permutations? Much help would be appreciated..thank you!

Upvotes: 0

Views: 53

Answers (1)

Tim Williams
Tim Williams

Reputation: 166146

Not very pretty, but using a named range "CITIES" (A20:A23 below)

In E3 to arrange as unique pairs regardless of direction (and fill down):

=IFERROR(INDEX(CITIES,MIN(MATCH(A3,CITIES,0),MATCH(B3,CITIES,0)))&":"&
         INDEX(CITIES,MAX(MATCH(A3,CITIES,0),MATCH(B3,CITIES,0))),"")

In F3:

=IF(E3<>"",AVERAGEIFS($C$3:$C$16,$E$3:$E$16,E3),"")

You can copy/paste values/remove duplicates to get the unique pairs.

enter image description here

Upvotes: 1

Related Questions