# Excel: How to reference a relative cell in a function

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

Not open for further replies.
1. ### eugenegThread 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

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. ### eugenegThread 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

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

### 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.

over 733,556 other people just like you!