View Single Post
  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
joeu2004 joeu2004 is offline
external usenet poster
 
Posts: 2,059
Default SMALL function to find X smallest number greater than Y

On Jun 6, 12:32 am, kittronald wrote:
How can you find the X smallest number in a range that
is greater than Y ?
For example, return the second smallest number in the
range A1:A4 that is greater than 0.


On Jun 6, 1:03*am, kittronald wrote:
*** CORRECTION ***
I didn't word this problem correctly, so forget the
A1:A4 range.
If you were to create a formula that resulted in
=SMALL({0,0,10,20},2), how could you get the result
to be 20 ?


I see nothing wrong with using the A1:A4 range. Your correction is
simply that you want the __2nd__ smallest instead of the smallest.
That is indeed an important correction because my first reaction was:
20 is the __largest__, not the (1st) smallest. But it is indeed the
__2nd__ smallest if we ignore zeros.

To answer your original more general question (Xth smallest greater
than Y), suppose B1 is the value X, and B2 is the value Y. Then enter
the following __array_formula__[*]:

=SMALL(IF(A1:A100B2,A1:A100),B1)
[*] Enter an array formula by pressing ctrl+shift+Enter instead of
Enter. Excel will display an array formula surrounded by curly braces
in the Formula Bar, i.e. {=formula}. You cannot type the curly braces
yourself. If you make a mistake, select the cell, press F2 and edit,
then press ctrl+shift+Enter.