Weighted average using sumproduct formula

[dreverof]

Data: # of calls (column B) = 23,808 with average hold time (column M) of 49 seconds

6,851 10 seconds


I built a sumproduct formula =sumproduct(B1:B2,M1:M2)/sum(M1:M2)


Column M is custom format mm:ss


My answer keeps coming out 47:10. Shouldn't it be 00:47? I've tried every custom format on the formula cell and cannot get the answer expressed correctly. Is it the formula that I have wrong?


Thanks so much for your feedback.

 

[Luke M]

First, double check that everything is using the same format, and eveyrthing is entered correctly. It might be worthwhile toa ctually format everything as hh:mm:ss so you can see if there's any strange data messing you up.


Also, I think your formula is actually dividing by SUM(B1:B2), else you would be getting average number of calls, not seconds (which doesn't seem to be the case).

 

[NARAYANK991]

Hi ,


To get the weighted average , you need to divide by the total number of calls , which is =SUM(B1:B2).


Format the result as hh:mm:ss.0


Narayan

 

[dreverof]

Thanks for the response. My formula logic was wrong - you are correct it should have been sum(b1:b2) <smacks forehead>


I suppose this is why a little bit of knowledge is a dangerous thing...


Many thanks for solving my problem!