Simplify Weighted Average Formula

[ferne]

I have a s/s that has the months of the year across the top and below 3
categories for each month and I want a weighted avg for each product. eg:
A B C D E F G H I
January February March etc. to Dec
Days Oil Gas Days OIl Gas Days Oil Gas
6 12 200 4 5 180 7 8 300
formula: =(a1*b1+D1*E1+G1*h1)/(a1+d1+g1)

Is there a way to simplify this? Thank in advance for any help.

 

[Tom-S]

If you could rearrange your data table then you could use SUMPRODUCT and SUM
to get you the weighted average.

Say you have cells B1 to M1 as column headers of the months Jan to Dec, and
cells A2 to A4 as row headers for Days, Oil and Gas respectively.

It sounds like from your post you want to use Days as the weightings, so
after filling in your data in cells B2 to M4, have a formula in say B5 as
follows:

=SUMPRODUCT(B$2:M$2,B3:M3)/SUM(B$2:M$2)

which gets you a weighted average for oil.

Drag-filling the formula down to B6 will get you a weighted average for gas.

Hope that helps.

Regards,

Tom

 

[Lars-Åke Aspelin]

I have a s/s that has the months of the year across the top and below 3
categories for each month and I want a weighted avg for each product. eg:
A B C D E F G H I
January February March etc. to Dec
Days Oil Gas Days OIl Gas Days Oil Gas
6 12 200 4 5 180 7 8 300
formula: =(a1*b1+D1*E1+G1*h1)/(a1+d1+g1)

Is there a way to simplify this? Thank in advance for any help.

If you don't want to rearrange your data, try this formulas:

For the "days-weighted" average of Oil:

=SUMPRODUCT((A2:AH2="Days")*A3:AH3*B3:AI3)/SUMIF(A2:AH2,"Days",A3:AH3)

For the "days-weighted" average of Gas:

=SUMPRODUCT((A2:AH2="Days")*A3:AH3*C3:AJ3)/SUMIF(A2:AH2,"Days",A3:AH3)

Hope this helps / Lars-Åke

 

[ferne]

Thanks so much - this will work perfectly and saves me so much time as my
formula would have been too long.

 

[ferne]

Thank you for the quick response - this is for work so can't change the
spreadsheet set up however I can definitely use the knowledge for other ones
and a second response gives me a formula for not changing setup.