CODEWITHSUNDEEP
excelexcel-formula
jnmf
jnmf

Reputation: 137

Calculate weighted mean in excel

I'd like some help writing this excel formula.

I'm trying to calculate the weighted mean of term payments accordingly to their percentage of use but I'm failing on the last part of the logic expression.

=IF(OR(B2>0,B3>0,B4>0,B5>0),(SUMIF(B2:B5,">=1",D2:D5))/(SUM(C2:C5)),0)

%[B]    days[C]    weighted mean[D=BxC]
30        1           30
70       30          2100
0        60           0
0        90           0
total                 68.70967742

What I'd like to do is sum all lines in [D], if the percentage of [B] is >=1. Then I want to divide this by the sum of the days in [C] if their corresponding cell in [B] is >=1

So in this example the answer would be 68.70967742 --- [(30x1)+(70x30)]/(1+30). What I came up with returns 11.7679558.

Thanks

Upvotes: 3

Views: 230

Answers (1)

CallumDA
CallumDA

Reputation: 12113

When calculating weighted averages, you'll find SUMPRODUCT is very useful!

=SUMPRODUCT($B$1:$B$4,$C$1:$C$4)/SUMIF($B$1:$B$4,">0",$C$1:$C$4)

This returned 68.7096774 as required.

Upvotes: 2

Related Questions