MS Office Forum

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

SumProduct challenge

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

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
'6 periods
'12 periods

I'd like to do something like this:


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



3 Answers Found

Answer #1    Answered By: Giovanna Silva     Answered On: Sep 22

Well, I can definitely help with the divisor. If the number of periods
is x, use the following:


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


Answer #2    Answered By: Aaliyah Khan     Answered On: Sep 22

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:


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.

Answer #3    Answered By: Maurice Hanson     Answered On: Sep 22

But this might be an easier formula  for your


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