Getting smallest number > 0 in an array constant

[kittronald]

How could you get the smallest number greater than zero in the array
constant {0,0,1,2} ?

In this case, the answer would be 1.


- Ronald K.

 

[Rick Rothstein]

How could you get the smallest number greater than

zero in the array constant {0,0,1,2} ?

If it is an array constant (emphasis on the word constant), then why would
you need to do anything... you can see it is 1 and, because it is a
constant, it will always be 1. Did you by any chance attempt to simplify
your *real* question for us (and perhaps you took that simplification too
far)? If so, you should not do that... all that will get you are answers to
questions you do not really care about.

Rick Rothstein (MVP - Excel)

 

[Jim Cone]

=MIN(IF({0,0,1,2}>0,{0,0,1,2}))
....entered as an array formula.

--
Jim Cone
Portland, Oregon USA .
http://www.mediafire.com/PrimitiveSoftware .
(XL Companion add-in: compares, matches, counts, lists, finds, deletes...)





"kittronald" <[email protected]>
wrote in message
news:b873d997-fb98-4d90-aa16-a04266994fe5@z37g2000vbl.googlegroups.com...

 

[kittronald]

Rick and Jim,

Thanks for those quick replies.

Actually, I posted this problem before. 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(INDEX(Names=
$B2,)*INDEX(LEFT(Codes,LEN($A2))=$A2,)*INDEX(LEN(Codes),),
1))>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 smallest number.

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


- Ronald K.