Looking for the smallest number that isn't less than zero
 

Hello all! I have a formual set up to return the smallest number in a
column...
=SMALL($E$4:$E$104,1)
However, I need to to return the smallest number greater than zero. We have
a contest going on and based on sales vs. the employee guess we may have some
negative numbers come up so I don't want them to show in my top 5... Can you
help?

Looking for the smallest number that isn't less than zero
 

=MIN(IF($E$4:$E$1040,$E$4:$E$104))

ctrl shift enter (not just enter)


"CharlieS" wrote:

Hello all! I have a formual set up to return the smallest number in a
column...
=SMALL($E$4:$E$104,1)
However, I need to to return the smallest number greater than zero. We have
a contest going on and based on sales vs. the employee guess we may have some
negative numbers come up so I don't want them to show in my top 5... Can you
help?

Looking for the smallest number that isn't less than zero
 

Thanks! Why does ctrl shift enter work, but enter does not?

Also I need to find the smallest 5 from the list, but if I copy this formula
to the next row down it gives me the same number. When I use SMALL I can
have it return 1, 2, 3, 4, and 5 in different rows... So I need it to return
the smallest 5 while ignoring anything less than zero...

"Teethless mama" wrote:

=MIN(IF($E$4:$E$1040,$E$4:$E$104))

ctrl shift enter (not just enter)


"CharlieS" wrote:

Hello all! I have a formual set up to return the smallest number in a
column...
=SMALL($E$4:$E$104,1)
However, I need to to return the smallest number greater than zero. We have
a contest going on and based on sales vs. the employee guess we may have some
negative numbers come up so I don't want them to show in my top 5... Can you
help?

Looking for the smallest number that isn't less than zero
 

Try this:

=SMALL(IF(E$4:E$1040,E$4:E$104),ROW())

This is an array formula, so you must use CTRL-SHIFT-ENTER instead of
just ENTER once you have typed it in or subsequently edit it.

I have assumed that you enter this on row 1, but if it is on row 4 (for
example) make the end of the formula ... ROW()-3).

This can then be copied down into 4 cells below to give you the 5
smallest values greater than 0 in the range E4 to E104.

Hope this helps.

Pete

CharlieS wrote:
Thanks! Why does ctrl shift enter work, but enter does not?

Also I need to find the smallest 5 from the list, but if I copy this formula
to the next row down it gives me the same number. When I use SMALL I can
have it return 1, 2, 3, 4, and 5 in different rows... So I need it to return
the smallest 5 while ignoring anything less than zero...

"Teethless mama" wrote:

=MIN(IF($E$4:$E$1040,$E$4:$E$104))

ctrl shift enter (not just enter)


"CharlieS" wrote:

Hello all! I have a formual set up to return the smallest number in a
column...
=SMALL($E$4:$E$104,1)
However, I need to to return the smallest number greater than zero. We have
a contest going on and based on sales vs. the employee guess we may have some
negative numbers come up so I don't want them to show in my top 5... Can you
help?