Weighted Average (math) question?

[hmmmidk220]

Hi!

This might be more of a math question but here goes:

I have two different contract rates, $52.38 and $52.50. Each rate corresponds to a certain amount of revenue, lets say $30310 and $1253 respectively.

What would be the weighted average of those two rates?

I got 52.39 using sum product (https://exceljet.net/formula/weighted-average) or just by using this : =((C188*C150)+(C151*C189))/SUM(C150:C151)

Is that right?

At first I tried taking the ratio of the first rate's revenue and multiplying it by the regular average but I guess that's not right (different result?)

=(C150/(SUM(C150:C151)))*(SUM(C188:C189)/2)

Thanks!

 

[Oaktree]

Think of it this way: your total revenue is $30,310 + $1,253 = $31,563.

~96% (30,310/31,563) of that revenue came at a $52.38 rate and the other ~4% came at the 52.50 rate.

96% * 52.38 + 4% * 52.50 = 52.38476 blended rate (using the calculated %s and not the rounded 96%/4% shown above). That's the same number you'd get with the SUMPRODUCT approach.

You should *not* use the regular average per the last formula you posted, as it assumes equal weighting for your two rates. We know the actual results skewed heavily toward the $52.38.