ROW vs ROWS for consecutive integer generation 6

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.:

=INDEX($B1:$B10,SMALL(IF($A$1:$A$10="A",ROW($A$1:$A$10)-MIN(ROW($A$1:$A$10))+1),1))

(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:

=INDEX($B:$B,SMALL(IF($A$1:$A$10="A",ROW($A$1:$A$10)),1))

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!

6 comments

  1. One Question: whether the ROWS approach can be array-entered to generate consecutive integers in array formulas, same as the ROW approach…

  2. @Qing

    Thanks for the comment and welcome to the site!

    An interesting point, and unfortunately not, no. I confess that I almost never use multi-cell array formulas, always preferring to enter the equivalent single-cell array formula and then drag it down accordingly.

    I must admit that this is one advantage of the unqualified form (i.e. without an explicit reference, e.g. ROW()) over the qualified, that it can be used to generate such row-dependent results in multi-cell array formulas.

    Many thanks for an interesting observation, and hope to hear more from you in the future.

    Regards

  3. This is amazing. I’ve never heard of this and have many times run into issues regarding the row and column array format of values returned with ROW(). I will endeavor to become one of the converted.

  4. @M Petox Hi and welcome to the site!

    And thanks for your kind comments. As you point out, the issues with, for example, the unqualified ROW() are not insignificant, which makes its popularity all the more surprising (and irksome!).

    Glad to know you’re joining the flock!

    Regards

  5. Pingback: Mmult filter

  6. Pingback: Sorting a List Alphabetically (Without Filters) « EXCELXOR

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s