# using MIN then finding the next smallest #

This is a discussion on using MIN then finding the next smallest # within the Excel Questions forums, part of the Question Forums category; I have a question about a formula I am needing....I was wondering if someone might be able to shed some ...

1. ## using MIN then finding the next smallest #

I have a question about a formula I am needing....I was wondering if someone might be able to shed some light on it.

I have a group of numbers 1, 2, 3, 1, 4, 5, 6, 7, 1, 8, 9, 0, 1

I use the MIN function to find the smallest number.......1

Well I need a formula that will then in turn find the next smallest number (excluding the number that the function MIN found.

Thank you very much, :D :o

Jeffery

2. use the SMALL function for this.

=SMALL(YourRange,2)

use 3 for third smallest number, etc.

3. Try the SMALL function instead:

=SMALL(A1:A10,2)

4. MIN of your example sample is 0, not 1!

=MIN(Range) for the minimum/smallest value.

=SMALL(Range,1)

is identical to MIN above.

Now, which one of what follows do you have in mind?

=SMALL(Range,2) for next smallest;

=SMALL(Range,COUNTIF(Range,MIN(Range))+1)

which excludes all occurrences of the samllest value.

5. Small orr min in A4 =SMALL(A2:M2,1)

may give 0 with your example

in A5
=SMALL(A2:M2,1+COUNTIF(A2:M2,A4))

Revise ranges as and cells as necessary.

or combined =SMALL(A2:M2,1+COUNTIF(A2:M2,SMALL(A2:M2,1)))

HTH Dave

6. Hi jbowes99:

Welcome to the Board!

The MIN in your posted numbers (say B1:N1) is =0. Do you mean MIN from your posted numbers that is >0, which is 1, and then the next smaller number which is >1. If this is the case then look at the following simulation ...

******** ******************** ************************************************************************>
 Microsoft Excel - Book2 ___Running: xl97 : OS = Windows 98
 (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)bout
 B3C3B4C4 =

A
B
C
D
E
F
G
H
I
J
K
L
M
N
1
*1231456718901
2
**************
3
in
B1:N1
smallest
Number
>0
smallest
Number
>1
***********
4
012***********
5
**************
 Sheet2 *

[HtmlMaker 2.20] To see the formula in the cells just click on the cells hyperlink or click the Name box
PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.

The formula in cell B4 is an array formula ...

=SMALL(IF(\$B\$1:\$N\$1>A4,\$B\$1:\$N\$1),1)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•