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.

 

[btadams]

How about this simply inelegant formula:

=IF(COUNT(B2:D2)=3,B2*0.5+C2*0.3+D2*0.1,IF(COUNT(B2:D2)<=1,SUM(B2:D2),IF(NOT(ISNUMBER(B2)),C2*0.6+D2*0.4,IF(NOT(ISNUMBER(C2)),B2*0.6+D2*0.4,B2*0.6+C2*0.4))))

 

[cflorackis]

sorry, still i cannot make it work----can you please provide the spreadsheet to see how it works in your case?

 

[cflorackis]

Hi btadams,

this one works perfectly!!!

many thanks to all of you, it is really great receiving suggestions from so clever people in this forum

C.Ven

 

[Gerald Higgins]

Sorry for not replying sooner, turned out it was a bit more complicated than I thought.

I think the solution from btadams is basically correct, but doesn't appear to deal with the twin #NA values on line 4. It also has a minor error in that the weight for 2004 is 0.1, which is an error from the OP.

Here's my suggestion -

=IF(COUNT(B2:D2)=3,(B2*0.5)+(C2*0.3)+(D2*0.2),IF(ISNA(B2),IF(ISNA(C2),D2,C2-(C2-D2)*0.4),IF(ISNA(C2),IF(ISNA(D2),B2,B2-(B2-D2)*0.4),B2-(B2-C2)*0.4)))

Slightly shorter than btadams' suggestion, and deals with the NA values.

 

[PATSYS]

I am sorry. My formula was in european settings, that mean you need to change all semi-colon ";" with comma ","

If you do that, it should work.