Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Getting smallest number 0 in an array constant
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. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Getting smallest number 0 in an array constant
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) |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Getting smallest number 0 in an array constant
=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" wrote in message ... 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. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Getting smallest number 0 in an array constant
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(IN DEX(Names= $B2,)*INDEX(LEFT(Codes,LEN($A2))=$A2,)*INDEX(LEN(C odes),), 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Vlookup - need the array to be constant | Excel Worksheet Functions | |||
Array Constant? | Excel Discussion (Misc queries) | |||
Array formula with a constant? | Excel Worksheet Functions | |||
array formula with constant | Excel Worksheet Functions | |||
Array constant issue | Excel Worksheet Functions |