# Robust way of creating formulas in Excel

To simplify the problem, let's say I have an Excel sheet which has different numeric values in the cells A1 to B100 (two columns by 100 rows). In column C, I would like to have a formula that will multiply the numbers in A and B for that same row.

The old way I used to write the formula was buy entering in C1 `=A1*B1` and then copy the formula down to C100. This worked great but isn't very robust because if someone moves around cells in columns A or B then the formula's will track the new location and mess things up.

Someone then showed me a trick so that this formula could be more robust. I would need to enter in C1 the formula `=A:A*B:B` this effectively looks at the value in the same row and thus will continue to work as expected when the values in columns A and B are moved around.

Let's assume I now want to create a new calculated value in column D (e.g. value in A for same row + value in column A in previous row) such that the formula in D1 is `=A1`. The formula in D2 would be `=A1+A2` and this formula would then be copied down in column D. This is not as robust, is there a more robust way to do this so that it is immune from the moving around of the source cells (like my first example).

You're best option would be using named ranges: a named range can be relative to the cell entered. E.g. if you select cell `A2` and insert a name _CellAbove`with the formula`=A1`(without the`\$` that are automatically inserted!), you can use CellAbove in any cell to refer to the cell above the cell with the formula.

Therefore for your example, select cell `D2` and insert two names (using the Name Manager): * ColA: `=\$A2` * ColA_above: `=\$A1`

Then use `=ColA+ColA_above` as the formula in `D2` and copy it down. (Use `=ColA` in `D1`).

This will always return you the correct result - and is non-volatile which is really handy in large calculations!

Additional note: For your first example with the multiplication, I'd recommend to simply convert your data to an Excel Table (Insert tab -> Table. Then instead of column `A` and `B`, you would have columns, say `Price` and `Quantity`- and instead of `=A1*B1` you would end up `=[@Price]*[@Quantity]` when you enter the formula (the same way as usual, i.e. Excel will place those names, no need to worry about it). Now even if you change the sheet strucuter, the formulas will work!

Try this

``````=INDIRECT("RC1",0)+INDIRECT("R[-1]C1",0)
``````

Note, this has the disadvantage of being Volitile

Try this one:

``````=IF(ROW()=1,OFFSET(B1,0,-1),OFFSET(B1,-1,-1)+OFFSET(B1,0,-1))
``````

The only cell it references is itself so it'll only change that reference if it gets moved and it won't matter, anyway.

Or this :

``````=SUMPRODUCT(A1:A100+A2:A101)
``````

Hopefully I understood you correctly ... :)

• This gives the sum of column A and B. I don't think this is what the user is after. – evoandy Mar 7 '13 at 15:47