+ Reply to Thread
Results 1 to 6 of 6

Need help with finding first negative value in a list of numbers

  1. 04-30-2014, 07:44 PM #1
    Good 4 Me
    Good 4 Me is offline
    Registered User
    Join Date
    12-10-2013
    Location
    ID
    MS-Off Ver
    Excel 2007
    Posts
    12

    Need help with finding first negative value in a list of numbers

    Hello...

    In column B, I have an unsorted list of numbers, both positive and negative. Column A is an index - 1,2,3, ... etc.

    What I need is a formula that will return the cell value (from column A) corresponding to the first negative number in column B.

    For example, suppose column A contains 1, 2, 3, 4, 5, 6. And suppose column B contains 12, 7, 9, -2, -16, and 34.


    I need the formula to return 4 (since the fourth number, which is -2, is the first negative in the list in B).

    Can anyone help?

    Thanks!
    Reply With QuoteRegister To Reply
  2. 04-30-2014, 07:50 PM #2
    Tony Valko
    Tony Valko is offline
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Need help with finding first negative value in a list of numbers

    Try this array formula**:

    =INDEX(A2:A7,MATCH(TRUE,B2:B7<0,0))

    If the number sequence in column A always starts at 1 and increments by 1 then you can use this shorter array formula**:

    =MATCH(TRUE,B2:B7<0,0)

    ** array formulas need to be entered using the key
    combination of CTRL,SHIFT,ENTER (not just ENTER).
    Hold down both the CTRL key and the SHIFT key
    then hit ENTER.
    Biff
    Microsoft MVP Excel
    Keep It Simple Stupid

    Let's Go Pens. We Want The Cup.
    Reply With QuoteRegister To Reply
  3. 04-30-2014, 07:51 PM #3
    martindwilson
    martindwilson is offline
    Forum Expert martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: Need help with finding first negative value in a list of numbers

    =MATCH(TRUE,INDEX(B1:B20<0,0),0) would give 4
    but if you wanted the 4th thing in column a
    =INDEX(A1:A20,MATCH(TRUE,INDEX(B1:B20<0,0),0))
    "Unless otherwise stated all my comments are directed at OP"

    Mojito connoisseur and now happily retired
    where does code go ?
    look here
    how to insert code

    how to enter array formula
    why use -- in sumproduct
    recommended reading
    wiki Mojito

    how to say no convincingly

    most important thing you need
    Martin Wilson: SPV
    and RSMBC
    Reply With QuoteRegister To Reply
  4. 04-30-2014, 08:00 PM #4
    Pete_UK
    Pete_UK is offline
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    25,412

    Re: Need help with finding first negative value in a list of numbers

    Here's another array* formula:

    =MIN(IF(B1:B6<0,ROW(B1:B6)))

    *Commit using Ctrl-Shift-Enter, instead of the usual <Enter>.

    Hope this helps.

    Pete
    Reply With QuoteRegister To Reply
  5. 04-30-2014, 08:09 PM #5
    Good 4 Me
    Good 4 Me is offline
    Registered User
    Join Date
    12-10-2013
    Location
    ID
    MS-Off Ver
    Excel 2007
    Posts
    12

    Re: Need help with finding first negative value in a list of numbers

    Thanks guys! The formula

    =INDEX(A2:A2001,MATCH(TRUE,B2:B2001<0,0))

    is exactly what I need!
    Last edited by Good 4 Me; 04-30-2014 at 08:19 PM.
    Reply With QuoteRegister To Reply
  6. 04-30-2014, 08:31 PM #6
    Tony Valko
    Tony Valko is offline
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Need help with finding first negative value in a list of numbers

    You're welcome. We appreciate the feedback!
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Associating a list with positve and negative numbers formula
    By damianberry in forum Excel General
    Replies: 5
    Last Post: 11-16-2012, 08:20 AM
  2. Finding numbers left in list
    By khank in forum Excel General
    Replies: 2
    Last Post: 03-13-2011, 01:28 PM
  3. Finding a predetermined Sum from a List of Numbers
    By zshuford in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 01-16-2007, 12:34 PM
  4. finding out pairs of positive & negative numbers!
    By via135 in forum Excel General
    Replies: 11
    Last Post: 12-18-2005, 02:27 AM
  5. how to change a list of possitive numbers to negative
    By michelle in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 11-16-2005, 04:30 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1