maximum of rolling totals - aka Rmax

[Guest]

I have a series of rows of data, for which I want to write a formula that
calulculates the maximum value of a moving total.
e.g.

period 1 2 3 4 5 6 7 8 9 10 11
sales 1 3 4 6 1 5 6 4 3 8 4

I use a sum offset function to calculate a 3 point moving total of the sales
( the number of points I total is dependant upon another cell entry). This
gives another row of formulas that result in

Rsum 8 13 11 12 12 15 13 15 15 12 4

I then use a simple max function to find the highest value within this row.

Doing it this way means I have to create a corresponding sum offset cell for
every sales cell.

I want to produce one formula that calculates the max of this Rsum for the
row of sales data.

help.

 

[Domenic]

Try...

=MAX(SUBTOTAL(9,OFFSET(B2:L2,0,COLUMN(B2:L2)-MIN(COLUMN(B2:L2)),1,3)))

....confirmed with CONTROL+SHIFT+ENTER, not just ENTER.

Hope this helps!

 

[Vasant Nanavati]

Very nice, Domenic! Hadn't seen that one before.

Regards,

Vasant

 

[Guest]

Domenic, It work !

I don't understand how, but it works.

If you would like to take the time to explain what its doing, I would be
grateful.

I recognise, and use all these functions seperately, but never in an array
formula like this.

Many thanks

 

[Domenic]

If we take a look at your example...

period 1 2 3 4 5 6 7 8 9 10 11
sales 1 3 4 6 1 5 6 4 3 8 4

....and we have the following formula...

=MAX(SUBTOTAL(9,OFFSET(B2:L2,0,COLUMN(B2:L2)-MIN(COLUMN(B2:L2)),1,3)))

....it can be broken down as follows...

COLUMN(B2:L2) returns the following array of values:

2 3 4 5 6 7 8 9 10 11 12

MIN(COLUMN(B2:L2)) returns a single value:

2

COLUMN(B2:L2)-MIN(COLUMN(B2:L2)) returns the following array of values:

0 1 2 3 4 5 6 7 8 9 10

These array of numbers are used for the third argument of OFFSET. So
what we get is an array of references....

OFFSET(B2:L2,0,0,1,3)
OFFSET(B2:L2,0,1,1,3)
OFFSET(B2:L2,0,2,1,3)
OFFSET(B2:L2,0,3,1,3)
OFFSET(B2:L2,0,4,1,3)
OFFSET(B2:L2,0,5,1,3)
OFFSET(B2:L2,0,6,1,3)
OFFSET(B2:L2,0,7,1,3)
OFFSET(B2:L2,0,8,1,3)
OFFSET(B2:L2,0,9,1,3)
OFFSET(B2:L2,0,10,1,3)

SUBTOTAL(9,OFFSET(B2:L2,0,COLUMN(B2:L2)-MIN(COLUMN(B2:L2)),1,3)) returns
the following array of numbers:

8 13 11 12 12 15 13 15 15 12 4

Lastly, the MAX function returns the maximum value within this array,
that being 15.

Hope this helps!

 

[Guest]

Yes it has !
Its helped me expanded this formula to flex automatically, dependant upon
the number of periods that are entered into the spread sheet.

Once again, Many thanks.