Min value to exclude zeros

**jaarthur** · 03-05-2013, 11:42 AM

Hello,

I have a spreadsheet that I am wanting to have the minimum values of certain data. Sometimes the data includes all zeros, in which case I would like to have a "-" if the minimum does not exist, instead of a bunch of zeros across my page. I would also like the formula to work when there are no zeros. Here is what I have so far...

This works fine when I am trying to do the max, if the data contains all zeros then it puts a "-", why will this same rule not apply to the MIN if I simply switch the "MAX" out with the word "MIN"?

=IF(MAX(I15:I16),MAX(I15:I16),"-") ==> works for max, does not work correctly when I try to switch to min on the row beneath that.

=MIN(IF((H15:H16)<>0,(H15:H16),"-")) ==> This works in excluding the zeros but it will not put a "-" if the row contains all zeros.

I hope that I am explaining this thouroughly. ALl help is appreciated!

**Richard Buttrey** · 03-05-2013, 12:02 PM

The two formulae are differently structured.

Doesn't the equivalent

Formula:

Please Login or Register  to view this content.

work? It seems OK to me in the sense that it gives you what you want if both H15 & H16 are zero, but also if one of them is zero as well.
But it seems an odd way of writing the formula

Formula:

Please Login or Register  to view this content.

would seem a more readable version, and will equally work as a MIN.

**jaarthur** · 03-05-2013, 02:41 PM

I agree it is much easier to read now. It is close, but if there is only one zero i need it to take the next highest number that is not zero. But if all the numbers in the row are zero then I would like the "-". Does that make sense?

**Richard Buttrey** · 03-05-2013, 03:34 PM

Ahh!
As so often happens a new consideration enters stage left to the delight of the audience.

How about

Formula:

Please Login or Register  to view this content.

Which can probably be simplified but since I've just partaken of a couple of glasses of wine at dinner I'll leave that to others.

**jaarthur** · 03-06-2013, 10:25 AM

That works flawlessly!! Thank you so much...I do have one more question that you might be able to help me with though. Hopefully. So...

I am now trying to average the averages. There are 5 averages and I am trying to get the total average of the data. Like my other question there are "-" in some of the cells. This particular data has all "-" because all of that column happened to have no data. This is the forumla that I currently have.

=IF(AVERAGE(I33,I26,I18,I11,I4)=0,"-",AVERAGE(I33,I26,I18,I11,I4))

It works if there is at least one number in the set of data but I would like the average to show "-" if all of the other averages are like that "-". Hope you can help?!