Find lowest total of cells in range without sorting

[bevinb]

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.

 

[L. Howard Kittle]

Hi Bevin B.

Try this;

=SUM(SMALL(A1:A18,{1,2,3,4,5}))

Of course change the range to suit.

HTH
Regards,
Howard

 

[T. Valko]

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?

 

[bevinb]

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.

 

[Lars-Åke Aspelin]

If your data are in A1:A100 try the following formula:

=SUMPRODUCT(MIN(A1:A96+OFFSET(A1:A96,1,0)+OFFSET(A1: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

 

[T. Valko]

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)