# Average from 2 unsorted lists

K

#### KevinE

Hello all,

I would like a formula that would find an average from two unsorte
lists.
One containing 12 months data and the other 3 months.

[a1]John [b1]26 [C1]Jack [D1]14
[a2]Fred [b2]21 [C2]John [D2]11
[a3]Jack [b3]32 [C3]Pete [D3]15
[a4]Pete [b4]24 [C4]Fred [D4]9

The averages should work out as:
John 18.5
Fred 15.0
Jack 23.0
Pete 19.5

many thanks for any help

G

#### Guest

If each name appears in each list only once

=(SUMPRODUCT(--(A1:A4="John")*B1:B4)+SUMPRODUCT(--(C1:C4="John")*D1 4))/2

If John can appear more than once per list

=(SUMPRODUCT(--(A1:A4="John")*B1:B4)+SUMPRODUCT(--(C1:C4="John")*D1 4))/=(SUMPRODUCT(--(A1:A4="John"))+SUMPRODUCT(--(C1:C4="John"))

B

#### Biff

Hi!

Assume you have the unique list of names in A10:A13

In B10 enter this formula using the key combo of CTRL,SHIFT,ENTER:

=AVERAGE(IF(A\$1:A\$4=A10,B\$1:B\$4,IF(C\$1:C\$4=A10,D\$1 \$4)))

Copy down to B13

Biff

G

#### Guest

KevinE -

The first formula I gave you works IF AND ONLY IF each name appears twice.
Unless that is an ironclad guarantee, use the second formula

Duke Carey said:
If each name appears in each list only once

=(SUMPRODUCT(--(A1:A4="John")*B1:B4)+SUMPRODUCT(--(C1:C4="John")*D1 4))/2

If John can appear more than once per list

=(SUMPRODUCT(--(A1:A4="John")*B1:B4)+SUMPRODUCT(--(C1:C4="John")*D1 4))/=(SUMPRODUCT(--(A1:A4="John"))+SUMPRODUCT(--(C1:C4="John"))

KevinE said:
Hello all,

I would like a formula that would find an average from two unsorted
lists.
One containing 12 months data and the other 3 months.

[a1]John [b1]26 [C1]Jack [D1]14
[a2]Fred [b2]21 [C2]John [D2]11
[a3]Jack [b3]32 [C3]Pete [D3]15
[a4]Pete [b4]24 [C4]Fred [D4]9

The averages should work out as:
John 18.5
Fred 15.0
Jack 23.0
Pete 19.5

many thanks for any help.