+ Reply to Thread
Results 1 to 24 of 24

Subtract last column from first column

  1. #1
    Registered User
    Join Date
    09-18-2017
    Location
    India
    MS-Off Ver
    2016
    Posts
    18

    Subtract last column from first column

    Forum Office software Excel
    Report Subtract last column from first column
    Ask a questionGaurav Kumar - Last answered on Sep 18, 2017 at 01:29 AM by Gauravkumar
    Hello,



    I am having a problem i have a excel sheet there are the data for various employees timing. first of all i want to search the last column and then i want to subtract that selected column from the column i have already mentioned. please help

    Ex: employee name punchin punch punch punch punch
    xyz 12.30 1.00 1.15 2.15
    abc 1.30 4.15 5.17 8.20 9.00

    first i want to search the last column in a row and then i want to subtract them from first punch this process will be in loop.

    Thanks in advance

  2. #2
    Forum Guru Kaper's Avatar
    Join Date
    12-14-2013
    Location
    Warsaw, Poland
    MS-Off Ver
    most often 365, but still time to time use also older versions
    Posts
    8,018

    Re: Subtract last column from first column

    Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

    Remember to desensitize the data.

    Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.
    Best Regards,

    Kaper

  3. #3
    Registered User
    Join Date
    09-18-2017
    Location
    India
    MS-Off Ver
    2016
    Posts
    18

    Re: Subtract last column from first column

    Your attachment link is not working
    Attached Files Attached Files

  4. #4
    Registered User
    Join Date
    09-18-2017
    Location
    India
    MS-Off Ver
    2016
    Posts
    18

    Re: Subtract last column from first column

    this is the attached file i need to subtract last column from first in a same row...and all rows have different last column...so i need to search the last column in each row and then subtract that last column from the column C in all rows by loop

  5. #5
    Forum Expert
    Join Date
    11-26-2013
    Location
    Colac, Victoria, Australia
    MS-Off Ver
    Excel 2016
    Posts
    1,309

    Re: Subtract last column from first column

    I have a couple of questions, because your post is unclear.

    Are these values actually times, or decimal values as displayed which have to be converted to times?
    Will these times always be in a 24 hour day, and always sequenced from earliest punch in to latest punchin?
    It could be as simple as =MAX(B1:F1)-B1

    I hope this helps, please let me know!

    Regards,

    David

    If this has been helpful
    - Please click on the *Add Reputation button at the bottom left.

    Please mark your thread as SOLVED:
    - Click Thread Tools above your first post, select "Mark your thread as Solved".


  6. #6
    Registered User
    Join Date
    09-18-2017
    Location
    India
    MS-Off Ver
    2016
    Posts
    18

    Re: Subtract last column from first column

    These are times value first i need to select the last value in a row of column L3 and subtract from E3 and store that value to C3 and this would run in a loop. like if 3rd row is completed than it goes to 4th row and search for the last cell which is N4 and subtract that from E4 and loop will continue.
    Last edited by Gauravkumar; 09-18-2017 at 03:09 AM.

  7. #7
    Forum Expert
    Join Date
    11-26-2013
    Location
    Colac, Victoria, Australia
    MS-Off Ver
    Excel 2016
    Posts
    1,309

    Re: Subtract last column from first column

    =MAX(E3:BB3)-E3 will work, provided all times are within one day.

    I hope this helps, please let me know!

    Regards,

    David

    If this has been helpful
    - Please click on the *Add Reputation button at the bottom left.

    Please mark your thread as SOLVED:
    - Click Thread Tools above your first post, select "Mark your thread as Solved".


  8. #8
    Registered User
    Join Date
    09-18-2017
    Location
    India
    MS-Off Ver
    2016
    Posts
    18

    Re: Subtract last column from first column

    yes it is working but i want it in macro code with loop

  9. #9
    Forum Guru Kaper's Avatar
    Join Date
    12-14-2013
    Location
    Warsaw, Poland
    MS-Off Ver
    most often 365, but still time to time use also older versions
    Posts
    8,018

    Re: Subtract last column from first column

    Why go for macro if standard formula does the job well?
    see attached file, where formula from David's post is applied for your data. Cell C3 and copy down.
    I added also formula for D3 (also copied down):
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Attached Files Attached Files

  10. #10
    Registered User
    Join Date
    09-18-2017
    Location
    India
    MS-Off Ver
    2016
    Posts
    18

    Re: Subtract last column from first column

    if i use this formula then i need to add this formula one by one on each row. but i need that i will execute a macro and it will calculate me whole the table on a single click

  11. #11
    Forum Guru Kaper's Avatar
    Join Date
    12-14-2013
    Location
    Warsaw, Poland
    MS-Off Ver
    most often 365, but still time to time use also older versions
    Posts
    8,018

    Re: Subtract last column from first column

    Why one by one?
    write it in first cell
    select this cell
    click and drag down the fill handle (small black square in bottom-right corner of the selected cell) to fill cells below with formula,
    or even better:
    double click on fill handle and excell will fill automatically all the cells below (as long ans next column is filled with data) for you.

  12. #12
    Registered User
    Join Date
    09-18-2017
    Location
    India
    MS-Off Ver
    2016
    Posts
    18

    Re: Subtract last column from first column

    I want this to be done by macro only because it is a requirement of my project

  13. #13
    Forum Guru Kaper's Avatar
    Join Date
    12-14-2013
    Location
    Warsaw, Poland
    MS-Off Ver
    most often 365, but still time to time use also older versions
    Posts
    8,018

    Re: Subtract last column from first column

    here is the code:
    Please Login or Register  to view this content.
    Attached Files Attached Files

  14. #14
    Registered User
    Join Date
    09-18-2017
    Location
    India
    MS-Off Ver
    2016
    Posts
    18

    Re: Subtract last column from first column

    Thanxx this code is working can you please explain this code

  15. #15
    Registered User
    Join Date
    09-18-2017
    Location
    India
    MS-Off Ver
    2016
    Posts
    18

    Re: Subtract last column from first column

    Hi Sir i need one more formula also which calculate punch in 3 subtract punch in 2 and punch in 5 subtract punch 4 punch 7 subtract punch 5 and add all this and subtract that number from the spent time and write it on effieient time like this example of row 4 =(C4-((G4-F4)+(I4-H4)+(K4-J4)+(M4-L4))) i want to calculate that time until the last number will come

  16. #16
    Forum Guru Kaper's Avatar
    Join Date
    12-14-2013
    Location
    Warsaw, Poland
    MS-Off Ver
    most often 365, but still time to time use also older versions
    Posts
    8,018

    Re: Subtract last column from first column

    Please analyze the formulas used in macro (they are the same as used in post #9) and adopt it to your needs.

  17. #17
    Registered User
    Join Date
    09-18-2017
    Location
    India
    MS-Off Ver
    2016
    Posts
    18

    Re: Subtract last column from first column

    i need that formula in excel not in macro

  18. #18
    Forum Guru Kaper's Avatar
    Join Date
    12-14-2013
    Location
    Warsaw, Poland
    MS-Off Ver
    most often 365, but still time to time use also older versions
    Posts
    8,018

    Re: Subtract last column from first column

    Then refer to post #9 - there are formulas

  19. #19
    Registered User
    Join Date
    09-18-2017
    Location
    India
    MS-Off Ver
    2016
    Posts
    18

    Re: Subtract last column from first column

    where can i see that post #9 please share me the link

  20. #20
    Forum Guru Kaper's Avatar
    Join Date
    12-14-2013
    Location
    Warsaw, Poland
    MS-Off Ver
    most often 365, but still time to time use also older versions
    Posts
    8,018

    Re: Subtract last column from first column

    It's post number nine in current thread.
    See at right side of blue bars above the posts (on left there is date/time on right post number).
    https://www.excelforum.com/excel-pro...ml#post4744611

  21. #21
    Registered User
    Join Date
    09-18-2017
    Location
    India
    MS-Off Ver
    2016
    Posts
    18

    Re: Subtract last column from first column

    Yes this is working can you please explain me this formula

  22. #22
    Registered User
    Join Date
    09-18-2017
    Location
    India
    MS-Off Ver
    2016
    Posts
    18

    Re: Subtract last column from first column

    Hi Kaper,

    i am using this formula and it is working fine but when i insert a blank column the sum of product would not be calculated its showing me ########## only. can you please help me.
    Attached Files Attached Files

  23. #23
    Forum Guru Kaper's Avatar
    Join Date
    12-14-2013
    Location
    Warsaw, Poland
    MS-Off Ver
    most often 365, but still time to time use also older versions
    Posts
    8,018

    Re: Subtract last column from first column

    You added one column, so now you do not want to focus on odd columns but on even. So the formula shall be:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

  24. #24
    Registered User
    Join Date
    09-18-2017
    Location
    India
    MS-Off Ver
    2016
    Posts
    18

    Re: Subtract last column from first column

    Thnxx kaper for your help

+ 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. Replies: 1
    Last Post: 12-28-2015, 08:43 AM
  2. Replies: 7
    Last Post: 11-13-2014, 04:46 PM
  3. Formula to subtract 7th column values from the column before the 1st (loop)
    By saya_26 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 02-22-2014, 09:13 AM
  4. VBA code to Subtract values in Column F with Column E if found non-blank
    By hydz1213 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 03-05-2013, 04:34 AM
  5. [SOLVED] Subtract value in a column when a certain range in crossed in another column
    By DexterG in forum Excel Formulas & Functions
    Replies: 13
    Last Post: 12-10-2012, 06:06 AM
  6. [SOLVED] Subtract from column based on value from another column
    By kevinali in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 08-30-2012, 08:54 PM
  7. [SOLVED] find max in column then subtract number til 0 from column
    By carden2 in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 06-20-2012, 10:58 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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1