Calculated Average by Fee Level

[Datatellsall2]

Hello -


I'm trying to calculate a measure where I can see the average number of hours by fee level based on the 'individual'. As an example based on the data below, the average for Person A would be 4 within the 20K fee level.

[TABLE="width: 497"]
<colgroup><col><col><col span="5"></colgroup><tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD="colspan: 5"]Fee Category[/TD]
[/TR]
[TR]
[TD]Individual[/TD]
[TD]Name of Project[/TD]
[TD]20000[/TD]
[TD]25000[/TD]
[TD]35000[/TD]
[TD]40000[/TD]
[TD]45000[/TD]
[/TR]
[TR]
[TD]Person A[/TD]
[TD]Project 1[/TD]
[TD]3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Person A[/TD]
[TD]Project 2[/TD]
[TD] [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]Person A[/TD]
[TD]Project 3[/TD]
[TD]5[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Person A[/TD]
[TD]Project 4[/TD]
[TD] [/TD]
[TD][/TD]
[TD]3[/TD]
[TD][/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Person A[/TD]
[TD]Project 5[/TD]
[TD] [/TD]
[TD][/TD]
[TD]8[/TD]
[TD][/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Person A[/TD]
[TD]Project 6[/TD]
[TD] [/TD]
[TD]3[/TD]
[TD][/TD]
[TD][/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Person B[/TD]
[TD]Project 1[/TD]
[TD] [/TD]
[TD][/TD]
[TD]6[/TD]
[TD][/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Person B[/TD]
[TD]Project 2[/TD]
[TD] [/TD]
[TD][/TD]
[TD]16[/TD]
[TD][/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Person B[/TD]
[TD]Project 3[/TD]
[TD] [/TD]
[TD][/TD]
[TD]7[/TD]
[TD][/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Person B[/TD]
[TD]Project 4[/TD]
[TD] [/TD]
[TD][/TD]
[TD]8[/TD]
[TD][/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Person C[/TD]
[TD]Project 5[/TD]
[TD] [/TD]
[TD]5[/TD]
[TD][/TD]
[TD][/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Person C[/TD]
[TD]Project 6[/TD]
[TD] [/TD]
[TD][/TD]
[TD]6[/TD]
[TD][/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Person C[/TD]
[TD]Project 7[/TD]
[TD] [/TD]
[TD][/TD]
[TD]12[/TD]
[TD][/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Person C[/TD]
[TD]Project 8[/TD]
[TD] [/TD]
[TD][/TD]
[TD][/TD]
[TD]1[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Person C[/TD]
[TD]Project 9[/TD]
[TD] [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]13[/TD]
[/TR]
[TR]
[TD]Person C[/TD]
[TD]Project 10[/TD]
[TD] [/TD]
[TD][/TD]
[TD]11[/TD]
[TD][/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Person C[/TD]
[TD]Project 11[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]7[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
</tbody>[/TABLE]

 

[Scott T]

Book1
	A	B	C	D	E	F	G
1			Fee Category
2	Individual	Name of Project	20000	25000	35000	40000	45000
3	Person A	Project 1	3
4	Person A	Project 2					3
5	Person A	Project 3	5
6	Person A	Project 4			3
7	Person A	Project 5			8
8	Person A	Project 6		3
9	Person B	Project 1			6
10	Person B	Project 2			16
11	Person B	Project 3			7
12	Person B	Project 4			8
13	Person C	Project 5		5
14	Person C	Project 6			6
15	Person C	Project 7			12
16	Person C	Project 8				1
17	Person C	Project 9					13
18	Person C	Project 10			11
19	Person C	Project 11			7
20
21			20000	25000	35000	40000	45000
22	Person A		4	3	5.5	0	3
23	Person B		0	0	9.25	0	0
24	Person C		0	5	9	1	13
Sheet5

Cell Formulas
Range	Formula
C22	=IFERROR(AVERAGEIF($A$3:$A$19,$A22,C$3:C$19),0)
C23	=IFERROR(AVERAGEIF($A$3:$A$19,$A23,C$3:C$19),0)
C24	=IFERROR(AVERAGEIF($A$3:$A$19,$A24,C$3:C$19),0)
D22	=IFERROR(AVERAGEIF($A$3:$A$19,$A22,D$3:D$19),0)
D23	=IFERROR(AVERAGEIF($A$3:$A$19,$A23,D$3:D$19),0)
D24	=IFERROR(AVERAGEIF($A$3:$A$19,$A24,D$3:D$19),0)
E22	=IFERROR(AVERAGEIF($A$3:$A$19,$A22,E$3:E$19),0)
E23	=IFERROR(AVERAGEIF($A$3:$A$19,$A23,E$3:E$19),0)
E24	=IFERROR(AVERAGEIF($A$3:$A$19,$A24,E$3:E$19),0)
F22	=IFERROR(AVERAGEIF($A$3:$A$19,$A22,F$3:F$19),0)
F23	=IFERROR(AVERAGEIF($A$3:$A$19,$A23,F$3:F$19),0)
F24	=IFERROR(AVERAGEIF($A$3:$A$19,$A24,F$3:F$19),0)
G22	=IFERROR(AVERAGEIF($A$3:$A$19,$A22,G$3:G$19),0)
G23	=IFERROR(AVERAGEIF($A$3:$A$19,$A23,G$3:G$19),0)
G24	=IFERROR(AVERAGEIF($A$3:$A$19,$A24,G$3:G$19),0)

 

[Datatellsall2]

Works perfectly! Thank you!