Calculated Average by Fee Level

[Datatellsall2]

Hello -


I'm trying to calcuate 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]

 

[DanteAmor]

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1	Individual	Name of Project	20000	25000	35000	40000	45000			Individual	20000	25000	35000	40000	45000
2	Person A	Project 1	3							Person A	4	3	5.5		3
3	Person A	Project 2					3			Person B			9.25
4	Person A	Project 3	5							Person C		5	9	1	13
5	Person A	Project 4			3
6	Person A	Project 5			8
7	Person A	Project 6		3
8	Person B	Project 1			6
9	Person B	Project 2			16
10	Person B	Project 3			7
11	Person B	Project 4			8
12	Person C	Project 5		5
13	Person C	Project 6			6
14	Person C	Project 7			12
15	Person C	Project 8				1
16	Person C	Project 9					13
17	Person C	Project 10			11
18	Person C	Project 11			7
sheet2

[TABLE="class: grid, width: 600"]
[TR]
[TD]Cell[/TD]
[TD]Formula[/TD]
[/TR]
[TR]
[TD]K2[/TD]
[TD]=SI.ERROR(AVERAGEIFS(C$2:C$18,$A$2:$A$18,$J2,C$2:C$18,"<>0"),"")[/TD]
[/TR]
[/TABLE]


Copy the formula to the right up to column O and then down to row 4.
Change the formula 18 to the number of your last row with data.