Find lowest total of cells in range without sorting
 

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.

Find lowest total of cells in range without sorting
 

Hi Bevin B.

Try this;

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

Of course change the range to suit.

HTH
Regards,
Howard


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

Find lowest total of cells in range without sorting
 

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.

Find lowest total of cells in range without sorting
 

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.

Find lowest total of cells in range without sorting
 

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.

Find lowest total of cells in range without sorting
 

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.