XcelLearner
Board Regular
- Joined
- Feb 6, 2016
- Messages
- 52
- Office Version
- 365
- 2016
- Platform
- Windows
I have a portfolio with hundred of stocks, but for simplicity, let's say I have three stocks, with their respective PERs and market caps:
<tbody>
</tbody>
I would like to take a market-cap-weighted PER for the entire portfolio, and the formula would be "SUMPRODUCT(B2:B5,C2:C5)/SUM(C2:C5)".
Suddenly, I want to have a market-cap-weighted PER for a portfolio of stocks with their market caps larger than 100. The result should be 12.99. However, I don't know how to revise the formula with condition of market cap >100.
Could you please help? Thank you very much.
PER | Market Cap | |
ABC | 15 | 150 |
DEF | 10 | 101 |
XYZ | 12 | 80 |
Market-cap-weighted average | 12.99x | |
Formula | ? |
<tbody>
</tbody>
I would like to take a market-cap-weighted PER for the entire portfolio, and the formula would be "SUMPRODUCT(B2:B5,C2:C5)/SUM(C2:C5)".
Suddenly, I want to have a market-cap-weighted PER for a portfolio of stocks with their market caps larger than 100. The result should be 12.99. However, I don't know how to revise the formula with condition of market cap >100.
Could you please help? Thank you very much.