How to get minimum value excluding zero?

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

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

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.

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.

Rankka,

Jerry

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

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.

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

Yes, that would be better.

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