Skip to content

Instantly share code, notes, and snippets.

@paxperscientiam
Created February 15, 2022 05:12
Show Gist options
  • Save paxperscientiam/61dd7de67349a3aa6ce41b2764f779b8 to your computer and use it in GitHub Desktop.
Save paxperscientiam/61dd7de67349a3aa6ce41b2764f779b8 to your computer and use it in GitHub Desktop.
Compute relative row in Excel
# 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.
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment