Help with an Excel formula?
 

I have a column of monthly figures to which I add the latest month's result
at the bottom. In the next column, I have a formula which, each month,
calculates the average monthly figure for the last 12 months.

I would like to change this to a weighted average - with 12 being the
weighting for last month, 11 for the previous month and so on. Clearly I
could create a formula to do this but is there an Excel function which
already does this please?

TIA

V

Help with an Excel formula?
 

Victor,

I think this will work. I put a bunch of numbers in column A beginning in
row 2. In B13 I put the following formula:
=SUMPRODUCT(A2:A13,ROW(A2:A13)-ROW(A13)+12)

I think this does what you want, although you may have been looking for
merely a built in function -- I don't think there is one.

"Victor Delta" wrote:

I have a column of monthly figures to which I add the latest month's result
at the bottom. In the next column, I have a formula which, each month,
calculates the average monthly figure for the last 12 months.

I would like to change this to a weighted average - with 12 being the
weighting for last month, 11 for the previous month and so on. Clearly I
could create a formula to do this but is there an Excel function which
already does this please?

TIA

V

Help with an Excel formula?
 

"Art" wrote in message
...
Victor,

I think this will work. I put a bunch of numbers in column A beginning in
row 2. In B13 I put the following formula:
=SUMPRODUCT(A2:A13,ROW(A2:A13)-ROW(A13)+12)

I think this does what you want, although you may have been looking for
merely a built in function -- I don't think there is one.

Art

Many thanks,

V

Help with an Excel formula?
 

"Victor Delta" wrote in message
...
"Art" wrote in message
...
Victor,

I think this will work. I put a bunch of numbers in column A beginning
in
row 2. In B13 I put the following formula:
=SUMPRODUCT(A2:A13,ROW(A2:A13)-ROW(A13)+12)

I think this does what you want, although you may have been looking for
merely a built in function -- I don't think there is one.

Art

Many thanks,

V

Well. Art, it almost worked! However, I think your formula actually needs to
be divided by 78 (the sum of the digits 1 to 12) to give the right answer.

Thanks again,

V