10 Replies Latest reply: Jul 8, 2013 11:26 PM by Barry
Level 1 (0 points)

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)
• Level 6 (16,935 points)

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

• Level 7 (29,610 points)

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

• Level 6 (11,515 points)

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.

• Level 1 (0 points)

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.

• Level 7 (29,955 points)

Rankka,

Jerry

• Level 7 (29,955 points)

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

• Level 6 (11,515 points)

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.

• Level 7 (29,955 points)

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

• Level 6 (11,515 points)

Yes, that would be better.

• Level 7 (29,610 points)

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