Hi
How does one change the formulas in row 1 so that they automatically apply only to the filtered range when for example, I want to filter by sector (column J). I´ve seen this before but have no clue if it´s a formula or a setting.
Thanks for any help!
How does one change the formulas in row 1 so that they automatically apply only to the filtered range when for example, I want to filter by sector (column J). I´ve seen this before but have no clue if it´s a formula or a setting.
Thanks for any help!
01 Master + Observations.xlsx | ||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|
A | B | C | D | E | F | G | H | I | J | |||
1 | 12 | 22 | 47.5% | 39 | 35 | 32 | 32 | 32 | SECTOR | |||
2 | Column1 | Column2 | Column3 | Column4 | Column5 | Column6 | Column7 | Column8 | Column9 | |||
3 | 01 AAPL.xlsx | 4 | 5 | 23% | 1 | 1 | 1 | 1 | 1 | Information Technology | ||
4 | 02 MSFT.xlsx | 0 | 0 | 0 | 0 | 0 | Information Technology | |||||
5 | 03 GOOG.xlsx | 13 | 39 | 27% | 1 | 1 | 1 | 1 | 1 | Communication Services | ||
6 | 04 AMZM.xlsx | 13 | 19 | 47% | 12 | 11 | 9 | 9 | 9 | Consumer Discretionary | ||
7 | 05 BRK-B.xlsx | 0 | 0 | 0 | 0 | 0 | Financials | |||||
8 | 06 TSLA.xlsx | 9 | 21 | 46% | 6 | 5 | 4 | 5 | 5 | Consumer Discretionary | ||
9 | 001 LAC - Copy.xlsx | 13 | 22 | 24% | 3 | 2 | 3 | 2 | 1 | Health Care | ||
10 | 08 JNJ.xlsx | 0 | 0 | 0 | 0 | 0 | Health Care | |||||
11 | 09 XOM.xlsx | 0 | 0 | 0 | 0 | 0 | Energy | |||||
12 | 10 JPM.xlsx | 18 | 37 | 17% | 1 | 0 | 1 | 1 | 1 | Financials | ||
13 | 11 NVDA.xlsx | 10 | 25 | 59% | 14 | 14 | 12 | 12 | 13 | Information Technology | ||
14 | 001 - Copy.xlsx | 0 | 0 | 0 | 0 | 0 | Information Technology | |||||
15 | 13 WMT.xlsx | 16 | 6 | 47% | 1 | 1 | 1 | 1 | 1 | Consumer Staples | ||
16 | 14 CVX.xlsx | 0 | 0 | 0 | 0 | 0 | Energy | |||||
17 | 15 LLY.xlsx | 0 | 0 | 0 | 0 | 0 | Health Care | |||||
Sheet2 |
Cell Formulas | ||
---|---|---|
Range | Formula | |
B1 | B1 | =AVERAGE(AAPL[Column2]) |
C1 | C1 | =AVERAGE(AAPL[Column3]) |
D1 | D1 | =SUMPRODUCT(AAPL[Column4],AAPL[Column5])/SUM(AAPL[Column5]) |
E1:I1 | E1 | =SUM(E3:E17) |