find the smallest number greater than Zero

I have 7 columns of numbers
I need to find the smallest number that is greater than Zero

the following formula is the best I can come up with,

IF((SMALL(R6:X6,1))>0,(SMALL(R6:X6,1)),IF((SMALL(R6:X6,2))>0,(SMALL(R6:X6,2)),IF((SMALL(R6:X6,3))>0,(SMALL(R6:X6,3)),IF((SMALL(R6:X6,4))>0,(SMALL(R6:X6,4)),IF((SMALL(R6:X6,5))>0,(SMALL(R6:X6,5)),IF((SMALL(R6:X6,6))>0,(SMALL(R6:X6,6)),(SMALL(R6:X6,7))))))))

problem is if I add or delete columns I have to change the formula.
Is there a better way?

Thanks

Originally Posted by carstowal

I have 7 columns of numbers
I need to find the smallest number that is greater than Zero

the following formula is the best I can come up with,

IF((SMALL(R6:X6,1))>0,(SMALL(R6:X6,1)),IF((SMALL(R6:X6,2))>0,(SMALL(R6:X6,2)),IF((SMALL(R6:X6,3))>0,(SMALL(R6:X6,3)),IF((SMALL(R6:X6,4))>0,(SMALL(R6:X6,4)),IF((SMALL(R6:X6,5))>0,(SMALL(R6:X6,5)),IF((SMALL(R6:X6,6))>0,(SMALL(R6:X6,6)),(SMALL(R6:X6,7))))))))

problem is if I add or delete columns I have to change the formula.
Is there a better way?

Thanks

Try one of these two (both are array formulas):

=SMALL(IF(R6:X6<=0,"",R6:X6),1)
=MIN(IF(R6:X6<=0,"",R6:X6))

Whichever you choose has to be entered with CTRL+SHIFT+ENTER.

HTH,
Scott

thanks, both work but can you explain why
I've never used "" and don't know what it refers to

Originally Posted by carstowal

thanks, both work but can you explain why
I've never used "" and don't know what it refers to

Actually, where I put "", you can put anything as long as it isn't a number. "" just refers to an empty string, which isn't a number. The reason we need to have this is because MIN() and SMALL() only work on numbers -- so if for each negative or 0 value we substitute a string, these two functions will ignore them and only look at the values greater than zero. You can see this if you try to use MIN or SMALL on a list that combines both numbers and words. The words will be ignored.

As to array formulas, you would be better to look around the net to find more information, as I can use them and know what they do, but probably cannot explain it concisely. In order to understand the rest of how this formula actually works, you'll need to understand how array formula work.

Scott