Find Smallest X Value For A Group Ignoring Zeros

**splendidus** · 06-11-2012, 12:24 PM

I have a trivial, hopefully, issue: I need to find several smallest data points for a certain group of values. I got an array formula, but I'm having a bit of a hard time making it ignore zero values completely. Here's the formula:

Please Login or Register  to view this content.

It's giving me zero values if there are any in the data, and I'd like to start with the smallest above-zero values.

Any ideas, guys/gals? Thanks so much in advance!

Sample file is attached as well.

**oeldere** · 06-11-2012, 12:50 PM

This one will do that for you.

I try to translate the formula:

=Small($B$2:$B$14;countif($B$2:$B$14;0%)+Row()-3)

**Paul** · 06-11-2012, 12:50 PM

Hi Splendidus, try adjusting your array formula to:

=SMALL(IF(($A$2:$A$14=$A$14)*($B$2:$B$14>0),$B$2:$B$14),1)

Note: You'll get a #NUM error if there is no 1st, 2nd or 3rd smallest greater than 0. You can use conditional formatting or wrap this array in an IFERROR function to hide those, of course.