Ignoring zero values in min and max functions - non contiguous cells

**milleribsen** · 11-02-2011, 05:03 PM

I have a very large data sheet and I need to find the max and min of various cells in a row. These cells are not contiguous, and I need to ignore zero values. If i were to simply need a min of these cells the formula would be as follows:

=MIN(D4,G4,M4,S4,Y4,AE4,AK4,AQ4,AW4,BC4,BI4)

Because this formula needs to go down the entire 800 rows of this report I can't do it by hand.

Excel 2010.

Thanks!

**Paul** · 11-02-2011, 10:05 PM

Hi Milleribsen, welcome to the forum.

Based on pgc01's first post in this thread (http://www.mrexcel.com/forum/showthread.php?t=226575) this formula should hopefully work for you.

=SMALL((D4,G4,M4,S4,Y4,AE4,AK4,AQ4,AW4,BC4,BI4),1+FREQUENCY((D4,G4,M4,S4,Y4,AE4,AK4,AQ4,AW4,BC4,BI4),0))

If you have hundreds of cells, though, it may get quite long and a macro would work better.

Hope it helps!

**Andrew-R** · 11-03-2011, 03:56 AM

If your range is D4:BI4 then try =MIN(IF(D4:BI4<>0,D4:BI4)) entered as an array formula (i.e. using Ctrl-Shift-Enter).

**Paul** · 11-03-2011, 11:26 AM

I assumed that the range was non-contiguous and that there were other numbers in the row that needed to be excluded (which is why you were averaging individual cells).

If that's not the case, Andrew's formula is much simpler as it will only look at numeric cells, and exclude any that have a zero value. The Average function also excludes blank (unused) cells.

**milleribsen** · 11-03-2011, 01:08 PM

You're correct, there are other value numbers in the row. I'll take a look at both options and hopefully something will work!

**Andrew-R** · 11-03-2011, 02:38 PM

Is there something in the headers for each row that you could look at?

For example, this formula returns the minimum values of row 4 looking only at cells where there is an X in row 1.

=MIN(IF(D$1:BI$1="X",D4:BI4))

As before it's an array formula.