9 Replies Latest reply: May 21, 2012 9:02 PM by Barry
ed1236pre Level 1 Level 1 (0 points)

I'm trying to find the minimum number in a row but I want to find a number greater than zero as the minimum not the zeros .


MacBook Pro, Mac OS X (10.4)
  • Barry Level 7 Level 7 (29,350 points)

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

     

    Regards,

    Barry

  • Barry Level 7 Level 7 (29,350 points)

    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.

    Picture 7.png

    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.

  • Jerrold Green1 Level 7 Level 7 (29,945 points)

    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.

     

    Screen Shot 2012-05-20 at 9.23.35 am.png

     

    Regards,

     

    Jerry

  • Badunit Level 6 Level 6 (11,400 points)

    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.

  • Barry Level 7 Level 7 (29,350 points)

    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.

    Picture 11.png

    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

  • Jerrold Green1 Level 7 Level 7 (29,945 points)

    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

  • Barry Level 7 Level 7 (29,350 points)

    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

  • Jerrold Green1 Level 7 Level 7 (29,945 points)

    Barry,

     

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

     

    Jerry

  • Barry Level 7 Level 7 (29,350 points)

    Always good to explore...

     

    Barry