Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old June 6th 11, 08:32 AM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Apr 2011
Posts: 162
Default SMALL function to find X smallest number greater than Y

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.

A
1 0
2 0
3 10
4 20

The result should be 20.

Any ideas ?


- Ronald K.

  #2   Report Post  
Old June 6th 11, 09:03 AM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Apr 2011
Posts: 162
Default SMALL function to find X smallest number greater than Y

*** 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 ?

Is there a way to replace the zero values with text and then use the
SMALL function ?


- Ronald K.
  #3   Report Post  
Old June 6th 11, 11:45 AM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Jan 2007
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.
  #4   Report Post  
Old June 6th 11, 11:47 AM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: May 2010
Posts: 420
Default SMALL function to find X smallest number greater than Y

=COUNTIF(A1:A4,"<="&0))
will give you the count of values less than or equal to 0.

So if you want to ignore those values, you could just look for the
(2 + that number) smallest:

=SMALL(A1:A4,2+COUNTIF(A1:A4,"<="&0))

I like this syntax:
=COUNTIF(A1:A4,"<="&0))
but you could use:
=COUNTIF(A1:A4,"<=0"))

But if you put that value in X99, the first is easier to modify:
=COUNTIF(A1:A4,"<="&x99))



On 06/06/2011 02:32, 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.

A
1 0
2 0
3 10
4 20

The result should be 20.

Any ideas ?


- Ronald K.


--
Dave Peterson
  #5   Report Post  
Old June 8th 11, 01:10 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Apr 2011
Posts: 162
Default SMALL function to find smallest number greater than 0

Joe and Dave,

Thanks for those quick replies.

Unfortunately, the curly braces within the parentheses of my second
post weren't very noticeable.

In my haste to post, I incorrectly described the problem.

Here's an accurate, albeit more involved example.

A grocery store is going through its inventory and is interested in
tracking only certain types of items.

For example, each type of fruit is assigned a code. When a
different vendor is used to obtain that type of fruit, the previous
vendor's code is retired by appending a letter or number to that code
(i.e., OA changes to OA1). The new vendor is assigned the original
code (i.e., OA). Over time, retired codes appear many times in the
inventory.

The goal is to look for duplicates and return a value of
"Duplicate" where that code will be ignored in other formulas.

A B
1 Code Name
2 A Apple
3 P Pear
4 OA Orange
5 OA1 Orange

Two named ranges exist:

Codes = $A$2:$A$5

Names = $B$2:$B$5

In C2:C5, the following formula is entered:

=IF(COUNTIF(Names,$B2)=1,"",IF(SUMPRODUCT(SMALL(IN DEX(Names=
$B2,)*INDEX(LEFT(Codes,LEN($A2))=$A2,)*INDEX(LEN(C odes),),
2))0,"","Duplicate"))

In cells C2 and C3, the formula correctly returns "".

However, in cell C4, the formula returns "Duplicate", because out
of {0,0,1,2}, 0 is the second smallest number.

What I'm trying to do is get the formula to exclude the 0's and
return the smallest number in that set.



- Ronald K.


  #6   Report Post  
Old June 8th 11, 01:12 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Apr 2011
Posts: 162
Default SMALL function to find smallest number greater than 0

*** CORRECTION ***

The formula should read:

=IF(COUNTIF(Names,$B2)=1,"",IF(SUMPRODUCT(SMALL(IN DEX(Names=
$B2,)*INDEX(LEFT(Codes,LEN($A2))=$A2,)*INDEX(LEN(C odes),),
1))0,"","Duplicate"))


- Ronald K.
  #7   Report Post  
Old June 15th 11, 12:35 AM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Apr 2011
Posts: 162
Default SMALL function to find smallest number greater than 0

Dave,

How can you get COUNTIF to use an array constant ?

For example, =COUNTIF(Names=$B2,"=0")



- Ronald K.


Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
find smallest positive number among datas Cerberus[_2_] Excel Worksheet Functions 6 March 17th 10 12:58 AM
Visual Script to find smallest number greater than 0 Jeremy Excel Discussion (Misc queries) 3 October 30th 09 08:32 PM
Find X number of smallest values in a range Steve Haack Excel Worksheet Functions 1 August 14th 07 05:02 AM
find smallest number not equal to 0 dave Excel Worksheet Functions 2 April 20th 05 10:56 PM
The formula to find the smallest number in a row not equal to zero seastheday Excel Worksheet Functions 8 April 14th 05 11:58 PM


All times are GMT +1. The time now is 12:06 PM.

Powered by vBulletin® Copyright ©2000 - 2021, Jelsoft Enterprises Ltd.
Copyright 2004-2021 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017