Results 1 to 7 of 7
  1. #1
    2 Star Lounger
    Join Date
    Mar 2001
    Location
    Soldotna, Alaska, USA
    Posts
    103
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Adding colums (Office XP)

    Ok here goes a simple question. I have two columns that I am adding up and then in column F I have the two columns added together. Is there a simple way I do not have to go down each column and hand code the sum in? Here is an example, line 1 would look like this in column F =(C1-B1)*24 Line 2 would look like this =(C2-B2)*24 and so on. So do I have to enter this code to line 100 by hand changing it each time or is there an easier way to do this?

    Thanks for helping the excel challenged.

  2. #2
    3 Star Lounger
    Join Date
    Jul 2002
    Location
    Memphis, Tennessee, USA
    Posts
    243
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Adding colums (Office XP)

    No, you can just create the formula for row 1 and then copy it to all the other rows. The cell references will change for each row you paste it to. You can also use AutoFill and a number of other techniques.

    FYI, the Excel term that describes the ability for the program to change cell references when you copy them is "relative reference". The cell references change relative to how far you paste them from where you copied them. If you disallowed the references to be changed when copied, that would be called "absolute referencing" and would involve having the "$" in front of the references in your formula.

    -Lenny

  3. #3
    Platinum Lounger
    Join Date
    Jan 2001
    Posts
    3,788
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Adding colums (Office XP)

    There is a very quick way of copying the formula.

    Enter the formula in the top cell & select this cell. Double click on the small black square in the bottom right hand corner of the cell. Excel will then copy the formula downwards for the rest of the block.

  4. #4
    3 Star Lounger
    Join Date
    Jul 2002
    Location
    Memphis, Tennessee, USA
    Posts
    243
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Adding colums (Office XP)

    <hr>Double click on the small black square <hr>

    That's provided non-contiguous data is in the column to the left of the cell in which you are double clicking the AutoFill handle. I didn't want to go there with this poster because he was setting up formulas next to blank cells on the left. He would come back later and fill in the data after the formulas were created. In such a case, that trick won't work.

    -Lenny

  5. #5
    2 Star Lounger
    Join Date
    Mar 2001
    Location
    Soldotna, Alaska, USA
    Posts
    103
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Adding colums (Office XP)

    I tried to cut the formula from row 1 F and paste it into row 39 F and it did not work right. It pasted in exactly what I copied. Maybe I misunderstood. I thought the from would fix the formula if I put it on a different line. Is there a simple easy way to do this? You mentioned autofill, but I am unfamiliar with that feature.

    Thanks for all the help! <img src=/S/bubbles.gif border=0 alt=bubbles width=31 height=17>

  6. #6
    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: Adding colums (Office XP)

    Don't CUT and PASTE

    COPY and PASTE

    CUT will keep the references exactly as they are, COPY will keep them relative.

  7. #7
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Adding colums (Office XP)

    Use Copy not Cut and the formula should be adjusted.

    However, Autofill is what you really want. There are several ways to do an autofill:

    1- Click on the first cell where you want the formula and drag to select all of the cells where you want the formula. The first cell you clicked on should be the active cell, and all of the others should be selected. Type the formula into the active cell, but hold down the Ctrl key when you press Enter to enter the formula. The formula should be entered into all of the selected cells with the appropriate changes.

    2- Enter the formula into the first cell where you want it. Select the cell where you just entered the formula. There should be a small solid square in the lower right corner of the cell, an if you move the cursor over that square it should change from a large open cross to a solid cross. With the cursor over the small solid square and the cursor changed to the solid cross, press and hold down the left mouse button and drag the cell to all of the cells where you want the formula. When you release the mouse button, the formula should be copied to all of the cells with the appropriate changes.

    3- Enter the formula in the first cell where you want it. Select that cell and drag to select all of the cells where you want the formula. Now, select Fill from the Edit menu and then on the flyout menu select Down, Right, Up, or Left, whichever is appropriate.
    Legare Coleman

Posting Permissions

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