A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Excel » Worksheet Functions
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Simplify Weighted Average Formula



 
 
Thread Tools Display Modes
  #1  
Old June 5th, 2010, 05:04 AM posted to microsoft.public.excel.worksheet.functions
ferne
external usenet poster
 
Posts: 7
Default Simplify Weighted Average Formula

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
  #2  
Old June 5th, 2010, 10:38 AM posted to microsoft.public.excel.worksheet.functions
Tom-S[_2_]
external usenet poster
 
Posts: 68
Default Simplify Weighted Average Formula

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

  #3  
Old June 5th, 2010, 12:13 PM posted to microsoft.public.excel.worksheet.functions
Lars-Åke Aspelin[_4_]
external usenet poster
 
Posts: 74
Default Simplify Weighted Average Formula

On Fri, 4 Jun 2010 21:04:38 -0700, 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.


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
  #4  
Old June 5th, 2010, 02:37 PM posted to microsoft.public.excel.worksheet.functions
ferne
external usenet poster
 
Posts: 7
Default Simplify Weighted Average Formula

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
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
.

  #5  
Old June 5th, 2010, 02:37 PM posted to microsoft.public.excel.worksheet.functions
ferne
external usenet poster
 
Posts: 7
Default Simplify Weighted Average Formula

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

 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 09:09 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.