using MIN then finding the next smallest #

[jbowes99]

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

[PHMayfield]

use the SMALL function for this.

=SMALL(YourRange,2)

use 3 for third smallest number, etc.

[phantom1975]

Try the SMALL function instead:

=SMALL(A1:A10,2)

[Aladin Akyurek]

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.

[Dave Patton]

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

[Yogi Anand]

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

*

1

2

3

1

4

5

6

7

1

8

9

0

1

2

*

*

*

*

*

*

*

*

*

*

*

*

*

*

3

in B1:N1

smallest Number >0

smallest Number >1

*

*

*

*

*

*

*

*

*

*

*

4

0

1

2

*

*

*

*

*

*

*

*

*

*

*

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)