Weighted Average

[cflorackis]

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
	A	B	C	D	E
1	company	Invest_2006	Invest_2005	Invest_4	Weighted average
2	A	10	8	4	7.8
3	B	20	10		16
4	C	35	N/A	N/A	35
5	D	N/A	6	8	6.8
6	E	5	N/A	1	3.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.

 

[Gerald Higgins]

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]

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]

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]

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]

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]

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]

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]

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]

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.