 Search:

# SumProduct challenge

Asked By: Lucas    Date: Sep 22    Category: MS Office    Views: 1234

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

Share:

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.