travellerva
New Member
- Joined
- Mar 31, 2012
- Messages
- 47
- Office Version
- 365
- Platform
- MacOS
Book1 | ||||||
---|---|---|---|---|---|---|
B | C | D | E | |||
1 | AAPL | AGM | AMD | ASML | ||
2 | Close | Close | Close | Close | ||
3 | $ 226.51 | $ 141.60 | $ 156.40 | $ 926.18 | ||
4 | $ 225.89 | $ 144.85 | $ 155.28 | $ 933.15 | ||
5 | $ 226.05 | $ 143.02 | $ 148.56 | $ 918.66 | ||
6 | $ 224.72 | $ 144.77 | $ 147.36 | $ 928.25 | ||
7 | $ 221.72 | $ 141.75 | $ 140.75 | $ 879.58 | ||
8 | $ 221.27 | $ 140.68 | $ 141.13 | $ 869.49 | ||
9 | $ 217.53 | $ 142.43 | $ 136.77 | $ 852.86 | ||
10 | $ 216.24 | $ 138.14 | $ 134.27 | $ 860.55 | ||
11 | $ 213.31 | $ 137.29 | $ 136.32 | $ 876.65 | ||
12 | $ 209.82 | $ 133.80 | $ 128.67 | $ 836.35 | ||
13 | $ 207.23 | $ 134.92 | $ 130.18 | $ 843.01 | ||
14 | $ 209.27 | $ 134.24 | $ 134.82 | $ 820.35 | ||
Sheet1 |
I need to finds a generic formula that identifies MAX values from a subset array in the 3D array indexed by a column header value (e.g. the MAX value of AAPL during a week of 5 values.). It can be found by the formula: =MAX(INDEX(B:B,5):INDEX(B:B,9)), but it needs variables for the column selection (B-E) and row range (5-9 and 10-14 in the example shown.
This is the table I'm trying to generate:
Book1 | ||||||
---|---|---|---|---|---|---|
H | I | J | K | |||
3 | Wk1 Hi | Wk2 Hi | ||||
4 | AAPL | =MAX(INDEX(B:B,5):INDEX(B:B,9)) | =MAX(INDEX(B:B,10):INDEX(B:B,14)) | |||
5 | AGM | =MAX(INDEX(C:C,5):INDEX(C:C,9)) | =MAX(INDEX(C:C,10):INDEX(C:C,14)) | |||
6 | AMD | =MAX(INDEX(D:D,5):INDEX(D:D,9)) | =MAX(INDEX(D:D,10):INDEX(D:D,14)) | |||
7 | ASML | =MAX(INDEX(E:E,5):INDEX(E:E,9)) | =MAX(INDEX(E:E,10):INDEX(E:E,14)) | |||
Sheet1 |
Any ideas? Many thanks.