MIN function
 

I am having trouble finding a way to create a formula that will give me the
MIN of a range that is not zero.

IE - in the given range..my MIN function should give me 2 not 0.
A
1 525
2 1000
3 0
4 2
5 25


Thanks!

MIN function
 

Daniel Q. wrote...
I am having trouble finding a way to create a formula that will give me the
MIN of a range that is not zero.

IE - in the given range..my MIN function should give me 2 not 0.
A
1 525
2 1000
3 0
4 2
5 25

Presumably you want the smallest positive value. If so, there are many
ways to do it. The basic array formula approach is

=MIN(IF(A1:A50,A1:A5))

and the simplest nonarray approach is

=SMALL(A1:A5,COUNTIF(A1:A5,"<=0")+1)

MIN function
 

Try this ARRAY FORMULA*:

=MIN(IF(A1:A5<0,A1:A5))

Note: For array formulas, hold down [Ctrl] and [Shift] when you press
[Enter], instead of just pressing [Enter].

Is that something you can work with?
***********
Regards,
Ron

XL2002, WinXP


"Daniel Q." wrote:

I am having trouble finding a way to create a formula that will give me the
MIN of a range that is not zero.

IE - in the given range..my MIN function should give me 2 not 0.
A
1 525
2 1000
3 0
4 2
5 25


Thanks!

MIN function
 

=SMALL(A1:A5,COUNTIF(A1:A5,"<=0")+1)

Nice, Harlan....I'd not seen that approach before.

***********
Regards,
Ron

XL2002, WinXP


"Harlan Grove" wrote:

Daniel Q. wrote...
I am having trouble finding a way to create a formula that will give me the
MIN of a range that is not zero.

IE - in the given range..my MIN function should give me 2 not 0.
A
1 525
2 1000
3 0
4 2
5 25

Presumably you want the smallest positive value. If so, there are many
ways to do it. The basic array formula approach is

=MIN(IF(A1:A50,A1:A5))

and the simplest nonarray approach is

=SMALL(A1:A5,COUNTIF(A1:A5,"<=0")+1)

MIN function
 

That's awesome...thx guys

"Ron Coderre" wrote:

=SMALL(A1:A5,COUNTIF(A1:A5,"<=0")+1)

Nice, Harlan....I'd not seen that approach before.

***********
Regards,
Ron

XL2002, WinXP


"Harlan Grove" wrote:

Daniel Q. wrote...
I am having trouble finding a way to create a formula that will give me the
MIN of a range that is not zero.

IE - in the given range..my MIN function should give me 2 not 0.
A
1 525
2 1000
3 0
4 2
5 25

Presumably you want the smallest positive value. If so, there are many
ways to do it. The basic array formula approach is

=MIN(IF(A1:A50,A1:A5))

and the simplest nonarray approach is

=SMALL(A1:A5,COUNTIF(A1:A5,"<=0")+1)