Skip navigation

How to get minimum value excluding zero?

717 Views 10 Replies Latest reply: Jul 8, 2013 11:26 PM by Barry RSS
RankkaApina Level 1 Level 1 (0 points)
Currently Being Moderated
Mar 29, 2013 4:37 PM

I'm trying to get a minimum duration value out of a column, unless the value is zero. This was the advice for excel (but it doesn't work):

=MIN(IF(Season 1 :: F>DURATION(0);Season 1 :: F))

 

So, my column with the duration values is Season 1 :: F. Any helpful hints? I saw one answer that suggested making an extra column, but I don't want extra columns (and I think this should be possible without any).

MacBook Pro (15-inch, Mid 2012), OS X Mountain Lion (10.8.2)
  • Wayne Contello Level 6 Level 6 (12,650 points)
    Currently Being Moderated
    Mar 29, 2013 8:18 PM (in response to RankkaApina)

    I cannot think of a way without an extra column.  You can hide the extra column so you don't see it.

  • Barry Level 7 Level 7 (29,095 points)
    Currently Being Moderated
    Mar 30, 2013 11:46 PM (in response to RankkaApina)

    What result do you want if the minimum is zero?

     

    How many results are there to take into account? Is it possible to have more than one zero amount?

     

    Are the zeroes in column F number values or Duration values?

     

    How do these values get into column F: Entered? Calculated by a formula?

     

    Those details might help provide a solution.

     

    Regards,

    Barry

  • Badunit Level 6 Level 6 (10,760 points)
    Currently Being Moderated
    Mar 31, 2013 6:43 PM (in response to RankkaApina)

    Are all the values positive? Here is an example using the Match function if all the values are positive durations. It isn't exactly "the minimum except for zero", it is "the minimum except for a really really small duration". This particular example is looking at column F in the same table as the formula.

     

    =OFFSET(F1,MATCH(DURATION(,,,,,0.001),F,-1)-1,0)

     

    That mashup of an Excel & Numbers function you provided would require Excel's array formula feature. Numbers doesn't have that feature.

  • seantasty Level 1 Level 1 (0 points)
    Currently Being Moderated
    Jul 5, 2013 5:36 PM (in response to RankkaApina)

    I am also trying to figure this out. There is an AVGIF function and a few other xxxIF functions that allow you to set some stipulations, yet there seems to be no MINIF function. I saw it was possible in Excel also.

     

    I have a spreadsheet set up that shows sales on a weekly basis and has an average, high, and low listed. The low is always $0.00 until we go through the entire year. I would just like it to show the lowest weekly sales amount excluding the weeks that have not yet happened. It seems like this should be an easy thing to do.

     

    I would even be ok with being able to add an IF function to the other column if you could set it to change to the word "Zero" so that the funtion would not include it. Although I think that and the other options of adding extra columns is extra work for what seems like it should be so easy to implement.

  • Jerrold Green1 Level 7 Level 7 (28,185 points)
    Currently Being Moderated
    Jul 5, 2013 6:24 PM (in response to RankkaApina)

    Rankka,

     

    Screen Shot 2013-07-05 at 9.24.06 pm.png

     

    Jerry

  • Jerrold Green1 Level 7 Level 7 (28,185 points)
    Currently Being Moderated
    Jul 5, 2013 6:31 PM (in response to Jerrold Green1)

    My edit time has expired, so I reply to my own post to correct the expression for Average. It should be:

     

    =AVERAGEIF(Season 1 :: F, ">0h")

     

    I was playing with it before creating the graphic and forgot to set it back to a full column reference.

     

    Jerry

  • Badunit Level 6 Level 6 (10,760 points)
    Currently Being Moderated
    Jul 8, 2013 7:55 AM (in response to seantasty)

    Sean,

     

    While Excel's array functions can be pretty handy, adding an extra column is a simple thing. And the column can be hidden. It is such a little amount of work to add a column to strip the zeros, leaving you with only the numbers you want to include in your MIN calculation. It could be done in less time than it took to read through this thread.

     

    Actually, given your problem statement, there is a remote possibility that sales could be $0 in a particular week so you need to check not for zeros but for some other indication that the data has been entered a each week. Maybe a column of checkmarks or something like that.

  • Jerrold Green1 Level 7 Level 7 (28,185 points)
    Currently Being Moderated
    Jul 8, 2013 1:51 PM (in response to Badunit)

    In my opinion, Sean should invetigate inititalizing his table with blanks, not zeros, so that months not yet entered will be ignored. That solves all the problems.

     

    Jerry

  • Badunit Level 6 Level 6 (10,760 points)
    Currently Being Moderated
    Jul 8, 2013 5:29 PM (in response to Jerrold Green1)

    Yes, that would be better.

  • Barry Level 7 Level 7 (29,095 points)
    Currently Being Moderated
    Jul 8, 2013 11:26 PM (in response to seantasty)

    Hi Sean,

     

    "I would even be ok with being able to add an IF function to the other column if you could set it to change to the word "Zero" so that the funtion would not include it. Although I think that and the other options of adding extra columns is extra work for what seems like it should be so easy to implement."

     

    If the amounts in the original column are created by a formula in that column, you can use the IF function there, and not bother with an added column.

     

    =IF(formula >0,formula,"")

     

    Where formula means the formula that is currently in those cells.

     

    If the amounts in this column are directly entered data, then Jerry's suggestion of beginning with empty cells in this column, rather than cells containing zeros, will be ignored in determining the minimum value.

     

    "Although I think that and the other options of adding extra columns is extra work for what seems like it should be so easy to implement."

     

    If you think that MINIF() should be a supported function in Numbers, then you should communicate that thought to Apple. Here, you're talking with a group of users like yourself. We can tell you how to achieve requested results using the existing features of the application, but we have no control or influence in changing the application itself.

     

    In the application menu (in Numbers, the 'Numbers' menu), you'll find a "Provide Numbers Feedback" item. choosing that will open a Feedback form, which you can then use to make a Feature Enhancement request for a future edition of Numbers.

     

    Regards,

    Barry

Actions

More Like This

  • Retrieving data ...

Bookmarked By (0)

Legend

  • This solved my question - 10 points
  • This helped me - 5 points
This site contains user submitted content, comments and opinions and is for informational purposes only. Apple disclaims any and all liability for the acts, omissions and conduct of any third parties in connection with or related to your use of the site. All postings and use of the content on this site are subject to the Apple Support Communities Terms of Use.