MrSamExcel
Board Regular
- Joined
- Apr 6, 2016
- Messages
- 51
- 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>
[TD="align: center"]1[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]4[/TD]
[TD="align: center"]5[/TD]
[TD="align: center"]82[/TD]
[TD="align: center"]83[/TD]
[TD="align: center"]84[/TD]
[TD="align: center"]85[/TD]
[TD="align: center"]86[/TD]
[TD="align: center"]163[/TD]
[TD="align: center"]164[/TD]
[TD="align: center"]165[/TD]
[TD="align: center"]166[/TD]
[TD="align: center"]167[/TD]
[TD="align: center"]440[/TD]
[TD="align: center"]441[/TD]
[TD="align: center"]442[/TD]
[TD="align: center"]443[/TD]
[TD="align: center"]444[/TD]
</tbody>
Excel 2013/2016
<colgroup><col style="width: 25pxpx"><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"]2[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D3D3D3]#D3D3D3[/URL] "]Ticker[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D3D3D3]#D3D3D3[/URL] , align: right"]ESG[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D3D3D3]#D3D3D3[/URL] , align: right"]Carbon[/TD]
[TD="align: center"]3[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"]4[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"]5[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"]6[/TD]
[TD="align: right"]3.951[/TD]
[TD="align: right"]280.01[/TD]
[TD="align: center"]7[/TD]
[TD="align: right"]3.7071[/TD]
[TD="align: right"]395.34[/TD]
[TD="align: center"]8[/TD]
[TD="align: right"]4.422[/TD]
[TD="align: right"]803.79[/TD]
[TD="align: center"]9[/TD]
[TD="align: right"]4.3341[/TD]
[TD="align: right"]510.7[/TD]
[TD="align: center"]10[/TD]
[TD="align: right"]4.3613[/TD]
[TD="align: right"]357.27[/TD]
[TD="align: center"]11[/TD]
[TD="align: right"]3.8628[/TD]
[TD="align: right"]547.71[/TD]
[TD="align: center"]12[/TD]
[TD="align: right"]4.1945[/TD]
[TD="align: right"]874.97[/TD]
[TD="align: center"]13[/TD]
[TD="align: right"]4.1331[/TD]
[TD="align: right"]367.89[/TD]
[TD="align: center"]14[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"]15[/TD]
[TD="align: right"]4.3322[/TD]
[TD="align: right"]490.16[/TD]
[TD="align: center"]16[/TD]
[TD="align: right"]6.2446[/TD]
[TD="align: right"]331.59[/TD]
[TD="align: center"]17[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"]18[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"]19[/TD]
[TD="align: right"]5.4414[/TD]
[TD="align: right"]423.78[/TD]
[TD="align: center"]20[/TD]
[TD="align: right"]6.0983[/TD]
[TD="align: right"]299.42[/TD]
[TD="align: center"]21[/TD]
[TD="align: right"]6.2375[/TD]
[TD="align: right"]354.06[/TD]
[TD="align: center"]22[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"]23[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"]24[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"]25[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"]26[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"]27[/TD]
[TD="align: right"]5.6529[/TD]
[TD="align: right"]387.08[/TD]
[TD="align: center"]28[/TD]
[TD="align: right"]4.8335[/TD]
[TD="align: right"]216.29[/TD]
[TD="align: center"]29[/TD]
[TD="align: right"]4.5237[/TD]
[TD="align: right"]259.8[/TD]
[TD="align: center"]30[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"]31[/TD]
[TD="align: right"]6.2139[/TD]
[TD="align: right"]367.38[/TD]
[TD="align: center"]32[/TD]
[TD="align: right"]6.2684[/TD]
[TD="align: right"]419.04[/TD]
[TD="align: center"]33[/TD]
[TD="align: right"]7.1591[/TD]
[TD="align: right"]128.09[/TD]
[TD="align: center"]34[/TD]
[TD="align: right"]6.5471[/TD]
[TD="align: right"]124.07[/TD]
[TD="align: center"]35[/TD]
[TD="align: right"]5.2365[/TD]
[TD="align: right"]387.88[/TD]
[TD="align: center"]36[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"]37[/TD]
[TD="align: right"]5.6364[/TD]
[TD="align: right"]126.42[/TD]
[TD="align: center"]38[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"]39[/TD]
[TD="align: right"]6.8942[/TD]
[TD="align: right"][/TD]
[TD="align: center"]40[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
</tbody>
Excel 2013/2016
<colgroup><col style="width: 25pxpx"><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="align: right"]MORNINGSTAR_CAT_ESG_SCORE[/TD]
[TD="align: right"]MORNINGSTAR_CAT_CARBON_SCORE[/TD]
[TD="align: center"]2[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFF00]#FFFF00[/URL] , align: right"]4.14[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFF00]#FFFF00[/URL] , align: right"]514.20[/TD]
[TD="align: center"]3[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFF00]#FFFF00[/URL] , align: right"]5.66[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFF00]#FFFF00[/URL] , align: right"]329.93[/TD]
</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 | |
---|---|---|---|
Name | Ticker | Morningstar Category | |
Invesco BLDRS Emerging Markets 50 ADR | ADRE | US Fund Diversified Emerging Mkts | |
Columbia Beyond BRICs ETF | BBRC | US Fund Diversified Emerging Mkts | |
First Trust BICK ETF | BICK | US Fund Diversified Emerging Mkts | |
iShares MSCI BRIC ETF | BKF | US Fund Diversified Emerging Mkts | |
Xtrackers MSCI ACWI ex USA ESG LdrsEqETF | ACSG | US Fund Foreign Large Blend | |
iShares MSCI ACWI ex US ETF | ACWX | US Fund Foreign Large Blend | |
Equbot AI Powered International Eq ETF | AIIQ | US Fund Foreign Large Blend | |
VictoryShares International Vol Wtd ETF | CIL | US Fund Foreign Large Blend | |
VictoryShares Developed Enh Vol Wtd ETF | CIZ | US Fund Foreign Large Blend | |
iShares Core US Aggregate Bond ETF | AGG | US Fund Intermediate-Term Bond | |
IQ Enhanced Core Bond US ETF | AGGE | US Fund Intermediate-Term Bond | |
IQ Enhanced Core Plus Bond US ETF | AGGP | US Fund Intermediate-Term Bond | |
WisdomTree Yield Enhanced US Aggt Bd ETF | AGGY | US Fund Intermediate-Term Bond | |
Vanguard Interm-Term Bond ETF | BIV | US Fund Intermediate-Term Bond | |
USAA MSCI USA Sm Cp Val Mom Blnd ETF | USVM | US Fund Small Blend | |
Vanguard Small-Cap ETF | VB | US Fund Small Blend | |
Vanguard S&P Small-Cap 600 ETF | VIOO | US Fund Small Blend | |
Vanguard Russell 2000 ETF | VTWO | US Fund Small Blend | |
Invesco Wilshire Micro-Cap ETF | WMCR | US Fund Small Blend |
<colgroup><col style="width: 25pxpx"><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]4[/TD]
[TD="align: center"]5[/TD]
[TD="align: center"]82[/TD]
[TD="align: center"]83[/TD]
[TD="align: center"]84[/TD]
[TD="align: center"]85[/TD]
[TD="align: center"]86[/TD]
[TD="align: center"]163[/TD]
[TD="align: center"]164[/TD]
[TD="align: center"]165[/TD]
[TD="align: center"]166[/TD]
[TD="align: center"]167[/TD]
[TD="align: center"]440[/TD]
[TD="align: center"]441[/TD]
[TD="align: center"]442[/TD]
[TD="align: center"]443[/TD]
[TD="align: center"]444[/TD]
</tbody>
Category
Excel 2013/2016
A | B | C | |
---|---|---|---|
ADRE | |||
BBRC | |||
BICK | |||
BKF | |||
CEY | |||
CEZ | |||
DBEM | |||
DEM | |||
DEMG | |||
DGRE | |||
DGS | |||
DVEM | |||
AGG | |||
AGGE | |||
AGGP | |||
AGGY | |||
BIV | |||
BND | |||
BNDC | |||
BOND | |||
BYLD | |||
CMBS | |||
EAGG | |||
FBND | |||
FFIU | |||
FIBR | |||
FIXD | |||
GBF | |||
ZCAN | |||
ZDEU | |||
ZGBR | |||
ZHOK | |||
ZIV | |||
ZJPN | |||
ZMLP | |||
ZROZ | |||
ZSL |
<colgroup><col style="width: 25pxpx"><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"]2[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D3D3D3]#D3D3D3[/URL] "]Ticker[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D3D3D3]#D3D3D3[/URL] , align: right"]ESG[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D3D3D3]#D3D3D3[/URL] , align: right"]Carbon[/TD]
[TD="align: center"]3[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"]4[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"]5[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"]6[/TD]
[TD="align: right"]3.951[/TD]
[TD="align: right"]280.01[/TD]
[TD="align: center"]7[/TD]
[TD="align: right"]3.7071[/TD]
[TD="align: right"]395.34[/TD]
[TD="align: center"]8[/TD]
[TD="align: right"]4.422[/TD]
[TD="align: right"]803.79[/TD]
[TD="align: center"]9[/TD]
[TD="align: right"]4.3341[/TD]
[TD="align: right"]510.7[/TD]
[TD="align: center"]10[/TD]
[TD="align: right"]4.3613[/TD]
[TD="align: right"]357.27[/TD]
[TD="align: center"]11[/TD]
[TD="align: right"]3.8628[/TD]
[TD="align: right"]547.71[/TD]
[TD="align: center"]12[/TD]
[TD="align: right"]4.1945[/TD]
[TD="align: right"]874.97[/TD]
[TD="align: center"]13[/TD]
[TD="align: right"]4.1331[/TD]
[TD="align: right"]367.89[/TD]
[TD="align: center"]14[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"]15[/TD]
[TD="align: right"]4.3322[/TD]
[TD="align: right"]490.16[/TD]
[TD="align: center"]16[/TD]
[TD="align: right"]6.2446[/TD]
[TD="align: right"]331.59[/TD]
[TD="align: center"]17[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"]18[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"]19[/TD]
[TD="align: right"]5.4414[/TD]
[TD="align: right"]423.78[/TD]
[TD="align: center"]20[/TD]
[TD="align: right"]6.0983[/TD]
[TD="align: right"]299.42[/TD]
[TD="align: center"]21[/TD]
[TD="align: right"]6.2375[/TD]
[TD="align: right"]354.06[/TD]
[TD="align: center"]22[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"]23[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"]24[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"]25[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"]26[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"]27[/TD]
[TD="align: right"]5.6529[/TD]
[TD="align: right"]387.08[/TD]
[TD="align: center"]28[/TD]
[TD="align: right"]4.8335[/TD]
[TD="align: right"]216.29[/TD]
[TD="align: center"]29[/TD]
[TD="align: right"]4.5237[/TD]
[TD="align: right"]259.8[/TD]
[TD="align: center"]30[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"]31[/TD]
[TD="align: right"]6.2139[/TD]
[TD="align: right"]367.38[/TD]
[TD="align: center"]32[/TD]
[TD="align: right"]6.2684[/TD]
[TD="align: right"]419.04[/TD]
[TD="align: center"]33[/TD]
[TD="align: right"]7.1591[/TD]
[TD="align: right"]128.09[/TD]
[TD="align: center"]34[/TD]
[TD="align: right"]6.5471[/TD]
[TD="align: right"]124.07[/TD]
[TD="align: center"]35[/TD]
[TD="align: right"]5.2365[/TD]
[TD="align: right"]387.88[/TD]
[TD="align: center"]36[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"]37[/TD]
[TD="align: right"]5.6364[/TD]
[TD="align: right"]126.42[/TD]
[TD="align: center"]38[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"]39[/TD]
[TD="align: right"]6.8942[/TD]
[TD="align: right"][/TD]
[TD="align: center"]40[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
</tbody>
Score
Excel 2013/2016
A | B | C | |
---|---|---|---|
MORNINGSTAR_CATEGORY | |||
US Fund Diversified Emerging Mkts | |||
US Fund Intermediate-Term Bond |
<colgroup><col style="width: 25pxpx"><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="align: right"]MORNINGSTAR_CAT_ESG_SCORE[/TD]
[TD="align: right"]MORNINGSTAR_CAT_CARBON_SCORE[/TD]
[TD="align: center"]2[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFF00]#FFFF00[/URL] , align: right"]4.14[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFF00]#FFFF00[/URL] , align: right"]514.20[/TD]
[TD="align: center"]3[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFF00]#FFFF00[/URL] , align: right"]5.66[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFF00]#FFFF00[/URL] , align: right"]329.93[/TD]
</tbody>
Result