+ Reply to Thread
Results 1 to 10 of 10

Subtract values from non blank cells in one row from non blank cells in the row below

  1. #1
    Registered User
    Join Date
    12-17-2014
    Location
    Thailand
    MS-Off Ver
    07
    Posts
    5

    Subtract values from non blank cells in one row from non blank cells in the row below

    Hi guys,


    I have attached the example. The thing is I need to find a function which could be used to subtract values from one row (any of the cells in the selected range) from the row below (any of the cells).
    E.g. A1 (1) B1(BLANK) C1(BLANK)

    A2(BLANK) B2 (3) C2(BLANK)

    in this case I would want to subtract B2 from A1

    but combinations vary from row to row (A1-A2;B1-A2;C1-A2...A1-B2;B1-B2;C1-B2...C1-C2;B1-C2; ) the only thing that is consistent is that it's always only one non blank cell in the row

    I have tried using functions for one cell and just applying it to the range that I have used and it doesn't work

    Please help

  2. #2
    Forum Guru Kaper's Avatar
    Join Date
    12-14-2013
    Location
    Warsaw, Poland
    MS-Off Ver
    most often: Office 365 in Windows environment
    Posts
    8,606

    Re: Subtract values from non blank cells in one row from non blank cells in the row below

    if there us "always only one non blank cell in the row" use:
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Best Regards,

    Kaper

  3. #3
    Forum Guru samba_ravi's Avatar
    Join Date
    07-26-2011
    Location
    Hyderabad, India
    MS-Off Ver
    Excel 2021
    Posts
    8,904

    Re: Subtract values from non blank cells in one row from non blank cells in the row below

    Attach sample excel file with some examples
    Samba

    Say thanks to those who have helped you by clicking Add Reputation star.

  4. #4
    Registered User
    Join Date
    12-17-2014
    Location
    Thailand
    MS-Off Ver
    07
    Posts
    5

    Re: Subtract values from non blank cells in one row from non blank cells in the row below

    Life savior!

    Is there a way the same formula could be applied to more than 200 files at once. My outputs are all the same, so the results would always pop up in the same columns?
    Also is there a way to edit files, like removing redundant columns, and adding couple of more functions and once again apply it to multiple files?

    Thanks so much once again!

  5. #5
    Forum Guru samba_ravi's Avatar
    Join Date
    07-26-2011
    Location
    Hyderabad, India
    MS-Off Ver
    Excel 2021
    Posts
    8,904

    Re: Subtract values from non blank cells in one row from non blank cells in the row below

    attach sample excel file

  6. #6
    Registered User
    Join Date
    12-17-2014
    Location
    Thailand
    MS-Off Ver
    07
    Posts
    5

    Re: Subtract values from non blank cells in one row from non blank cells in the row below

    Hi guys,

    So here are the files, am trying to figure out using one column as a reference point for operations in the other columns, tried to explain it in more detail in the file itself.

    Thanks!
    Attached Files Attached Files

  7. #7
    Forum Guru samba_ravi's Avatar
    Join Date
    07-26-2011
    Location
    Hyderabad, India
    MS-Off Ver
    Excel 2021
    Posts
    8,904

    Re: Subtract values from non blank cells in one row from non blank cells in the row below

    Why I4=F4-D3
    In your Question No 1 Sheet

  8. #8
    Registered User
    Join Date
    12-17-2014
    Location
    Thailand
    MS-Off Ver
    07
    Posts
    5

    Re: Subtract values from non blank cells in one row from non blank cells in the row below

    Because columns d-f indicate onsets and columns g-I indicate durations, so I need to subtract non blank values from the row below of the non blank values from the row above.

    Thanks!

  9. #9
    Forum Guru samba_ravi's Avatar
    Join Date
    07-26-2011
    Location
    Hyderabad, India
    MS-Off Ver
    Excel 2021
    Posts
    8,904

    Re: Subtract values from non blank cells in one row from non blank cells in the row below

    I think I4=D5-F4
    Because
    G3=F4-D3
    G5=E7-D5
    H7=D8-E7
    Hence I4=D5-F4

  10. #10
    Registered User
    Join Date
    12-17-2014
    Location
    Thailand
    MS-Off Ver
    07
    Posts
    5

    Re: Subtract values from non blank cells in one row from non blank cells in the row below

    Yep, I knew that bit, the thing is that I need a macro for excel or matlab code that would be applicable for huge files that would do these calculations automatically. Mind you there is no pattern in the onsets so basically the formula or code would need to check the previous row and make the subtraction from non blank which is placed above.

    Thanks in advance!

+ 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] If formula, if two cells are blank, return blank, if one has a date, subtract todays date
    By amthyst826 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 03-06-2014, 12:47 PM
  2. [SOLVED] Macro to fill blank cells in column A based on non-blank cells
    By ktalamantez in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 02-28-2014, 02:47 PM
  3. Replies: 5
    Last Post: 08-26-2013, 06:26 PM
  4. How can I make a macro ignore blank cells? - Blank Cells have formulas
    By mz1161 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 04-30-2013, 08:54 AM
  5. Advanced filter with blank cells / Dynamic named range with blank cells
    By Jason_2112 in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 11-15-2010, 12:06 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