find two (2) lowest values in a range (Excel)

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Bob in Carson - 15 Aug 2006 02:28 GMT

I want to be able to use Excel to find the two lowest of 7 values in a
non-contiguous range of cells. I know how to use MIN to find the lowest, but
how to find the next lowest?

Reply to this Message

RagDyer - 15 Aug 2006 02:56 GMT

You could assign a name to your non-contiguous range, say "list" (no
quotes),
And then try this:

=SMALL(list,2)

OR
you could sort the range, descending, by copying this formula down:

=SMALL(list,ROW(A1))

Signature

HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------

>I want to be able to use Excel to find the two lowest of 7 values in a
> non-contiguous range of cells. I know how to use MIN to find the lowest,
> but
> how to find the next lowest?

Reply to this Message

MartinW - 15 Aug 2006 03:03 GMT

Hi Bob,

=SMALL(A1:A7,1) will return the lowest value
=SMALL(A1:A7,2) will return the second lowest value etc.

To handle the non contiguous part I think you need to put
in a helper column to make the range contiguous.

If you need the two values in the one cell you could use
a formula like
=SMALL(A1:A7,1)&"-"&SMALL(A1:A7,2)

HTH
Martin

Reply to this Message

Biff - 15 Aug 2006 04:24 GMT

Another one:

=SMALL((A$1,A$4,A$6,A$10,A$15,B$8,C$20),ROWS($1:1))

Copied down to give Small 2, Small 3,Small 4 etc.

Biff

>I want to be able to use Excel to find the two lowest of 7 values in a
> non-contiguous range of cells. I know how to use MIN to find the lowest,
> but
> how to find the next lowest?

Reply to this Message