A review of various ways to determine relative row numbers in Excel

ComputeRelativeRowsExcel.txt

# v. 1.0
# Originally posted here: https://www.reddit.com/r/excel/comments/339gw7/discussion_simplest_possible_way_to_get_this/
# Use at your own discretion.
# Pax Per Scientiam

This is pretty much ripped directly from a posting I made on Reddit. I'm putting it here for posterity. Please note that I'm using the Excel 2011 (Mac) version of structured reference syntax, which I think is a bit different than that of Excel 2013 (Windows).


I've long wondered if there were a simpler way to get the relative row number of an Excel Table.
This has been my usual go to (refers to some table called "Table1" and some column therein called "column1"),which is entered into column1:
=ROW()-ROW(Table1[[#Headers],[column1]])

Incidentally, that formula does take on a more compact form outside of Excel Tables. Assuming entry into A2:
=ROW()-ROW($A$1)


Pretty good, but I'd like to do better. Well, after playing around a bit I've found that there's a more elegant way...though possibly more resource intensive (no clue)...which refers to the whole column:
=1+ROW()-ROW([column1])

For use outside of an Excel Table, this takes the following form and needs to be dragged (unlike formulas in so-called calculated columns):
=1+ROW()-ROW($A$1:A1)


I also found that this works, though creates a circular reference as it requires self-reference:
=MATCH([column1],[column1],0)
With iterative calculations turned on, it seems to be fine.
Anyway, I wanted to share my methods and to see if anyone out there has an even better way of getting "this" relative row number of an Excel Table.

This array formula also works without error:
={MATCH(ROW(),ROW([column1]),0)}


The point of all this is that sometimes one needs to know what the current relative row number is for complex formula construction. Hopefully, the above is helpful and inspiring to other Excel users.