Trying to figure out how to exclude 0's from a weighted average

**roem** · 01-15-2013, 07:30 PM

I have a portfolio with a number of investments and i need to aggregate certain data such as PE ratios on a portfolio level. This entails taking the PE ratio of each investment and multiplying it by the weighted ave of the investment in the portfolio and than adding them all together. My problem is this, some investments don't have PE ratios pulled and I want to exclude the from the calc but that means that i need to adjust the weighted average of the rest of the investments in the portfolio Is there a sumproduct formula that can do that? Example is attached. Thanks in advance.

**Andrew-R** · 01-15-2013, 07:58 PM

Assuming it's column G you're trying to calculate then something like this in G2, copied down, maybe:

=IF(ISNUMBER(C2),E2/SUMIF($C$2:$C$8,">0",$E$2:$E$8),"")