Greater Than

  • Thread starter Thread starter msao
  • Start date Start date
M

msao

Need to take a colum and find the smallest number in it but it must be
greater than 0.01 any help would be great
 
Hi,

Try this ARRAY formula

=MIN(IF(A1:A20>0.01,A1:A20))

This is an array formula which must be entered by pressing CTRL+Shift+Enter
'and not just Enter. If you do it correctly then Excel will put curly brackets
'around the formula {}. You can't type these yourself. If you edit the formula
'you must enter it again with CTRL+Shift+Enter.

Mike
 
still will return 0.00 as the lowest number

Mike H said:
Hi,

Try this ARRAY formula

=MIN(IF(A1:A20>0.01,A1:A20))

This is an array formula which must be entered by pressing CTRL+Shift+Enter
'and not just Enter. If you do it correctly then Excel will put curly brackets
'around the formula {}. You can't type these yourself. If you edit the formula
'you must enter it again with CTRL+Shift+Enter.

Mike
Click to expand...
 
Not in my testing.

Returns 0.010000001 as minimum but not 0.01 or less.

How are your cells formatted?


Gord Dibben MS Excel MVP
 
Did you enter Mike's formula using CSE as advised?

Here's another take on what you might want:

=MAX(MIN(A:A),0.1)

Hope this helps.

Pete
 
Glenn said:
It returned 0.00, or you think it will so you didn't try it?
Click to expand...

Mike's array formula would return 0 if there were no values in A1:A20
0.01.
Click to expand...

If there are no numbers in A1:A20 greater than or equal to 0.01, what
should the formula return? If blank,

=IF(COUNTIF(A1:A20,">0.01"),LARGE(A1:A20,COUNTIF(A1:A20,">0.01")),"")

or for Excel 2007 & later

=IFERROR(LARGE(A1:A20,COUNTIF(A1:A20,">0.01")),"")

OTOH, if it should return 0.01,

=MAX(0.01,MIN(A1:A20))
 
Back
Top