A problem with relative reference

  • Thread starter Thread starter David F
  • Start date Start date
D

David F

I have created column A and B with numerical values.
All the respective (same row) cells of column C, as the LAST COLUMN,
contain the formula: =ROUND(((RC[-1]-RC[-2])*100/RC[-2]),1)
The idea is simply for the last column to show the difference in percentage
between the values of the LAST TWO COLUMNS THAT PRECEEDED
it - I intend from time to time introduce a new set of numerical
values in a new column I would add from time to time to the left of
the last column.
But, as I insert new columns with values left to the last column,
the last column keeps to show its original values, that is, the difference
(in percentage) of the first two columns. In other words, after
adding the first new column, the formula changed to:
=ROUND(((RC[-2]-RC[-3])*100/RC[-3]),1),
while I intended that the -1 & -2 will remain fixed.

How should I write the formula to achieve the goal?

If it is not achievable by growing the table horizontally,
would it be achieveable if I grow the table vertically by
adding rows? If yes - how?

Thanks,
David
 
Try it like this:

=ROUND((OFFSET(RC,,-1)-OFFSET(RC,,-2))*100/OFFSET(RC,,-2),1)

That will always refer to the 2 columns to the immediate left of the formula
cell.
 
Great. It worked.
Many thanks,
David


T. Valko said:
Try it like this:

=ROUND((OFFSET(RC,,-1)-OFFSET(RC,,-2))*100/OFFSET(RC,,-2),1)

That will always refer to the 2 columns to the immediate left of the formula
cell.

--
Biff
Microsoft Excel MVP


David F said:
I have created column A and B with numerical values.
All the respective (same row) cells of column C, as the LAST COLUMN,
contain the formula: =ROUND(((RC[-1]-RC[-2])*100/RC[-2]),1)
The idea is simply for the last column to show the difference in
percentage
between the values of the LAST TWO COLUMNS THAT PRECEEDED
it - I intend from time to time introduce a new set of numerical
values in a new column I would add from time to time to the left of
the last column.
But, as I insert new columns with values left to the last column,
the last column keeps to show its original values, that is, the difference
(in percentage) of the first two columns. In other words, after
adding the first new column, the formula changed to:
=ROUND(((RC[-2]-RC[-3])*100/RC[-3]),1),
while I intended that the -1 & -2 will remain fixed.

How should I write the formula to achieve the goal?

If it is not achievable by growing the table horizontally,
would it be achieveable if I grow the table vertically by
adding rows? If yes - how?

Thanks,
David
Click to expand...
Click to expand...
 
You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


David F said:
Great. It worked.
Many thanks,
David


T. Valko said:
Try it like this:

=ROUND((OFFSET(RC,,-1)-OFFSET(RC,,-2))*100/OFFSET(RC,,-2),1)

That will always refer to the 2 columns to the immediate left of the
formula
cell.

--
Biff
Microsoft Excel MVP


David F said:
I have created column A and B with numerical values.
All the respective (same row) cells of column C, as the LAST COLUMN,
contain the formula: =ROUND(((RC[-1]-RC[-2])*100/RC[-2]),1)
The idea is simply for the last column to show the difference in
percentage
between the values of the LAST TWO COLUMNS THAT PRECEEDED
it - I intend from time to time introduce a new set of numerical
values in a new column I would add from time to time to the left of
the last column.
But, as I insert new columns with values left to the last column,
the last column keeps to show its original values, that is, the
difference
(in percentage) of the first two columns. In other words, after
adding the first new column, the formula changed to:
=ROUND(((RC[-2]-RC[-3])*100/RC[-3]),1),
while I intended that the -1 & -2 will remain fixed.

How should I write the formula to achieve the goal?

If it is not achievable by growing the table horizontally,
would it be achieveable if I grow the table vertically by
adding rows? If yes - how?

Thanks,
David
Click to expand...
Click to expand...
Click to expand...
 
Back
Top