minimum in range greater than zero

Are the 'zeroes' actual data, or the result of calculating the value of an 'empty' cell?

Regards,

Barry

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)

D2: =MIN(C)

Regards,

Barry

If the zeroes are produced by a formula, revise the formula to return an empty string where it now returns a zero.

Ed,

Here's a method that doesn't require that you use an auxiliary column, but you must know what your smalest real data value may be. In this case, I assumed that no real data would be less then 0.001.

Regards,

Jerry

Here is another way.Your numbers must all be >=0.

=SMALL(B,COUNTIF(B,0)+1)

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.

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:

Original: =IF(B=0,"",B)

Revised: =IF(B<=0,"",B)

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:

Original: =SMALL(B,COUNTIF(B,0)+1)

Revised: =SMALL(B,COUNTIF(B,"<=0")+1)

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,"

Regards,

Barry

Hi 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.

Jerry

Hi Jerry,

"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.

Regards,

Barry

Barry,

I only looked into the 1-cell solution because you took the obvious one first.🙂

Jerry

Always good to explore...🙂

Barry