View Single Post
  #5   Report Post  
Posted to microsoft.public.excel.worksheet.functions
kittronald kittronald is offline
external usenet poster
 
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.