Currently Being ModeratedMay 19, 2012 11:39 PM (in response to ed1236pre)
Further on my reply above:
If the zeroes are real data, which you do not want to include, add an auxiliary column (C in the example) to convert the zeroes to empty strings. Take the minimum value from the auxiliary column (C). MIN() ignores text values.
C2 (and filled down): =IF(B=0,"",B)
If the zeroes are produced by a formula, revise the formula to return an empty string where it now returns a zero.
Currently Being ModeratedMay 20, 2012 6:26 AM (in response to ed1236pre)
Currently Being ModeratedMay 21, 2012 4:09 AM (in response to ed1236pre)
Here is another way.Your numbers must all be >=0.
The SMALL function arranges all the numbers in order of size. COUNTIF is counting how many zeros there are in that list so we can skip over all of them and get the first smallest number greater than zero.
Currently Being ModeratedMay 21, 2012 4:14 PM (in response to Badunit)
Badunit's remark that "Your numbers must all be >=0" led me to check all three suggestions against this possiblity. Here are the results for two data sets, one containing text, blank and negative values, the second containing only positve numbers, zero, and a blank cell.
As you can see, my procedure includes both positive and negative values, and eliminates only the zeros from contention. If your data will include negative values, and you do NOT wish to include them, they may be easily stripped from the data when it is transfered the the Aux column, in the same manner as the zeroes have been.
Stripping all values less than or equal to zero requies a minor edit of the formula in the AUX column:
Jerry's formula requires that it be placed into a Footer row (or a Header row) IF it is to be placed on the Data table and to use the table name ("Data") as the first argument for INDEX. INDEX ignores Header and Footer rows in arguments specifying a whole column (or a whole table).
As can be seen in column B, this formula will be thrown off (as BU has warned) by the inclusion of negative values. A revision similar to the one shown in my formula above will remove the requirement to not include negative values:
As revised, the formula will return the smallest positive value in the data list.
Badunit's formula will also show a warning triangle if there is an empty cell or a cell containing text in the data list. Annoying, but it does not affect the results. The warning is that "The formula refers to cells without numbers,"
Currently Being ModeratedMay 21, 2012 5:35 PM (in response to Barry)
When Ed stated: "I want to find a number greater than zero as the minimum", I guess I disregarded the negative possibilities. I wonder if he will ever return to see the discussion he has set into motion.
Currently Being ModeratedMay 21, 2012 6:46 PM (in response to Jerrold Green1)
"When Ed stated: "I want to find a number greater than zero as the minimum", I guess I disregarded the negative possibilities."
I did too. It wasn't until BU mentioned it that I considered negatives (and non-numerical values). Thought they were worth exploring.