Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Average from 2 unsorted lists
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. -- KevinE ------------------------------------------------------------------------ KevinE's Profile: http://www.excelforum.com/member.php...nfo&userid=525 View this thread: http://www.excelforum.com/showthread...hreadid=395064 |
#2
|
|||
|
|||
If each name appears in each list only once
=(SUMPRODUCT(--(A1:A4="John")*B1:B4)+SUMPRODUCT(--(C1:C4="John")*D1:D4))/2 If John can appear more than once per list =(SUMPRODUCT(--(A1:A4="John")*B1:B4)+SUMPRODUCT(--(C1:C4="John")*D1:D4))/=(SUMPRODUCT(--(A1:A4="John"))+SUMPRODUCT(--(C1:C4="John")) "KevinE" wrote: 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. -- KevinE ------------------------------------------------------------------------ KevinE's Profile: http://www.excelforum.com/member.php...nfo&userid=525 View this thread: http://www.excelforum.com/showthread...hreadid=395064 |
#3
|
|||
|
|||
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 :D$4))) Copy down to B13 Biff "KevinE" wrote in message ... 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. -- KevinE ------------------------------------------------------------------------ KevinE's Profile: http://www.excelforum.com/member.php...nfo&userid=525 View this thread: http://www.excelforum.com/showthread...hreadid=395064 |
#4
|
|||
|
|||
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" wrote: If each name appears in each list only once =(SUMPRODUCT(--(A1:A4="John")*B1:B4)+SUMPRODUCT(--(C1:C4="John")*D1:D4))/2 If John can appear more than once per list =(SUMPRODUCT(--(A1:A4="John")*B1:B4)+SUMPRODUCT(--(C1:C4="John")*D1:D4))/=(SUMPRODUCT(--(A1:A4="John"))+SUMPRODUCT(--(C1:C4="John")) "KevinE" wrote: 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. -- KevinE ------------------------------------------------------------------------ KevinE's Profile: http://www.excelforum.com/member.php...nfo&userid=525 View this thread: http://www.excelforum.com/showthread...hreadid=395064 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
plotted Average | Charts and Charting in Excel | |||
What is this kind of average called? | Excel Worksheet Functions | |||
Average Formula with Criteria | Excel Discussion (Misc queries) | |||
AVERAGE and STDEV functions with logic | Excel Worksheet Functions | |||
Average the Last Five Cells in a Column | Excel Worksheet Functions |