MrExcel Publishing
Your One Stop for Excel Tips & Solutions

HELP! I need a way to find the answer - MIN or Max excluding zeros


Posted by Carole Cronan on February 02, 2001 2:07 PM

I'm working on a worksheet and I'm having a problem.
My boss wants to know the most and the least in sales for a given period. We do not want lowest or second lowest to show for zero. I have tried nested loops with small and large and everything and I can't get the second lowest or lowest to show for anything but zero. (either it's zero or it doesnt show up at all)
So would one of you geniuses please save me. thanks for the help


Posted by cpod on February 02, 2001 2:28 PM

Try this:

{=MIN(IF(A1:A17=0,"",A1:A17))}

this is an array function and must be entered using Control+Shift+Enter