Greater Than

[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

 

[Mike H]

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

 

[msao]

still will return 0.00 as the lowest number

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

 

[Glenn]

It returned 0.00, or you think it will so you didn't try it?

 

[Gord Dibben]

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

 

[Pete_UK]

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

 

[Harlan Grove]

It returned 0.00, or you think it will so you didn't try it?

Mike's array formula would return 0 if there were no values in A1:A20

0.01.

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))