Weighted Average

[jei eusebio]

Hi Ninjas,

Just want to know how to get the weighted average for a certain site using the data below without the use of helper columns. Thanks a lot!

Date Site Offered SL%
6/1/2015 FTP 605 74.00%
6/1/2015 Yuma 634 95.00%
6/1/2015 Sunrise 378 73.00%
6/1/2015 Miramar 656 91.00%
6/2/2015 FTP 641 96.00%
6/2/2015 Yuma 881 97.00%
6/2/2015 Sunrise 743 76.00%
6/2/2015 Miramar 674 76.00%
6/3/2015 FTP 445 80.00%
6/3/2015 Yuma 765 90.00%
6/3/2015 Sunrise 626 68.00%
6/3/2015 Miramar 438 81.00%
6/4/2015 FTP 685 85.00%
6/4/2015 Yuma 327 66.00%
6/4/2015 Sunrise 635 84.00%
6/4/2015 Miramar 626 85.00%
6/5/2015 FTP 901 94.00%
6/5/2015 Yuma 597 83.00%
6/5/2015 Sunrise 612 94.00%
6/5/2015 Miramar 826 80.00%
6/6/2015 FTP 605 71.00%
6/6/2015 Yuma 552 88.00%
6/6/2015 Sunrise 513 90.00%
6/6/2015 Miramar 875 70.00%

 

[barry houdini]

I'm not sure whether you want the weighted average of the Offered Value based on the SL% or vice versa.....anyway it's almost the same formula - for the former you can try

=SUMPRODUCT((B$2:B$25=G2)+0,C$2:C$25,D$2:D$25)/SUMIF(B$2:B$25,G2,D$2:D$25)

If it's the latter then the last range changes to C$2:C$25

Assumes you have data in columns A to D with a specific site in G2, see attached

 

[jei eusebio]

Thanks a lot Barry! Just what I was looking for!

 

[jei eusebio]

=SUMPRODUCT((J$3:J$26=O2)+0,K$3:K$26,M$3:M$26)/SUMIF(J$3:J$26,O2,K$3:K$26)

By the way Barry, may I know what the "+0" in the formula does? :D
Thanks again!

 

[barry houdini]

The expression (J$3:J$26=O2) returns an "array" of TRUE/FALSE values but for the formula to work these need to be 1/0 values - using +0 or another calculation which doesn't change the value, like *1, will "co-erce" the TRUE/FALSE values to 1/0

 

[jei eusebio]

Got it! Thank you sir!