# Weighted Average

#### cflorackis

##### Board Regular
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?

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

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

Replies
4
Views
2K
Replies
3
Views
446
Replies
1
Views
1K
Replies
2
Views
477
Replies
3
Views
709

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.

### Which adblocker are you using?

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

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