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.

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
>
>

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
> >
> >

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" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>

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
>
>

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" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>

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
> >
> >

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
>
>

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" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>

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
> >
> >

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
>
>

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" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>

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
> >
> >

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
> >
> >

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" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>

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
>
>

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
> >
> >

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" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>

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
>
>

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" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>

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
>
>

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
> >
> >

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" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>

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
>
>

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
> >
> >

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
> >
> >

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
>
>

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" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>

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
> >
> >

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" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>

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
>
>

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
> >
> >

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" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>

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
>
>

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
>
>

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" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>

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
> >
> >

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" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>

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
> >
> >

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
>
>

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
> >
> >

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" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>

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
>
>

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
>
>

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" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>

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
> >
> >

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
> >
> >

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" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>

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
>
>

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
>
>

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" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>

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
> >
> >

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" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>

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
>
>

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
> >
> >