MIN function

[Guest]

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!

 

[Harlan Grove]

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:A5>0,A1:A5))

and the simplest nonarray approach is

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

 

[Guest]

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

 

[Guest]

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

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

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

XL2002, WinXP

 

[Guest]

That's awesome...thx guys

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

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

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

XL2002, WinXP