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