Search: | |||||

| ||||

Home » Forum » MS Office | RSS Feeds |

Is it possible to do a weighted average, where the length

can be varied?

Right now, every time I want to change the length, I have to

change the entire formula and copy down the column:

'3 periods

=SUMPRODUCT({1;2;3},$N32:$N34)/6

'6 periods

=SUMPRODUCT({1;2;3;4;5;6},$N29:$N34)/21

'12 periods

=SUMPRODUCT({1;2;3;4;5;6;7;8;9;10;11;12},$N23:$N34)/78

I'd like to do something like this:

=SUMPRODUCT({1;2;3;4;5;6;7;8;9;10;11;12;13;14;15},

OFFSET($N33,0,0,-BK$2,1))/120

The OFFSET part works to vary the length (BK$2) of the

range (column $N), but I don't know how to do the SUMPRODUCT

array and divisor (120).

Well, I can definitely help with the divisor. If the number of periods

is x, use the following:

FACT(x)

The FACT function returns the factorial, more commonly designated thus:

x!

I think that won't work -- Factorial would multiply, and for

a weighted average divisor, it would have to add the weights.

Got this working for the divisor, though:

=COUNT(OFFSET($N10,0,0,-BK$2,1))*

(1+COUNT(OFFSET($N10,0,0,-BK$2,1)))/2

Now, just need to make the array {1;2;3;4;5;6;7;8;9;10;11;12}

part "flexible"...

Don't know if that can be done; have also tried writing in

VBA, and have posted as separate msg.

But this might be an easier formula for your

divisor:

(x+1)/2*(x)

Didn't find what you were looking for?
Find more on SumProduct challenge
Or get search suggestion and latest updates.