Logo 
Search:

MS Office Forum

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

SumProduct challenge

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

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: 

 

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:

FACT(x)

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

x!

 
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:

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

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

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.




Tagged: