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.
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) |