Weighted Averages Using Sumproduct

  • Thread starter Thread starter JimS
  • Start date Start date
J

JimS

=IF(L401="","",SUMPRODUCT(--($G$27:G400=G401)*($O$27:O400)))

This formula adds up all of the numbers that correspond to G401 in the
O column.

OK, but there might be 15, 20 or 40 "instances" of G401 in the O
column.

What I really want to do is a weighted average that doesn't go back
farther than the last nine, but if I could just get it to average,
say, the last three, five, nine, or whatever all in one formula that
would be a great start.

Is there a way to do that?
 
Jim,

For the last 5, array enter (enter using Ctrl-Shift-Enter) the formula

=IF(L401="","",SUM(IF($G$27:G400=G401,IF(ROW($G$27:$G$400)*($G$27:G400=G401)>=LARGE(ROW($G$27:$G$400)*($G$27:G400=G401),5),($O$27:O400),0),0)))/5

HTH,
Bernie
MS Excel MVP
 
Thanks Bernie! I'll play around with that.

Jim,

For the last 5, array enter (enter using Ctrl-Shift-Enter) the formula

=IF(L401="","",SUM(IF($G$27:G400=G401,IF(ROW($G$27:$G$400)*($G$27:G400=G401)>=LARGE(ROW($G$27:$G$400)*($G$27:G400=G401),5),($O$27:O400),0),0)))/5

HTH,
Bernie
MS Excel MVP
Click to expand...
 
Could we not simplify this to
=IF(L401="","",SUM(IF(ROW($G$27:$G$400)*($G$27:G400=G401)>=LARGE(ROW($G$27:$G$400)*($G$27:G400=G401),5),($O$27:O400),0)))/5

I worked on some sample data I used in A5:B30 with
=SUM(IF(ROW(A5:A30)*(A5:A30="a")>=LARGE(ROW(A5:A30)*(A5:A30="a"),5),(B5:B30),0))
 
Bernard, I played around with your second formula in a new sheet and
it works fine except for one thing. If there is less than the
required number of data points, in this case five, it then adds up all
of the data points whether they are "a" or otherwise.

So let's say I have ten data points, but only two of them are an "a."
With this formula, all of the data points will be summed.
 
Back
Top