Average of max weight

[phil133]

Hi. I'm copying the data and the desired fields D and E. Column A have some scenarios from 1 to 100. Column B has dates. Column C has weights.
1) How can I get the max weight for the same scenario and trade date please?
2) How can I get the average of the max weight by scenario please?
I tried to do it with pivots but I can't find how I can get the average of the max weight by scenario.
Thank you.

sample.xlsx
	A	B	C	D	E
1	Scenario	TradeDate	Weight	Max weight	Average max weight by scenario
2	1	1/4/2016	7.50%
3	1	1/4/2016	1.30%
4	1	1/4/2016	11.50%	11.50%
5	1	1/8/2016	5.00%
6	1	1/8/2016	0.50%
7	1	1/8/2016	4.10%
8	1	1/8/2016	2.60%	5.00%	8.25%
9	2	1/8/2016	8.00%
10	2	1/8/2016	6.40%
11	2	1/8/2016	1.30%
12	2	1/8/2016	3.80%
13	2	1/8/2016	0.30%	8.00%
14	2	1/22/2016	7.00%
15	2	1/22/2016	6.40%
16	2	1/22/2016	1.30%
17	2	1/22/2016	3.80%
18	2	1/22/2016	0.30%
19	2	1/22/2016	9.00%	9.00%	8.50%
Sheet7

Cell Formulas
Range	Formula
D4	D4	=MAX(C2:C4)
D8	D8	=MAX(C5:C8)
E8	E8	=AVERAGE(D2:D8)
D13	D13	=MAX(C9:C13)
D19	D19	=MAX(C14:C19)
E19	E19	=AVERAGE(D9:D19)

 

[Eric W]

Try:

Book1
	A	B	C	D	E
1	Scenario	TradeDate	Weight	Max weight	Average max weight by scenario
2	1	1/4/2016	7.50%
3	1	1/4/2016	1.30%
4	1	1/4/2016	11.50%	11.50%
5	1	1/8/2016	5.00%
6	1	1/8/2016	0.50%
7	1	1/8/2016	4.10%
8	1	1/8/2016	2.60%	5.00%	8.25%
9	2	1/8/2016	8.00%
10	2	1/8/2016	6.40%
11	2	1/8/2016	1.30%
12	2	1/8/2016	3.80%
13	2	1/8/2016	0.30%	8.00%
14	2	1/22/2016	7.00%
15	2	1/22/2016	6.40%
16	2	1/22/2016	1.30%
17	2	1/22/2016	3.80%
18	2	1/22/2016	0.30%
19	2	1/22/2016	9.00%	9.00%	8.50%
Sheet8

Cell Formulas
Range	Formula
D2:D19	D2	=IF(AND(A2=A3,B2=B3),"",AGGREGATE(14,6,C$2:C2/(A$2:A2=A2)/(B$2:B2=B2),1))
E2:E19	E2	=IF(A2=A3,"",AVERAGEIF(A$2:A2,A2,D$2:D2))

 

[phil133]

Thank you Eric! This works!

 

[Eric W]

Glad it works for you!