Robust way of creating formulas in Excel

Question

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

Many thanks in advance

Answer 1

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 _CellAbovewith 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!

Answer 2

If you don't mind using array formulas here, the following, entered as an array formula in the range D2:D100 will work as you ask for:

{=A1:A99 + A2:A100}

(Do not type the curly braces: highlighting cells D2:D100, typing the formula, and then using Control-Shift-Enter to "confirm/enter" to actualize things will see Excel provide the curly braces around the whole formula.)

This would have worked IN 2013 and does with Excel 2013 today. Of course, today (2023), the {CSE} entry method is not required.

Being an array formula, the cells it references will not change if someone drags and drops a cell from the A1:A100 range. It will simply recalculate for what now exists. So drag A23 and drop it on A12 and A23 will be seen as blank/0 when recalcing and A12 will be seen as having A23's old value when recalcing. The formulas in the array do not change to follow the dragging and dropping.

Answer 3

Try this

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

Note, this has the disadvantage of being Volitile

Answer 4

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.

Answer 5

Or this :

=SUMPRODUCT(A1:A100+A2:A101)

Hopefully I understood you correctly ... :)