Weighted Average

cflorackis

Board Regular
Joined
Aug 18, 2002
Messages
137
Dear all,

I have a large dataset that includes information on the investment payout ratios of US companies over the period 2004-2006. I need to calculate the weighted average investment ratio for each company---see sample below
example.xls
ABCDE
1companyInvest_2006Invest_2005Invest_4Weighted average
2A10847.8
3B201016
4C35N/AN/A35
5DN/A686.8
6E5N/A13.4
Sheet1


The corresponding weight for each year must be as follows: 0.5 for 2006, 0.3 for 2005 and 0.2 for 2004. Then, for company A, the weighted average will be: (0.5*B2)+(0.3*C2)+(0.1*D2) = 7.8 (see cell E2)

The problem is that for some companies there are missing data for some years. For example, for company B, there are no data available for year 2004. In this case , the weights have to change to 0.6 for 2006 and 0.4 for 2005. Then, the weighted average investment for company B will be (0.6*B3)+(0.4*C3) = 16 (see cell E3). Similarly, in cases when there are data for only 1 year , the weight has to change to 1. Then, for company C the weighted average is 35 (see cell E4)

Is there any formula to give me the weighted average as it appears in column E without having to adjust it for each single case?

many thanks in advance for your help

C.
 

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.

Gerald Higgins

Well-known Member
Joined
Mar 26, 2007
Messages
9,258
Hi C.

Are you sure you want to do the weighting as you've described ?

In your example, for company A, the weighting for 2006 is 62.5% of the combined weighting for 2006 and 2005 (5 / (5+3) ), whereas for company B, the weighting for 2006 is 60% of the combined weighting for 2006 and 2005.
In other words, 2006 is more "important" for company's with three years of data than it is for those with two years. Is this really what you wany ? I would have thought not.

If this really is what you want to do, you need to provide more data on what to do with other combinations of years, for example Company D, which has data for 2005 and 2004 - a combination not addressed in your OP.

I would suggest that you do not actually want to vary the weightings, in which case the formula will be reasonably straightforward.
 

PATSYS

Well-known Member
Joined
Mar 12, 2006
Messages
1,750
EDITTED:

Sorry, I mislooked something.

Your weights are not totalling 100%. 0.5, 0.3 and 0.1

You said that if 2004 is missing, then the new weights are 0.6 and 0.4. Are these rounded off figures? Are these calculated based on the original weights e.g. 5/8 and 3/8?
 

cflorackis

Board Regular
Joined
Aug 18, 2002
Messages
137
hi and thanks for your reply,

in the specific case I really need the weights to vary if there are missing data----for company D, the weights will still be 0.6 and 0.4 respectively----see cell E5 -----(0.6*C5)+(0.4*D5)


But just for curiosity, what would be your formula if the weights were fixed?

cheers

C.Ven
 

Gerald Higgins

Well-known Member
Joined
Mar 26, 2007
Messages
9,258
I'm sorry I still don't understand the logic of the variation in weights.

Are you saying that, if there is only data for two years, then the most recent year will be 60% and the earlier year will be 40% ?

As for the basic formula, try the solution offered by Patsys. It won't deal with variable weights, but it might work for constant weights.
 

cflorackis

Board Regular
Joined
Aug 18, 2002
Messages
137
Hi PATSYS

thanks

I tried but your formula does not seem to work----could you please provide me with an example in a spreadsheet?

Also, when you say to confirm pressing CTRL+ALT+ENTER, do you mean ALT+SHIFT + ENTER?

C.Ven
 

cflorackis

Board Regular
Joined
Aug 18, 2002
Messages
137
Gerald Higgins

yes this is what i mean, if there is data only for two years, then the most recent year will be 60% and the earlier year will be 40%

But still, what would be your formula for fixed weights?----that is, how would you control for the missing values?


C.Ven
 

PATSYS

Well-known Member
Joined
Mar 12, 2006
Messages
1,750
Hi PATSYS

thanks

I tried but your formula does not seem to work----could you please provide me with an example in a spreadsheet?

Also, when you say to confirm pressing CTRL+ALT+ENTER, do you mean ALT+SHIFT + ENTER?

C.Ven

Hi,

I meant CTRL+SHIFT+ENTER

It was an array formula.

However, I editted my post. Pls see my comment and reply.

You need to clarify how are the new weights calcualted, in the event one or 2 of the items are missing.
 

cflorackis

Board Regular
Joined
Aug 18, 2002
Messages
137
PATSYS,

the weights need to add up to 1 in each case. When 2004 is missing, the weights must be 0.6 and 0.4------I picked them randomly---that could be 0.7 and 0.3

thanks
 

PATSYS

Well-known Member
Joined
Mar 12, 2006
Messages
1,750
PATSYS,

the weights need to add up to 1 in each case. When 2004 is missing, the weights must be 0.6 and 0.4------I picked them randomly---that could be 0.7 and 0.3

thanks

Place the values 0.5, 0.3 and 0.2 in cells G1, H1, I1 repsectively.

In E2, place this formula

=SUM(IF(ISNUMBER(B2:D2);IF(B2:D2>0;B2:D2))*$G$1:$I$1)/SUM(IF(ISNUMBER(B2:D2);IF(B2:D2>0;$G$1:$I$1)))

Confirm with CTRL+SHIFT+ENTER

Copy down as necessary.

NOTE: I editted to accomodate for the N/A values.
 

Forum statistics

Threads
1,181,724
Messages
5,931,688
Members
436,798
Latest member
spprtpplcm

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top