+ Reply to Thread
Results 1 to 7 of 7

Difference between two columns

  1. #1
    Registered User
    Join Date
    06-21-2009
    Location
    UK
    MS-Off Ver
    Excel 2003
    Posts
    5

    Difference between two columns

    Hi,

    I've never really used Excel for this kind of thing so it's probably a really easy question. But I just can't find the answer.

    I have a column with "time arrived" and another with "time departed". how to I create a 3rd column with the difference between those two columns? I can do it on a row by row basis (=B1-A1) but I want a formula that will work on any new row as it's added.

    Thanks.

    Peter.
    Last edited by PSmithson; 06-22-2009 at 11:58 AM. Reason: solved

  2. #2
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Difference between two columns

    Well, the formula has to already be there for that to work. You can manually put in a formula that doesn't do anything until both values are there and copy it down quite a ways...

    =IF(B1>0, B1-A1, "")

    Or, you can highlight the existing dataset and press CTRL-L to activate Excel's "LIST" function. This is my favorite way.

    With the LIST function turned on, your dataset is now bounded with a blue box. When you click inside the data anywhere, Excel adds a blank row to the bottom of your dataset with a blue star * in the first cell. If you type data ANYWHERE on this special row, Excel will permanently expand your dataset to include this row and copy all the appropriate formulas and formatting down into the row from the row above.

    So, that way you don't have to extend the formulas down your sheet, but every new row of data you add gets all the necessary formulas and formatting.

    You also get some neat drop-down auto-filters at the top of each column, you can play with those later since they don't directly relate to your question for now.
    _________________
    Microsoft MVP 2010 - Excel
    Visit: Jerry Beaucaire's Excel Files & Macros

    If you've been given good help, use the icon below to give reputation feedback, it is appreciated.
    Always put your code between code tags. [CODE] your code here [/CODE]

    ?None of us is as good as all of us? - Ray Kroc
    ?Actually, I *am* a rocket scientist.? - JB (little ones count!)

  3. #3
    Registered User
    Join Date
    06-21-2009
    Location
    UK
    MS-Off Ver
    Excel 2003
    Posts
    5

    Re: Difference between two columns

    I played around a bit more. I didn't realise that if I pasted =B1-A1 into the 2nd row, it would magically change the 1 to a 2 for me. So I can paste that formula into the whole column and Excel sorts it out.

    So it was a bit of a more stupid question than you thought! Thanks for the reply.

    Peter.

  4. #4
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Difference between two columns

    If you copy any cell with "relative" cell references in them...Excel will adjust those references as you paste them.

    If that takes care of your need, be sure to EDIT your original post, click Go Advanced and mark the PREFIX box [SOLVED].

  5. #5
    Registered User
    Join Date
    06-21-2009
    Location
    UK
    MS-Off Ver
    Excel 2003
    Posts
    5

    Re: Difference between two columns

    I didn't realise "A1" was relative. I spent some time searching for how to use relative addressing and refer to the current row!

    If "A1" is relative, how do you write an absolute address?

    Thanks.

  6. #6
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Difference between two columns

    You slip $ signs ahead of the pieces of cell references you want to "LOCK" in place.

    A1 = completely relative
    $A1 = column is absolute, row is relative. (copying left/right won't change column ref)
    A$1 = column is relative, row is absolute (copying up/down won't change row ref)
    $A$1 = column is absolute, row is absolute (nothing changes when you copy)

  7. #7
    Registered User
    Join Date
    06-21-2009
    Location
    UK
    MS-Off Ver
    Excel 2003
    Posts
    5

    Re: Difference between two columns

    Thanks - that's clear.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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