How can we improve Excel for Windows (Desktop Application)?

Have a BEFORE() function that refers to the cell above, below, left or right of current cell

If I want cell C5 to refer to the cell above and add 1, then I would usually use: =C4+1. But this has the problem that if I insert a row at row 5, then C6 (where the formula has been shifted down to) now refers to two cells above plus 1.

I can get around this by using: =OFFSET(C5,-1,0)+1, but this feels like overkill and I am now using a volatile function.

It would be useful to have a function that refers to one cell above, left, right or below the current cell, e.g. = BEFORE(1)+1, where:

This is a good suggestion. A running balance that doesn't break when you delete rows is a very common use case. The method I use when I want to avoid using the volatile OFFSET function is to create a "relative named range" such as cell_above or cell_left or prev_balance.

1. Select cell C5
2. Go to Formulas > Define Name
3. In the Name field, enter "cell_above"
4. Set the Scope to the current Worksheet
5. Set the Refers field to =C4 (no dollar signs)
6. Use cell_above in a formula such as =cell_above+1

Sub DefAbove()
' My checkbook.xlss spreadsheet has a column named 'Balance'.
' Old formula was ugly: =index(rows()-1,columns()) + credits - debits
' New formula is clean: =Above+credits-Debits
' Also if [a1].formula has '=sum(a1:above)' it will evaluate as =sum(a1:a9)
' note: This was tested with Selection at all 4 corners of Xls and Xlsx workbooks.
' If your formula is in row 1 or in column A, the RefersTo will be the last Row or Column (Row 1048576/XFD depending on release).

Some people would argue that BEFORE should be extended to an arbitrary number of cells away from the current one. For example, if I have 2-column groups labelled diesel, gasoline, and propane, and within each group I have a volume column and a cost column, then if I want to sum the costs, I need to sum every 2 cells. If I add a new fuel, I need to insert 2 columns at once.

Lastly, your summation example can avoid BEFORE and OFFSET altogether. If you want a cumulative sum of A1:A10, use SUM(A$1:A1). Then when you drag the formula down, A$1 will stay the same but A1 will change to A2, A3, etc.

If you are using a table, use a formula like SUM(TableName[[#Headers],[Column Name]]:[@[Column Name]]).

If you feel that we still need a BEFORE function, feel free to counter my argument.