MrSamExcel
Board Regular
- Joined
- Apr 6, 2016
- Messages
- 50
- Office Version
- 365
- 2021
- Platform
- Windows
I'm trying to calculate a simple AVERAGE based on two source tabs 'Category' and 'Score'. Stipulations:
1) No helper columns on the source tabs; data from source tabs are exports from other systems that must remain untouched
2) Average scores on 'Result' tab should be calculated using a formula (not an array formula if possible), not VBA
Category tab lists all fund Names with their Ticker and Category assignment [truncated version displayed in snapshot below]
Score tab lists a custom group of tickers with their ESG and Carbon scores; not all tickers from the Score tab are on the Category tab
Result tab: Category AVERAGE scores. What formulas would generate the results currently placed in cells B2:C3? Identify Category assignment for each Ticker, calculate average Score of all Tickers that are in the same Category.
Thanks for your help.
Excel 2013/2016
<colgroup><col style="width: 25pxpx"><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>
Excel 2013/2016
<colgroup><col style="width: 25pxpx"><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>
Excel 2013/2016
<colgroup><col style="width: 25pxpx"><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>
1) No helper columns on the source tabs; data from source tabs are exports from other systems that must remain untouched
2) Average scores on 'Result' tab should be calculated using a formula (not an array formula if possible), not VBA
Category tab lists all fund Names with their Ticker and Category assignment [truncated version displayed in snapshot below]
Score tab lists a custom group of tickers with their ESG and Carbon scores; not all tickers from the Score tab are on the Category tab
Result tab: Category AVERAGE scores. What formulas would generate the results currently placed in cells B2:C3? Identify Category assignment for each Ticker, calculate average Score of all Tickers that are in the same Category.
Thanks for your help.
Excel 2013/2016
A | B | C | |
---|---|---|---|
1 | Name | Ticker | Morningstar Category |
2 | Invesco BLDRS Emerging Markets 50 ADR | ADRE | US Fund Diversified Emerging Mkts |
3 | Columbia Beyond BRICs ETF | BBRC | US Fund Diversified Emerging Mkts |
4 | First Trust BICK ETF | BICK | US Fund Diversified Emerging Mkts |
5 | iShares MSCI BRIC ETF | BKF | US Fund Diversified Emerging Mkts |
82 | Xtrackers MSCI ACWI ex USA ESG LdrsEqETF | ACSG | US Fund Foreign Large Blend |
83 | iShares MSCI ACWI ex US ETF | ACWX | US Fund Foreign Large Blend |
84 | Equbot AI Powered International Eq ETF | AIIQ | US Fund Foreign Large Blend |
85 | VictoryShares International Vol Wtd ETF | CIL | US Fund Foreign Large Blend |
86 | VictoryShares Developed Enh Vol Wtd ETF | CIZ | US Fund Foreign Large Blend |
163 | iShares Core US Aggregate Bond ETF | AGG | US Fund Intermediate-Term Bond |
164 | IQ Enhanced Core Bond US ETF | AGGE | US Fund Intermediate-Term Bond |
165 | IQ Enhanced Core Plus Bond US ETF | AGGP | US Fund Intermediate-Term Bond |
166 | WisdomTree Yield Enhanced US Aggt Bd ETF | AGGY | US Fund Intermediate-Term Bond |
167 | Vanguard Interm-Term Bond ETF | BIV | US Fund Intermediate-Term Bond |
440 | USAA MSCI USA Sm Cp Val Mom Blnd ETF | USVM | US Fund Small Blend |
441 | Vanguard Small-Cap ETF | VB | US Fund Small Blend |
442 | Vanguard S&P Small-Cap 600 ETF | VIOO | US Fund Small Blend |
443 | Vanguard Russell 2000 ETF | VTWO | US Fund Small Blend |
444 | Invesco Wilshire Micro-Cap ETF | WMCR | US Fund Small Blend |
<colgroup><col style="width: 25pxpx"><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>
Category
Excel 2013/2016
A | B | C | |
---|---|---|---|
1 | |||
2 | Ticker | ESG | Carbon |
3 | |||
4 | ADRE | ||
5 | BBRC | ||
6 | BICK | 3.951 | 280.01 |
7 | BKF | 3.7071 | 395.34 |
8 | CEY | 4.422 | 803.79 |
9 | CEZ | 4.3341 | 510.7 |
10 | DBEM | 4.3613 | 357.27 |
11 | DEM | 3.8628 | 547.71 |
12 | DEMG | 4.1945 | 874.97 |
13 | DGRE | 4.1331 | 367.89 |
14 | DGS | ||
15 | DVEM | 4.3322 | 490.16 |
16 | AGG | 6.2446 | 331.59 |
17 | AGGE | ||
18 | AGGP | ||
19 | AGGY | 5.4414 | 423.78 |
20 | BIV | 6.0983 | 299.42 |
21 | BND | 6.2375 | 354.06 |
22 | BNDC | ||
23 | BOND | ||
24 | BYLD | ||
25 | CMBS | ||
26 | EAGG | ||
27 | FBND | 5.6529 | 387.08 |
28 | FFIU | 4.8335 | 216.29 |
29 | FIBR | 4.5237 | 259.8 |
30 | FIXD | ||
31 | GBF | 6.2139 | 367.38 |
32 | ZCAN | 6.2684 | 419.04 |
33 | ZDEU | 7.1591 | 128.09 |
34 | ZGBR | 6.5471 | 124.07 |
35 | ZHOK | 5.2365 | 387.88 |
36 | ZIV | ||
37 | ZJPN | 5.6364 | 126.42 |
38 | ZMLP | ||
39 | ZROZ | 6.8942 | |
40 | ZSL |
<colgroup><col style="width: 25pxpx"><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>
Score
Excel 2013/2016
A | B | C | |
---|---|---|---|
1 | MORNINGSTAR_CATEGORY | MORNINGSTAR_CAT_ESG_SCORE | MORNINGSTAR_CAT_CARBON_SCORE |
2 | US Fund Diversified Emerging Mkts | 4.14 | 514.20 |
3 | US Fund Intermediate-Term Bond | 5.66 | 329.93 |
<colgroup><col style="width: 25pxpx"><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>
Result