Results 1 to 5 of 5
  1. #1
    3 Star Lounger
    Join Date
    Mar 2004
    Location
    North Carolina, USA
    Posts
    268
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Formula to tell me when Row of Numbers Turn Pos (2002)

    I have a financial calculation that adds annual totals together. For example, in row 62 I would have the following:
    2005 2006 2007 2008 2009
    a -500, b -900, c +200, d +1000, e +900.

    Row 63 would have cumulative totals:

    2005 2006 2007 2008 2009
    -500, -1400, -1200, -200, +700


    Each one of these cells represents a year. Consequently, in a 65 I would like to have a formula that showed me this the cumulative total turned positive after 4 years. Is this possible?

  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: Formula to tell me when Row of Numbers Turn Pos (2002)

    Not sure exactly what you are after but how about in A65:

    =e64>=0

    it will give a TRUE (truned possitive) or a FALSE (is negative)

    If you are looking for something different could you elaborate?
    Steve

  3. #3
    3 Star Lounger
    Join Date
    Mar 2004
    Location
    North Carolina, USA
    Posts
    268
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Formula to tell me when Row of Numbers Turn Pos (2002)

    Yr 1 Yr 2 Yr 3 Yr 4
    (1,230) (2,480) (3,730) 500
    a 62 b62 c62 d62
    I basically like to look at row 62 and tell me what year this thing turns positive (beginning in row a). So if it turns positive in year 4 (row D) then my formula in a65 would tell me 4. Or if it turned positive in year 5 (row E), it would tell me 5...

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Formula to tell me when Row of Numbers Turn Pos (2002)

    Your cells seem to shift around slightly - in the first post the cumulative totals were in row 63, now in row 62. Assuming they are in row 62, the following array formula (confirm with Ctrl+Shift+Enter) will return the number of the first column with a positive total:
    <code>
    =MATCH(TRUE,A62:E62>0,0)
    </code>
    It'll return 4 if the first positive total is in column D (the 4th column), or #N/A if there is no positive total. If there are no other values in row 62, you can also use
    <code>
    =MATCH(TRUE,62:62>0,0)</code>

  5. #5
    3 Star Lounger
    Join Date
    Mar 2004
    Location
    North Carolina, USA
    Posts
    268
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Formula to tell me when Row of Numbers Turn Pos (2002)

    Thanks - PERFECT.

Posting Permissions

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