Forum Discussion
Continued ContributorWeighted Average for time constrained data
Microsoft EmployeeHi mrothschild ,
Purchase Date Equity Deployed Current Month Income Yield First Month Income Yield A 10/31/2018 5 2.50% 1.75% B 2/28/2019 10 1.60% 1.50% C 6/30/2019 7 2.00% 2.00%
Desired output would be: 1.93% and in Excel is calculated: =SUMPRODUCT(Table2[Equity Deployed],Table2[Current Month Income Yield])/SUM(Table2[Equity Deployed])
Is above a known data table? Or should it be calculated based on Table1? Please provide detailed description about how to get the result of [Purchase Date] and [Equity Deployed], I could not find the relationship between these two tables.
Regards,
Yuliana Gu
Continued ContributorIn my prior email, Table 2 was not related to Table 1 I was intending to use it as a simple example. I have now updated Table 2 so it is related to Table 1 and looks as follows:
In this example, the desired output is 1.60%, from the formula: =SUMPRODUCT(Table2[Equity Deployed],Table2[Current Month Income Yield])/SUM(Table2[Equity Deployed])
| Project | Asset ID | Calendar Month | Current Month | Equity Deployed | Asset ID - Month | Current Month Income Yield | Income Yield - Base (monthly) |
| A | EC135 (MSN 0467) | 10/31/2018 | 3/1/2019 | 12.60 | EC135 (MSN 0467) - 03/01/19 | 1.63% | 1.61% |
| A | EC135 (MSN 0472) | 10/31/2018 | 3/1/2019 | 12.60 | EC135 (MSN 0472) - 03/01/19 | 1.63% | 1.61% |
| A | EC145 (MSN 9084) | 10/31/2018 | 3/1/2019 | 15.20 | EC145 (MSN 9084) - 03/01/19 | 1.62% | 1.60% |
| B | H4 (MSN 4) | 2/28/2019 | 3/1/2019 | 15.28 | H4 (MSN 4) - 03/01/19 | 0.99% | 0.98% |
| B | H5 (MSN 5) | 2/28/2019 | 3/1/2019 | 15.73 | H5 (MSN 5) - 03/01/19 | 1.25% | 1.23% |
| B | H6 (MSN 6) | 2/28/2019 | 3/1/2019 | 15.90 | H6 (MSN 6) - 03/01/19 | 3.01% | 2.98% |
| C | H7 (MSN 7) | 6/30/2019 | 3/1/2019 | 15.05 | H7 (MSN 7) - 06/30/19 | 0.99% | 0.99% |
| C | H8 (MSN 8) | 6/30/2019 | 3/1/2019 | 6.18 | H8 (MSN 8) - 06/30/19 | 1.26% | 1.26% |
| C | H9 (MSN 9) | 6/30/2019 | 3/1/2019 | 5.80 | H9 (MSN 9) - 06/30/19 | 2.27% | 2.27% |
| D | H10 (MSN 10) | 12/31/2019 | 3/1/2019 | 5.95 | H10 (MSN 10) - 12/31/19 | 1.47% | 1.47% |