Another way without the OFFSET functions.

Range in question is A1:A20.

Notice the pattern of the staggered ranges.

=SUMPRODUCT(MIN(A1:A16+A2:A17+A3:A18+A4:A19+A5:A20 ))

That may not be very intuitive, so this array formula** :

=MIN(SUBTOTAL(9,OFFSET(A1,ROW(A1:INDEX(A:A,ROWS(A1 :A20)-n+1))-1,,n,)))

Where n = interval. In this case n = 5.

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)


--
Biff
Microsoft Excel MVP


"Lars-Åke Aspelin" wrote in message
...
If your data are in A1:A100 try the following formula:

=SUMPRODUCT(MIN(A1:A96+OFFSET(A1:A96,1,0)+OFFSET(A 1:A96,2,0)+OFFSET(A1:A96,3,0)+OFFSET(A1:A96,4,0)))

Change the A1:A96 in all places to a range that cover all but the four
last cells of your data.

Hope this helps / Lars-Åke


On Sun, 21 Dec 2008 14:25:35 -0800 (PST), bevinb
wrote:

Sorry, I do, I want (I think) the second option there - in more
detail: the starting point could be any cell in the column, and I want
to find the five cells in sequence in that column with the lowest
overall sum

Thanks, Bevin B.


On Dec 20, 8:12 pm, "T. Valko" wrote:
You'll need to explain in more detail what "5 together" means.

Does that mean A1:A5, A6:A10, A11:A15, or does it mean A1:A5, A2:A6,
A3:A7 ?
Or, does it mean something else?

--
Biff
Microsoft Excel MVP

"bevinb" wrote in message

...

Hi, I have a long list of numbers in a column. I want to find the 5
together that have the lowest sum but do not want to sort them. i.e.,
the lowest total of the five as they are arranged on the spreadsheet
now. I thought I had done this previously and it wasn't that
difficult but now I cannot for the life of me remember how or even if
I did accomplish it! Any help appreciated.

Excel 2003

Thanks, Bevin B.