is "=row()-row([#Headers])" the best way to get auto-incremented sequential numbers in an Excel table?

[Mr. Snrub]

I use the formula "=row()-row([#Headers])" to get an auto-incremented column of numbers. Is this the best way to do this? I think making two calls to the "ROW()" function is sub-optimal.

 

[Eric W]

I tend to agree with you. One issue I have with table nomenclature in formulas is that there's no good way to refer to rows above or below the current line. You can use @ to indicate the current line, but without that you have to use some awkward INDEX() function with the same double ROW construct in it. I can't say for certain that there's no better way that what you have, but in several years of working with these kind of formulas, I've never seen a better way.

 

[Tetra201]

Maybe

=ROWS([#Headers]:[@])-1

 

[Mr. Snrub]

Maybe

=ROWS([#Headers]:[@])-1

Is there some sort of diagnostic tool or test that I can use to verify whether this is indeed a better option and should therefore be adopted as a best practice?

 

[Tetra201]

It depends on what you mean under "better." The formula from Post # 3 has a single function call, but the formula from Post # 1 is substantially faster.

 

[Mr. Snrub]

“Faster” is what I meant. So Post #1 would be the preferred approach. But how do you know that it is faster? Is there a way to time these functions?

 

[Tetra201]

Try running the following macro in an empty sheet. The macro creates a 2-column, 10,000-row table and populates it with the two formulas.

I am getting ~0.03 sec for the formula from Post # 1 and ~0.09 sec for the formula from Post # 3.

Sub Test()
    Dim x, t
    Set x = ActiveSheet.ListObjects.Add(xlSrcRange, Range("A1:B10000"), , xlYes)
    t = Timer
    Range("A2").Formula = "=ROW()-ROW([#Headers])"
    MsgBox "=ROW()-ROW([#Headers])" & vbCrLf & vbCrLf & Timer - t
    t = Timer
    Range("B2").Formula = "=ROWS([#Headers]:[@])-1"
    MsgBox "=ROWS([#Headers]:[@])-1" & vbCrLf & vbCrLf & Timer - t
End Sub

 

[Mr. Snrub]

Well that code snippet inspired me to go down a deep rabbit hole with this stuff. I wanted to know exactly what was the best (fastest) way to create a running balance column:

    Dim t1, t2, t3, t4, t5, t6, t7
    Dim formula1 As String
    Dim formula1_time
    Dim formula2 As String
    Dim formula2_time
    Dim formula3 As String
    Dim formula3_time
    Dim formula4 As String
    Dim formula4_time
    Dim formula5 As String
    Dim formula5_time
    Dim formula6 As String
    Dim formula6_time

    formula1 = "=if(row()-row([#Headers])=1,A2,A2+B1)"
    formula2 = "=if(row()-row([#Headers])=1,[@[Column1]],[@[Column1]]+B1)"
    formula3 = "=if(row()-row([#Headers])=1,[@[Column1]],[@[Column1]]+indirect(address(row()-1,column())))"
    formula4 = "=if(row()-row([#Headers])=1,[@[Column1]],[@[Column1]]+offset([@Column5],-1,0))"
    formula5 = "=if(row()-row([#Headers])=1,[@[Column1]],[@[Column1]]+indirect(""b"" & row() - 1))"
    formula6 = "=SUM(INDEX([Column1],1):[@[Column1]])"

    t1 = Timer
    Range("B2").Formula = formula1
    t2 = Timer
    Range("C2").Formula = formula2
    t3 = Timer
    Range("D2").Formula = formula3
    t4 = Timer
    Range("E2").Formula = formula4
    t5 = Timer
    Range("F2").Formula = formula5
    t6 = Timer
    Range("G2").Formula = formula6
    t7 = Timer

    formula1_time = t2 - t1
    formula2_time = t3 - t2
    formula3_time = t4 - t3
    formula4_time = t5 - t4
    formula5_time = t6 - t5
    formula6_time = t7 - t6

    Range("B2:G10000").NumberFormat = "_($* #,##0.00_);_($* (#,##0.00);_($* """" - """"??_);_(@_)"

    'Call MsgBox("Results: " & (t2 - t1) & ", " & (t3 - t2) & ", " & (t4 - t3))
    Call LogIt("formula1: " & formula1)
    Call LogIt("time:     " & formula1_time)
    Call LogIt("formula2: " & formula2)
    Call LogIt("time:     " & formula2_time)
    Call LogIt("formula3: " & formula3)
    Call LogIt("time:     " & formula3_time)
    Call LogIt("formula4: " & formula4)
    Call LogIt("time:     " & formula4_time)
    Call LogIt("formula5: " & formula5)
    Call LogIt("time:     " & formula5_time)
    Call LogIt("formula6: " & formula6)
    Call LogIt("time:     " & formula6_time)

Results:

formula1: =if(row()-row([#Headers])=1,A2,A2+B1)
time:     9.765625E-02

formula2: =if(row()-row([#Headers])=1,[@[Column1]],[@[Column1]]+B1)
time:     0.0234375

formula3: =if(row()-row([#Headers])=1,[@[Column1]],[@[Column1]]+indirect(address(row()-1,column())))
time:     0.0546875

formula4: =if(row()-row([#Headers])=1,[@[Column1]],[@[Column1]]+offset([@Column5],-1,0))
time:     5.078125E-02

formula5: =if(row()-row([#Headers])=1,[@[Column1]],[@[Column1]]+indirect("b" & row() - 1))
time:     5.859375E-02

formula6: =SUM(INDEX([Column1],1):[@[Column1]])
time:     2.941406

So it turns out formulas 1 and 2 are about the same speed (so we can conclude using "[[@]]" table names doesn't slow things down much at all.) And using "indirect()" or "offset()" are a little slower but not too bad. But the real killer is formula6, the sum(index()) one, which should NEVER BE USED AT ALL ANYWHERE. This is really good to know!