Explanation
When no argument is provided, the ROW function returns the “current row”, that is, the row number of the cell that contains it. When a cell reference is provided, ROW returns the row number of the cell. When a range is provided, ROW returns the first row number in the range.
In the example shown, the formula in B5 is:
=ROW()-ROW(Table1[#Headers])
The first ROW returns 5, since ROW is provided no argument, and resides in cell B5. The second ROW uses a structured reference:
Table1[#Headers] // header row
The header row resolves to the range $B$4:$F$4, so ROW returns 4. For the first 3 rows of the table, we have:
B5=5-4 // 1
B6=6-4 // 2
B7=7-4 // 3
No header row
The formula above works great as long as a table has a header row, but it will fail if the header row is disabled. If you are working with a table without a header row, you can use this alternative:
=ROW()-INDEX(ROW(Table1),1,1)+1
In this formula, the first ROW function returns the current row, as above. The INDEX function returns the first cell in the range Table1 (cell B5) to the second ROW function, which always returns 5. For the first 3 rows of the table, the formula works like this:
B5=5-5+1 // 1
B6=6-5+1 // 2
B7=7-5+1 // 3
This formula will continue to work normally even when the header row is disabled.
Explanation
This formula is a good example of how structured references can make working with data in Excel much easier. At the core, this is what we’re doing:
=AVERAGE(first:last)
where “first” is a reference to the first cell to include in the average and “last” is a reference to the last cell to include. The result is a range that includes the N cells to average.
To get the first cell in the range, we use INDEX like this:
INDEX(Table1[Sales],ROWS(Table1)-(F4-1))
The array is the entire Sales column, and row number worked by subtracting (n-1) from total rows.
In the example, F4 contains 3, so the row number is 10-(3-1) = 8. With a row number of 8, INDEX returns C12.
To get the last cell we use INDEX again like this:
INDEX(Table1[Sales],ROWS(Table1))
There are 10 rows in the table, so INDEX returns C14.
The AVERAGE function then returns the average of C12:C14, which is $78.33.
