1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Excel: How to reference a relative cell in a function

Discussion in 'Business Applications' started by eugeneg, Jan 5, 2005.

Thread Status:
Not open for further replies.
Advertisement
  1. eugeneg

    eugeneg Thread Starter

    Joined:
    Sep 26, 2003
    Messages:
    74
    How can I refer to a cell in an Excel spreadsheet three to the left of the current cell ? I want to perform a calculation which remains valid even when I insert a column between the original data and the column which contains the calculation. Perhaps an example makes this clear:
    . A B C
    1 5 3 8
    2 3 9 12
    Column C is adding the contents of the two columns to the left of it. I now want to add a new column (for illustration here named Z), and without altering column C, have column C remain as the sum of the two columns to its immediate left:
    . A B Z C
    1 5 3 6 9
    2 3 9 1 10

    Thanks
    Eugene.
     
  2. XL Guru

    XL Guru

    Joined:
    Aug 30, 2003
    Messages:
    2,702
    >> Perhaps an example makes this clear

    Crystal. In C1,

    =SUM(OFFSET($A1,,COLUMN()-3,,2))

    HTH,
    Andy
     
  3. eugeneg

    eugeneg Thread Starter

    Joined:
    Sep 26, 2003
    Messages:
    74
    A good answer but alas my previous example was too simplified to allow a simple brain like mine to extrapolate to my 'real life' situation. Sorry, let me try another example.

    . A B C
    1 3 4 =((B1-A1)/A1)
    2
    Here, column C shows the percentage change of B from A (33%). Each month I want to add a new column before the last and have the percentage change remain accurate with the newly inserted data. So if, a month after the above example, I added a new column Z, I could get the desired result by altering C thus:
    . A B Z C
    1 3 4 5 =((Z1-B1)/B1)
    2
    Yielding a result in C1 of 25% but I am trying to avoid retyping column C each month; I think it should be possible as the relationships in the equasion are constant over the months, just the cell addresses change.

    The OFFSET function works fine in the previous answer as the reference cell is explicitly entered, but in my example it would need to change each month. I have tried to use the COLUMN() function, but that gives a column number rather than a column address.

    Am I asking too much ?
     
  4. XL Guru

    XL Guru

    Joined:
    Aug 30, 2003
    Messages:
    2,702
    >> Am I asking too much?

    Hard to tell, this "Z" column stuff is rilly confusing me.

    >> I am trying to avoid retyping column C each month

    So cheat. Select the top cell of the column, press Shift+End+Down. Hit F2 and update the (row 1) formula, then press CTRL+Enter to update in all cells simultaneously.

    Or a little code:

    For Each Cell In Range(Selection, Selection.End(xlDown))
    Cell.FormulaR1C1 = "=((RC[-1]-RC[-2])/RC[-2])"
    Next Cell

    Rgds,
    Andy

    EDIT: just update the formula in the top cell. Then doubleclick the fill handle (small black square at the bottom of the selection).
     
As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/315616

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice