Count and Average dilemma

[Serafin M]

I have lists of offices that are ranked in different categories. I'm trying
to create a final list that Averages all of the rankings for the specific
offices. For example:

A B C D
1 Dwntwn 1 Main
2 Main 2 Square
3 Square 3 Main

I want to be able to create a formula that will look into column B, find a
name and return the ranking from column A. And repeat for columns D&C. Then
take the Average.

PLease Help.

 

[Domenic]

Try the following...

=AVERAGE(CHOOSE({1,2},INDEX(A1:A3,MATCH(A10,B1:B3,0)),INDEX(C1:C3,MATCH(A
10,D13,0))))

....where A10 contains your office of interest.

If you have a number of lists, you may want to try the following
approach...

On Sheet2...

1) Enter your list of offices in Column A

2) Enter the following formula in B1, copy down and across:

=INDEX(OFFSET(Sheet1!$A$1:$A$3,0,(COLUMN()-COLUMN($B1)+1)*2-2),MATCH($A1,
OFFSET(Sheet1!$B$1:$B$3,0,(COLUMN()-COLUMN($B1)+1)*2-2),0))

3) Enter your AVERAGE formula at the end of the first row and copy down

Hope this helps!

 

[RagDyer]

Try this, with the office to average entered in E1:

=SUMPRODUCT(--(B150=E1),A1:C50)/COUNTIF(B150,E1)
--

HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================


I have lists of offices that are ranked in different categories. I'm trying
to create a final list that Averages all of the rankings for the specific
offices. For example:

A B C D
1 Dwntwn 1 Main
2 Main 2 Square
3 Square 3 Main

I want to be able to create a formula that will look into column B, find a
name and return the ranking from column A. And repeat for columns D&C. Then
take the Average.

PLease Help.