Often we wish to incorporate into our formula a construction which, as that formula is copied down to successive rows, will generate a series of consecutive integers, usually beginning with 1.
A classic example is the standard INDEX/SMALL set-up for returning multiple values corresponding to a certain set of criteria, e.g.:
(I still have a slight preference for this version, despite the fact that alternatives may abbreviate somewhat the formula, and so also require less function calls, e.g. that preferred by Tony Valko amongst others:
My preference being based on a mixture of habit and fondness for using Named Ranges.)
The 1 at the end here represents k for the SMALL function. Instead of having to manually amend this value to 2, 3, etc. for the formulas in successive rows, we would ideally like to have this automated, and there are two main functions – ROW and ROWS – which suggest themselves as candidates for this integer generation.
I have to confess that I’m still always surprised to see the use of ROW (either in its unqualified form – ROW() – or with an actual cell reference, e.g. ROW(A1)) in these cases, given that it has two drawbacks which the equivalent ROWS construction doesn’t, i.e.:
1) It is dependent on the cell in which the initial formula is placed
2) It will give erroneous results should any rows be inserted within the range
I often see cases around the various Excel forums where a solution is given with an inclusion along the lines of ROW()-7 or some such other, where the initial formula is to be placed in row 8, perhaps with an additional piece of advice that the 7 would need to be changed should that starting row be changed.
But all this extra information and need for performing some addition/subtraction is unnecessary, since ROWS (I’m talking here of course about ROWS($1:1) – or with column references if you prefer, e.g. ROWS(A$1:A1)) gives precisely the same construction of integers yet without the two obvious drawbacks mentioned above.
Yet despite this ROW continues to be the choice of preference for the vast majority of Excel users, including some noticeably high-profile ones.
Perhaps through this post I can hope to add to the number of people who make the correct choice!