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?

=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?

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?

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?