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:A200.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" wrote:

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

still will return 0.00 as the lowest number

"Mike H" wrote:

Hi,

Try this ARRAY formula

=MIN(IF(A1:A200.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" wrote:

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

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

msao wrote:
still will return 0.00 as the lowest number

"Mike H" wrote:

Hi,

Try this ARRAY formula

=MIN(IF(A1:A200.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" wrote:

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

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

On Tue, 17 Nov 2009 12:51:01 -0800, msao
wrote:

still will return 0.00 as the lowest number

"Mike H" wrote:

Hi,

Try this ARRAY formula

=MIN(IF(A1:A200.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" wrote:

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

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

On Nov 17, 8:51*pm, msao wrote:
still will return 0.00 as the lowest number



"Mike H" wrote:
Hi,

Try this ARRAY formula

=MIN(IF(A1:A200.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" wrote:

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- Hide quoted text -

- Show quoted text -

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

msao wrote:
still will return 0.00 as the lowest number

"Mike H" wrote:
Try this ARRAY formula

=MIN(IF(A1:A200.01,A1:A20))
....
"msao" wrote:

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'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(A 1: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))