3 Variable Weighted Average in Excel

2
votes

I have 3 variables that I need to take a weighted average of.

I want to excel to calculate all possible combination of weights by .01 increments.

I have three columns in excel. Weight A, Weight B and Weight C. Weight A = 1-sum(Weight B + Weight C).

Fix Weight C to .01, Cell in column B will be = cell above + 0.01 (where the very first cell is just a value cell of 0.01)

This works, but then I have to manually search for where Weight A becomes negative and then manually change the cell in column B back to 0.01 (next cell continues the formula of cell above + 0.01), and then manually change the cell in column C to 0.02 and drag it down.

As you can see, I would have to do this for C=.03, .04, .05 ....etc. And then fix column B as .01, .02, .03 etc.

Is there a faster way of doing this? i.e. Excel finding all possible combinations of the sum of 3 cells summing to one?

Thanks in advance.

1
excelalgorithmaverageweightedweighted-average
I would probably use VBA or a macro to handle this. - Tim Biegeleisen

1 Answers

0
votes

I believe I have a grasp on what you are trying to accomplish. Would your results look similar to the following?

        Weighted Sum and Reset

The formulas in A2:C2 are,

=1-SUM(B2:C2)                           ◄ A2
=IF(SUM(A1)=0, 0.01, B1+0.01)           ◄ B2
=IF(SUM(A1)=0, MAX(C$1:C1)+0.01, C1)    ◄ C2

Fill down as necessary.