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
