Using relative positions in Excel formulas

excel formula
FloatLeft picture FloatLeft · Apr 9, 2011 · Viewed 89.7k times · Source

How do I create a formula that isn't made invalid when I delete a row.

For example in cell F12 I have the formula: =F11+D12-E12

This basically says take the value from the cell above then add the value of the cell 2 to the left and subtract the value in the cell directly to the left.

However, because I'm using actual cell addresses, as soon as I delete a row, all the rows below become invalid.

How do i express the formula by relative position (ie = "1 above" + "2 to left" - "1 to left")

Thanks.

Answer

mousio picture mousio · Apr 9, 2011

You can use either

  • =OFFSET(F12,-1,0)+OFFSET(F12,0,-2)-OFFSET(F12,0,-1), or
  • =INDIRECT("F11",true)+INDIRECT("D12",true)-INDIRECT("E12",true)
  • =INDIRECT("R11C6",false)+INDIRECT("R12C4",false)-INDIRECT("R12C5",false)
  • =INDIRECT("R[-1]",false)+INDIRECT("C[-2]",false)-INDIRECT("C[-1]",false)

Both functions also allow to specify ranges, just use whatever has your personal preference (see Excel Help)…

Related questions

How to turn a string formula into a "real" formula

I have 0,4*A1 in a cell (as a string). How can convert this "string formula" into a real formula and calculate its value, in another cell?

Read More
Fill formula down till last row in column

I'm trying to draw down the formula that's in cell M3 to the end of the data set. I'm using column L as my base to determine the last cell with data. My formula is a concatenation of two cells …

Read More
Using OR & AND in COUNTIFS

I would like to include an "AND" condition for one of the conditions I have in my COUNTIFS clause. Something like this: =COUNTIFS(A1:A196;{"Yes"or "NO"};J1:J196;"Agree") So, it should return the number of rows where: (…

Read More