Excel Forumla - two colums/need average 1

[DwayneL]

Hello there

I need to find that average for data that is related to a column of data. For example

column A column B

1 45
1 23
3 5
2 43
4 12
2 32

I need to find the average of column B for all rows that relate to column A where number is 2 in column A

If I did it for 2 the average should be 43+32=75/2=37.5

It there a way to do that with a forumla?

I would apperiate any suggestions

 

[SkipVought]

Hi,

1) generate a unique list of column A values using Data/Advanced Filter - UNIQUE VALUES to a new location (no criteria)

2) assuming that this list is in column D...
[tt]
E2: =SUMPRODUCT(($A$2:$A$7=D2)*($B$2:$B$7))
F2: =COUNTIF($A$2:$A$7,D2)
G2: =E2/2
[/tt]
results in
[tt]
A B C D E F G

A B . A sum count avg
1 45 1 68 2 34
1 23 3 5 1 2.5
3 5 2 75 2 37.5
2 43 4 12 1 6
4 12
2 32

[/tt]




Skip,
[sub]
[red]A palindrome gone wrong?[/red]
A man, a plan, a ROOT canal...
PULLEMALL![/sub]

 

[bluedragon2]

=SUMPRODUCT((A1:A100=2)*(B1:B100))/COUNTIF(A1:A100,2)

[Blue]Blue[/Blue] [Dragon]

If I wasn't Blue, I would just be a Dragon...

 

[KenWright]

=AVERAGE(IF(A1:A6=2,B1:B6))
Array entered using CTRL+SHIFT+ENTER

or

=SUMIF(A1:A6,2,B1:B6)/COUNTIF(A1:A6,2)
entered normally

or

=SUMPRODUCT(--(A1:A6=2),B1:B6)/COUNTIF(A1:A6,2)
entered normally

The 2 can also be replaced with a cell reference where that cell contains the appropriate number.

Regards
Ken............

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission
----------------------------------------------------------------------------

 

[SkipVought]

oops...
[tt]
G2: =E2/F2
[/tt]


Skip,
[sub]
[red]A palindrome gone wrong?[/red]
A man, a plan, a ROOT canal...
PULLEMALL![/sub]

 

[DwayneL]

Thanks guys I went ahead and used KenWright's soultion. Thanks for being so quick, I hardly use excel anymore so that saved me a lot of time.