Archive of Mr Excel Message Board

Back to Forms in Excel VBA archive index
Back to archive home

(Advanced Help) Finding first Non-Negative Number!!!

Posted by deadlee on February 04, 2002 7:53 AM

Hey guys,

I gotta small problem here. i'm stuck!!!
I have a row of calculated fields, and I want to use the first Non-Negative Cell in another calculation.

How do i do that? cannot figure out the logic.

eg: 0.00 0.00 0.00 3.16 3.17

I want the value 3.16 in another field. How do i obtain that value using logic only?

Would appreciate any help guys

Check out our Excel Resources

Re: (Advanced Help) Finding first Non-Negative Number!!!

Posted by Brian on February 04, 2002 8:32 AM

The DMIN function provides a nice way of doing this.
Lets say you have five numbers in cells A2:A6. Put a column heading above this (e.g. “Values”).
Now outside of your database put a criteria database with the same heading and the entry >0 (e.g. in cells d1:d2).
Now wherever you want to use the highest non-zero entry, enter this function:
=DMIN(A1:6,1,d1:d2)

Re: (Advanced Help) Finding first Non-Negative Number!!!

Posted by Aladin Akyurek on February 04, 2002 8:39 AM

Array-enter:

=INDEX(A:A,MIN(IF(A2:A6>0,(A2:A6>0)*ROW(A2:A6))))

where A2:A6 houses the numbers of interest.

Note. I took "the first Non-Negative Cell" quite literally, anyway as something different than MIN of all positive values. In order to array-enter a formula, you need to hit control+shift+enter instead of just enter.

========

Re: (Advanced Help) Finding first Non-Negative Number!!!

Posted by Brian on February 04, 2002 8:52 AM

On further consideration, formula arrays does this even nicer.
Again, assuming the numbers are in cells a1:a6

=MIN(IF(A1:A6>0,A2:A11)

(Dont forget, do not press enter after entering this. Press CTRL+SHIFT+ENTER

Re: (Advanced Help) Finding first Non-Negative Number!!!

Posted by Yogi Anand on February 04, 2002 5:06 PM

Hi Brian:
You did mean
=MIN(IF(A1:A6>0,A1:A6))
for your array formula ... Didn't You!

Re: (Advanced Help) Finding first Non-Negative Number!!!

Posted by Brian on February 05, 2002 1:19 AM

Doh! Yup, apologies and good spot. Shows someone is watching.

This archive is from the original message board at www.MrExcel.com.
All contents © 1998-2004 MrExcel.com.
Visit our online store to buy searchable CD's with thousands of VBA and Excel answers.
Microsoft Excel is a registered trademark of the Microsoft Corporation.
MrExcel is a registered trademark of Tickling Keys, Inc.