#### cflorackis

##### Board Regular

- Joined
- Aug 18, 2002

- Messages
- 137

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.