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.

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 ,
"Marky Dodd" <Marky 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.

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

Regards,

Vasant

"Domenic" wrote in message
...
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 ,
"Marky Dodd" <Marky 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, 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" wrote in message
...
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 ,
"Marky Dodd" <Marky 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.

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 ,
"Marky Dodd" 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

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 ,
"Marky Dodd" 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