Min (ignore values less than zero) 1

[Montano]

In a range of numbers, I want to get the mininum value but ignore any values less than zero. How do I write this formula?

 

[Zathras]

You can use an array formula like this. First create a range with the name "MyNumbers" for the range of numbers you want the minimum:
[blue]

    =MIN(IF(MyNumbers>0,MyNumbers,99999))

[/color]
Do not simply enter this formula, instead you must enter it as an "array formula" by the combination keystrokes

{Ctrl}+{Shift}+{Enter}

When done correctly, you will see the formula inclosed in curly braces in the formula edit window.

Adjust the constant if all of your numbers are larger than 99,999.

 

[xlbo]

Sorry for butting in Z - just wanted to produce a non array formula to do the same thing....and this does it:
=SMALL(myNumbers,COUNTIF(myNumbers,0)+1)
using your range name ;-)

Rgds, Geoff
Quantum materiae materietur marmota monax si marmota monax materiam possit materiari?
Want the best answers to your questions ? faq222-2244

 

[Bowers74]

I tried it another way:

=SMALL(A:A;COUNTIF(A:A;">0&quot-COUNTIF(A:A;0))

This worked well enough.

I tried to write it this way but for some reason, the COUNTIF() function in Excel doesn't count cells that are less than 0.

THIS DOESN'T WORK!!!!!

=SMALL(A1:A26;COUNTIF(A:A;&quot;<=0&quot)

I'm using Excel XP, maybe it still works right in 2000.

Enjoy & Good Luck!!!

If you can't be &quot;The Best&quot;, be the best at what you can!!!

Never say Never!!!
Nothing is impossible!!!

 

[Zathras]

No need to apologize, Geoff. Jump anytime whenever you have an improvement. (Goodness knows, I do it to you enough.)

However....

Your formula doesn't appears not to ignore negative numbers (at least in Excel 97) with this test data:

A1: 5
A2: 6
A3: 7
A4: -4
B1: -3
B2: 10
B3: 7
B4: 8

Range &quot;MyNumbers&quot; = Range(&quot;A1:B4&quot

On my computer, your formula evaluates to -4.

 

[xlbo]

D'oh D'oh and again D'oh
maybe if I just read the question eh ??? didn't read the <0 bit - this works in XP:
=SMALL(mynumbers,COUNTIF(mynumbers,&quot;<=0&quot+1)

Rgds, Geoff
Quantum materiae materietur marmota monax si marmota monax materiam possit materiari?
Want the best answers to your questions ? faq222-2244

 

[Bowers74]

My mistake . . . .

Excel isn't the one with the problem, I am.

I didn't have any cells that were less than 0 in my range.

A quick change to Geoff's formula will make it work the way Montano wants:

=SMALL(myNumbers,COUNTIF(myNumbers,&quot;<=0&quot;)+1)

Sorry guys!

If you can't be &quot;The Best&quot;, be the best at what you can!!!

Never say Never!!!
Nothing is impossible!!!

 

[Zathras]

Ok, now I get it. (I should have looked up SMALL.)

Very neat! And definitely better. Much faster, too!