+ Reply to Thread
Results 1 to 9 of 9

Need a formula that adds the previous row if >1 to another cell

  1. #1
    Registered User
    Join Date
    03-05-2015
    Location
    New Jersey
    MS-Off Ver
    2007
    Posts
    9

    Need a formula that adds the previous row if >1 to another cell

    Hi ,

    I'm working on a worksheet that compares early social security payments and full social security payments to find the breakeven point with running totals and dates.

    Here's where I'm stuck...

    If a value of "1" is in the same row but in column J - then I place the value of ($D$2) in that cell (Column E). (That part works) However, once that row in column E, contains the value of ($D$2), (which I'm finding by using an arbitrary ">100" in my formula) -- , then I need to add that value plus ($D$2) again to each and every consecutive row.

    (Each row is a monthly payment that is to be summed down the column) Note: there will be only a single "1" in the entire column J, but at different rows each time.

    I tried a couple of things and below is what I tried last.


    =IF(AND(J8=1,$D$2,IF(INDIRECT("E"&ROW()-1)>100,(INDIRECT("E"&ROW()-1,+$D$2,"")))))

    Thanks - Any assistance would be appreciated

    thebrin

  2. #2
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365 v 2403
    Posts
    13,396

    Re: Need a formula that adds the previous row if >1 to another cell

    This is difficult for me to visualize and interpret.

    See if uploading an Excel workbook helps. (... not pics or screenshots ... saves having to retype data.)

    Be sure it is of the same structure with only relevant data.
    Show a BEFORE section (what you start with) and an AFTER section hand typed if necessary.
    Provide a clear explanation of the logic behind the AFTER section.

    If you aren't familiar with how to upload a file:

    To attach a file to your post,
    • be sure to desensitize the data
    • click “Go Advanced” (next to Post Quick Reply – bottom right),
    • scroll down until you see “Manage Attachments”, click that,
    • click “Browse”.
    • select your file(s)
    • click “Upload”
    • click “Close window”
    • click “Submit Reply”

    The file name will appear at the bottom of your reply.
    Dave

  3. #3
    Registered User
    Join Date
    03-05-2015
    Location
    New Jersey
    MS-Off Ver
    2007
    Posts
    9

    Re: Need a formula that adds the previous row if >1 to another cell

    Hi - Sorry, I know this was confusing. I attached a example of what I was talking about. Hope it clears this up.


    I tried several things and the last attempt is below...

    =IF(ISBLANK(J9),"BLANK",IF(J9=1,$D$2,IF(INDIRECT("E"&ROW()-1)>1,(INDIRECT("E"&ROW()-1,+$D$2)))))
    Attached Files Attached Files

  4. #4
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    27,999

    Re: Need a formula that adds the previous row if >1 to another cell

    In F3

    =IF($J3=1,$D$2,IF(F2>0,$D$2*(COUNTIF($F$2:F2,"<>0")),0))

    copy down

  5. #5
    Forum Contributor
    Join Date
    12-13-2013
    Location
    Calgary,AB,Canada
    MS-Off Ver
    2016,O365
    Posts
    236

    Re: Need a formula that adds the previous row if >1 to another cell

    Or
    f3=$d$2*count($j$2:j3)+max($f$2:f2)

  6. #6
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365 v 2403
    Posts
    13,396

    Re: Need a formula that adds the previous row if >1 to another cell

    Another way.

    =IF(ISNUMBER(LOOKUP(E1+306,J$3:J3)),D$2+F2,0)

  7. #7
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    27,999

    Re: Need a formula that adds the previous row if >1 to another cell

    ...or..

    =IF($J3,$D$2,IF(F2,$D$2+F2,0))

  8. #8
    Registered User
    Join Date
    03-05-2015
    Location
    New Jersey
    MS-Off Ver
    2007
    Posts
    9

    [SOLVED] Need a formula that adds the previous row if >1 to another cell

    Thanks Everyone . Works great

  9. #9
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365 v 2403
    Posts
    13,396

    Re: Need a formula that adds the previous row if >1 to another cell

    You're welcome. Thanks for the feedback.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Macro that adds 1 to previous day but stops when following day isnt the same
    By Faintkitara in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 05-19-2015, 01:14 PM
  2. Replies: 2
    Last Post: 07-02-2013, 07:03 AM
  3. Help creating a macro that adds the previous day's data into a monthly column.
    By MonaHussain in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 06-28-2012, 08:44 AM
  4. Replies: 1
    Last Post: 04-30-2012, 06:02 AM
  5. Replies: 0
    Last Post: 12-22-2011, 03:42 PM
  6. Cell adds previous values automatically
    By nih in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 08-20-2009, 10:38 AM
  7. [SOLVED] entering a # each row adds 1 to the previous cell. Turn off?
    By dbog in forum Excel General
    Replies: 1
    Last Post: 11-20-2005, 02:45 PM

Tags for this Thread

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1