Simplify Weighted Average Formula
in [Functions]
|
Prev: Create Formula for Weighted Average in non-consecutive cells
Next: How to Convert 24/03/2010 as text into 03/24/2010 date format
From: ferne on 5 Jun 2010 00:04 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. -- ferne
From: Tom-S on 5 Jun 2010 05:38 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 "ferne" wrote: > 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. > -- > ferne
From: Lars-�ke Aspelin on 5 Jun 2010 07:13 On Fri, 4 Jun 2010 21:04:38 -0700, ferne <ferne(a)discussions.microsoft.com> wrote: >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
From: ferne on 5 Jun 2010 09:37 Thanks so much - this will work perfectly and saves me so much time as my formula would have been too long. -- ferne "Lars-Åke Aspelin" wrote: > On Fri, 4 Jun 2010 21:04:38 -0700, ferne > <ferne(a)discussions.microsoft.com> wrote: > > >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 > . >
From: ferne on 5 Jun 2010 09:37
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. -- ferne "Tom-S" wrote: > 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 > > > "ferne" wrote: > > > 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. > > -- > > ferne |