min excluding zero values?

[lukman4068]

is there any such MIN formula in excel 2003 which will return the smallest value of an array excluding zero values? I could set up a bunch of if formulas to return blanks instead of zero values however with the dataset as large as it is, it isn't really an option.

I'm thinking the formula would be something like =if(Array"<>"0,Min(Array)) and CTRL Shift Enter

but this doesn't work - it still returns zero values so I don't quite know how get around that. any ideas would be appreciated! thanks!

 

[barry houdini]

The syntax is like this

=MIN(IF(Range<>0,Range))

 

[lukman4068]

ps example would be:

Dataset:

4.2
7.9
6.2
0.0
3.2
0.0
5.4

Formula should return 3.2 and NOT 0.0

 

[Special-K99]

<code> http://www.mrexcel.com/archive/Dates/11449.html </code>

may help

 

[barry houdini]

See my previous reply. Assuming you only have positive values and zero you could also use this non-array version

=SMALL(Range,COUNTIF(Range,0)+1)