Results 1 to 4 of 4
  1. #1
    Star Lounger
    Join Date
    Feb 2001
    Posts
    60
    Thanks
    0
    Thanked 0 Times in 0 Posts

    First 2 negative numbers (97 SR1)

    Hello,

    How do I print out the X and Y values (from data of X and Y range) of the first and second negative numbers from a sorted sequence?

    Thanks, Hanan.

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: First 2 negative numbers (97 SR1)

    In C26 enter
    <pre>=INDEX($A$2:$A$22,MATCH(C27,$B$2:$B$22,0))</pre>


    copy this to C30

    In C27 enter the ARRAY (confirm with ctrl-shift-enter):
    <pre>=LARGE(IF($B$2:$B$22<0,$B$2:$B$22),1)</pre>


    In C31 enter the ARRAY (confirm with ctrl-shift-enter):
    <pre>=LARGE(IF($B$2:$B$22<0,$B$2:$B$22),2)</pre>


    Steve

  3. #3
    Star Lounger
    Join Date
    Feb 2001
    Posts
    60
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: First 2 negative numbers (97 SR1)

    Thanks Steve for your quick reply.

    Another question that I have in this regard. In the Y range I have some numbers that show up as #VALUE!. In this case those equations fail. Is there a work-around equation? If not, what is the best way to hide these particular cells?

    Thanks, Hanan.

  4. #4
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: First 2 negative numbers (97 SR1)

    The workbook you uploaded contained only numbers in the "Y range", no formula. It is a little tough to answer your question without seeing the formula that is giving the #VALUE error. If you just want to hide the error, then you can do something like this. It the formula is =A1-B1, then you could change it to:

    <pre>=IF(ISERROR(A1-B1),"",A1-B1)
    </pre>

    Legare Coleman

Posting Permissions

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