Problem with SMALL Function

[ryanhannasmith]

I am having a problem with the SMALL function in Excel. I've written a
simplified example of the problem I'm having:

Example
A
1 10
2 20
3 30
4 60
5 25
6 12

When I use the =SMALL(A1:A6,1) the function returns 10
When I use the =SMALL(A1:A6,2) the function returns 10 ** this should
be 12 **
When I use the =SMALL(A1:A6,3) the function returns 12 ** this should
be 25 **

Any thoughts??

Thanks in advance,
Ryan

 

[JE McGimpsey]

Hmmm... check your formulae again - they return the correct values
(i.e., =SMALL(A1:A6,3) returns 20, not 25) for me.

For instance, are you sure the A1:A6 ranges are correct?

 

[ryanhannasmith]

I'm using this as an example. The actual data is 3000 rows and it is
too much to include here.

 

[Harlan Grove]

(e-mail address removed) wrote...

I'm using this as an example. The actual data is 3000 rows and it is
too much to include here.

....

Then you're out of luck. Given A1:A6 containing

10
20
30
60
25
12

This is correct.

These are INCORRECT.

Your formulas, as shown, produce the expected results with your overly
simplified sample data. IF Excel is giving you incorrect results, it's
critical you show the actual values in your worksheet for any of the
rest of us to diagnose the problem.

Show us the EXACT formula in your ACTUAL workbook that gives the first
incorrect result. Then show us IN FULL the value that formula returns,
that is, select the cell containing the formula, press [F2], [F9],
[Ctrl]+C, [Esc] in sequence to put the exact value into your clipboard
so you could paste it into a follow-up. Denoting the 2nd argument of
this formula as N, repeat this - formulas and values - for N-1 and N+1,
what Excel returns as the immediately smaller and larger values.

So 3 formulas and 3 values. That shouldn't take too much bandwidth.

 

[ryanhannasmith]

Thanks to everyone's input. I figured out the problem, the formula was
fine, it was my data set that was wrong. Sorry to waste anyone's time.

-Ryan

(e-mail address removed) wrote...

I'm using this as an example. The actual data is 3000 rows and it is
too much to include here.

...

Then you're out of luck. Given A1:A6 containing

10
20
30
60
25
12

This is correct.

These are INCORRECT.

Your formulas, as shown, produce the expected results with your overly
simplified sample data. IF Excel is giving you incorrect results, it's
critical you show the actual values in your worksheet for any of the
rest of us to diagnose the problem.

Show us the EXACT formula in your ACTUAL workbook that gives the first
incorrect result. Then show us IN FULL the value that formula returns,
that is, select the cell containing the formula, press [F2], [F9],
[Ctrl]+C, [Esc] in sequence to put the exact value into your clipboard
so you could paste it into a follow-up. Denoting the 2nd argument of
this formula as N, repeat this - formulas and values - for N-1 and N+1,
what Excel returns as the immediately smaller and larger values.

So 3 formulas and 3 values. That shouldn't take too much bandwidth.