maximum of rolling totals - aka Rmax

[Marky Dodd]

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!

In article <[email protected]>,
"Marky Dodd" <Marky [email protected]> wrote:

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

[Vasant Nanavati]

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

Regards,

Vasant

"Domenic" <[email protected]> wrote in message
news:[email protected]...
> 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!
>
> In article <[email protected]>,
> "Marky Dodd" <Marky [email protected]> wrote:
>
> > 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.

[Marky Dodd]

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


"Vasant Nanavati" wrote:

> Very nice, Domenic! Hadn't seen that one before.
>
> Regards,
>
> Vasant
>
> "Domenic" <[email protected]> wrote in message
> news:[email protected]...
> > 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!
> >
> > In article <[email protected]>,
> > "Marky Dodd" <Marky [email protected]> wrote:
> >
> > > 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]

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!

In article <[email protected]>,
"Marky Dodd" <[email protected]> wrote:

> 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

[Marky Dodd]

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.

"Domenic" wrote:

> 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!
>
> In article <[email protected]>,
> "Marky Dodd" <[email protected]> wrote:
>
> > 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
>