CODEWITHSUNDEEP
excelformula
FloatLeft
FloatLeft

Reputation: 1337

Using relative positions in Excel formulas

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.

Upvotes: 32

Views: 102367

Answers (3)

Stephen Turner
Stephen Turner

Reputation: 7314

Reading between the lines... is your actual problem creating a running total from additions and deductions columns?

You can sum each column starting with an absolute reference and ending with a relative reference

Assuming row 11 is your first, put =SUM(D$11:D12)-SUM(E$11:E12) in F12.

As you copy this cell down the absolute reference remains the same where the relative reference automatically updates extending the range. You can even copy the formula up one line and it will work on the first line too.

Deleting and inserting lines doesn't break the formula, though any extra cells would be included in the SUM().

Upvotes: 1

mousio
mousio

Reputation: 10337

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)…

Upvotes: 42

Howard
Howard

Reputation: 39197

  • You can switch e.g. to the R1C1 reference style (excel options)
  • Use OFFSET function (e.g. =OFFSET(F12;-1;0) for above)

Upvotes: 5

Related Questions