MIN function

  • Thread starter Thread starter Guest
  • Start date Start date
G

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!
 
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
Click to expand...

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)
 
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
 
RE: =SMALL(A1:A5,COUNTIF(A1:A5,"<=0")+1)

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

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

XL2002, WinXP
 
That's awesome...thx guys

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

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

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

XL2002, WinXP
Click to expand...
 
Back
Top