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

[Guest]

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?

 

[RagDyer]

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))

 

[MartinW]

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

 

[Biff]

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