Feedback by UserVoice

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:

1 = above, 2 = left, 3 = right, 4 = below.

3 votes
Sign in
Check!
(thinking…)
Reset
or sign in with
  • facebook
  • google
    Password icon
    Signed in as (Sign out)

    We’ll send you updates on this idea

    Gary shared this idea  ·   ·  Flag idea as inappropriate…  ·  Admin →

    6 comments

    Sign in
    Check!
    (thinking…)
    Reset
    or sign in with
    • facebook
    • google
      Password icon
      Signed in as (Sign out)
      Submitting...
      • Jon Wittwer commented  ·   ·  Flag as inappropriate

        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

      • Uncle Bob commented  ·   ·  Flag as inappropriate

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

        On Error Resume Next
        ActiveWorkbook.Names.Add name:="Above", RefersToR1C1:="=!R[-1]C"
        ActiveWorkbook.Names.Add name:="Below", RefersToR1C1:="=!R[1]C"
        ActiveWorkbook.Names.Add name:="Before", RefersToR1C1:="=!RC[-1]"
        ActiveWorkbook.Names.Add name:="After", RefersToR1C1:="=!RC[1]"
        ' this is a safety net in case the workbook previously had a different definition of Above or Below or Before or After.
        Above1: If InStr(1, "=!R[-1]C" & Replace("=!R[-1]C", -1, Application.Rows.count - 1), ActiveWorkbook.Names("Above").RefersToR1C1, 1) = 0 Then Stop: MsgBox "'Above' was not assigned"
        Before1: If InStr(1, "=!RC[-1]" & Replace("=!RC[-1]", -1, Application.Columns.count - 1), ActiveWorkbook.Names("Before").RefersToR1C1, 1) = 0 Then Stop: MsgBox "'Before' was not assigned"
        Below1: If InStr(1, "=!R[1]C" & Replace("=!R[1]C", 1, 1 - Application.Rows.count), ActiveWorkbook.Names("Below").RefersToR1C1, 1) = 0 Then Stop: MsgBox "'Below' was not assigned"
        After1: If InStr(1, "=!RC[1]" & Replace("=!RC[1]", 1, 1 - Application.Columns.count), ActiveWorkbook.Names("After").RefersToR1C1, 1) = 0 Then Stop: MsgBox "'After' was not assigned"

        On Error GoTo 0
        End Sub

      • Uncle Bob commented  ·   ·  Flag as inappropriate

        This comes close.
        Select cell a2
        alt M M D
        name:= Before refersto: "=!a1"

        select a2
        alt M M D
        name:= after refersto: "=!a3"

      • Kenneth Barber commented  ·   ·  Flag as inappropriate

        If such a function is made, it should be called BESIDE or ADJACENT.

        As you mentioned, OFFSET already solves your problem, but it is volatile. I think that it's better to solve the issues related to volatile functions than to work around them with new functions. See the link below.
        https://excel.uservoice.com/forums/304921-excel-for-windows-desktop-application/suggestions/10769886-today-volatility-reduction

        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.

      Feedback and Knowledge Base