Weighted Average

[JimS]

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

11.0
12.5
14
12.5

If I average the last nine entries from the first list (1through 15),
I get 11. If I average the last six I get 12.5. And if I average the
last three I get 14. The average of those three averages is 12.5, or
the same as if I just averaged the last six.

What I want to do is a weighted average. With the most weight being
given to the last three, and then the last six and then the last nine,
so that the last nine get the least amount of weight.

Does anybody have any idea how I would go about doing that in Excel?

I haven't a clue.

Thanks

 

[Anthony Fontana]

I copied what I list below from the Help Dialog box of Excel 2003. I simply
typed in the phrase "weighted average" and came up with options.'


Calculate a weighted average

To do this task, use the SUMPRODUCT and SUM functions.

Example
The example may be easier to understand if you copy it to a blank worksheet.

How to copy an example

Create a blank workbook or worksheet.
Select the example in the Help topic.
Note Do not select the row or column headers.



Selecting an example from Help

Press CTRL+C.
In the worksheet, select cell A1, and press CTRL+V.
To switch between viewing the results and viewing the formulas that return
the results, press CTRL+` (grave accent), or on the Tools menu, point to
Formula Auditing, and then click Formula Auditing Mode.
This example calculates the average price paid for a unit across three
purchases, where each purchase is for a different number of units at a
different price per unit.


1
2
3
4
A B
Price per unit Number of units
20 500
25 750
35 200
Formula Description (Result)
=SUMPRODUCT(A2:A4,B2:B4)/SUM(B2:B4) Divides the total cost of all three
orders by the total number of units ordered (24.66)

 

[OLY]

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

11.0
12.5
14
12.5

If I average the last nine entries from the first list (1through 15),
I get 11.  If I average the last six I get 12.5.  And if I average the
last three I get 14.  The average of those three averages is 12.5, or
the same as if I just averaged the last six.

What I want to do is a weighted average.  With the most weight being
given to the last three, and then the last six and then the last nine,
so that the last nine get the least amount of weight.

Does anybody have any idea how I would go about doing that in Excel?

I haven't a clue.

Thanks

There might be a more streamlined way to do what I'm suggesting.

From what you're describing, I'm guessing you want separate averages
to three possible scenarios. 1. An average with the last three in a
series heavily weighted. 2. an average with the last six moderately
weighted. 3. an average with the last nine lightly weighted.

To do this I think you need four columns in your spreadsheet. The
first (column A) would contain your numbers 1-15. The corresponding
cells in the second, third and fourth columns (columns B, C, D) would
contain weighting factors, corresponding to the scenarios. In column
B the cells would all be the number 1 except for those that correspond
to cells in column A containing the numbers 13, 14, 15. For these
column B cells, you would enter a weighting factor of, say, 5. In
column C you would do the same thing--enter all 1's except for the
last six cells (corresponding to Column A's 10, 11, 12, 13, 14, 15).
In these column C cells enter, say, a 4. Notice that this weighting
factor is less than the 5 in column B. In column D, same thing except
the last nine cells would contain a 3.


YOu would multiply these weighting factors in columns two, three and
four by the values in your first column. respectively. And using
array formulas, you would then calculate separate weighted averages
for each scenario.

The array formulas, which would be entered into B16, C16 and D16, are:

{=AVERAGE(A1:A15*B1:B15)} = 19.2

{=AVERAGE(B1:B15*C1:C15)} = 5.4

{=AVERAGE(C1:C15*D115)} = 5.8 (This average turns out higher than
the one in column C, so maybe the weighting factor needs to be lower
than 3.)

Remember to type these without the braces at the end and enter by
pressing CONTROL-SHIFT-ENTER.

AO