"Harlan Grove" wrote in message
ups.com...
"Bernie Deitrick" <deitbe @ consumer dot org wrote...
As you have found, using the ROWS function is not better than
using the ROW function, since inserting a new row 1 will mess up
the ROWS formula, but not the one ROW()-ROW($a$5) etc that I
posted.
...

Consider the formulas currently on offer with the *$A$4 pruned away,
entered into A5 (Bernie's), C5 (Biff's) and E5 (mine) and all filled
down into the next 5 rows.

Bernie's:
A5: =ROW()-ROW($A$5)+1 returns 1
A6: =ROW()-ROW($A$5)+1 returns 2
A7: =ROW()-ROW($A$5)+1 returns 3
A8: =ROW()-ROW($A$5)+1 returns 4
A9: =ROW()-ROW($A$5)+1 returns 5
A10: =ROW()-ROW($A$5)+1 returns 6

Biff's:
C5: =ROWS($1:1) returns 1
C6: =ROWS($1:2) returns 2
C7: =ROWS($1:3) returns 3
C8: =ROWS($1:4) returns 4
C9: =ROWS($1:5) returns 5
C10: =ROWS($1:6) returns 6

mine:
E5: =ROWS(E$5:E5) returns 1
E6: =ROWS(E$5:E6) returns 2
E7: =ROWS(E$5:E7) returns 3
E8: =ROWS(E$5:E8) returns 4
E9: =ROWS(E$5:E9) returns 5
E10: =ROWS(E$5:E10) returns 6


Insert cells above A1:E2. These become

Bernie's:
A7: =ROW()-ROW($A$7)+1 returns 1
A8: =ROW()-ROW($A$7)+1 returns 2
A9: =ROW()-ROW($A$7)+1 returns 3
A10: =ROW()-ROW($A$7)+1 returns 4
A11: =ROW()-ROW($A$7)+1 returns 5
A12: =ROW()-ROW($A$7)+1 returns 6

Biff's:
C7: =ROWS($1:1) returns 1
C8: =ROWS($1:2) returns 2
C9: =ROWS($1:3) returns 3
C10: =ROWS($1:4) returns 4
C11: =ROWS($1:5) returns 5
C12: =ROWS($1:6) returns 6

mine:
E7: =ROWS(E$7:E7) returns 1
E8: =ROWS(E$7:E8) returns 2
E9: =ROWS(E$7:E9) returns 3
E10: =ROWS(E$7:E10) returns 4
E11: =ROWS(E$7:E11) returns 5
E12: =ROWS(E$7:E12) returns 6

No differences so far. Undo that cell insertion.


Now insert cells above A7:E8, that is, within these formula ranges.
These formulas become

Bernie's:
A5: =ROW()-ROW($A$5)+1 returns 1
A6: =ROW()-ROW($A$5)+1 returns 2
A7: <blank
A8: <blank
A9: =ROW()-ROW($A$5)+1 returns 5
A10: =ROW()-ROW($A$5)+1 returns 6
A11: =ROW()-ROW($A$5)+1 returns 7
A12: =ROW()-ROW($A$5)+1 returns 8

Biff's:
C5: =ROWS($1:1) returns 1
C6: =ROWS($1:2) returns 2
C7: <blank
C8: <blank
C9: =ROWS($1:3) returns 3
C10: =ROWS($1:4) returns 4
C11: =ROWS($1:5) returns 5
C12: =ROWS($1:6) returns 6

mine:
E5: =ROWS(E$5:E5) returns 1
E6: =ROWS(E$5:E6) returns 2
E7: <blank
E8: <blank
E9: =ROWS(E$5:E9) returns 5
E10: =ROWS(E$5:E10) returns 6
E11: =ROWS(E$5:E11) returns 7
E12: =ROWS(E$5:E12) returns 8

Debatable whether Biff's formulas are still correct and the others
wrong or vice versa, but there's no differences in the results of
Bernie's and my formulas. Undo that cell insertion and change to
inserting entire rows, but it'll produce the same results.

So Biff's formulas can behave differently than Bernie's and mine.
Bernie's and mine will behave the same with respect to cell or row
insertion. However, they'll behave differently with respect to row
DELETION.

Bernie's:
A5: =ROW()-ROW(#REF!)+1 returns #REF!
A6: =ROW()-ROW(#REF!)+1 returns #REF!
A7: =ROW()-ROW(#REF!)+1 returns #REF!
A8: =ROW()-ROW(#REF!)+1 returns #REF!
A9: =ROW()-ROW(#REF!)+1 returns #REF!

Biff's:
C5: =ROWS($1:2) returns 2
C6: =ROWS($1:3) returns 3
C7: =ROWS($1:4) returns 4
C8: =ROWS($1:4) returns 4
C9: =ROWS($1:5) returns 5

mine:
E5: =ROWS(E$5:E5) returns 1
E6: =ROWS(E$5:E6) returns 2
E7: =ROWS(E$5:E7) returns 3
E8: =ROWS(E$5:E8) returns 4
E9: =ROWS(E$5:E9) returns 5

Bigtime differences now.

So I may grant that Biff's ROWS($1:#) formula isn't as robust as
Bernie's ROW()-ROW(base_cell)+1 formula, but Bernie's formula isn't as
robust as my ROWS(base_cell:current_cell) formula. If you don't
believe me, find an actual example of INSERTING cells/rows that would
produce different results for Bernie's and my formulas. I've already
provided one that shows they differ when DELETING cells/rows.

Then there's the simple fact that shorter formulas with one function
call are usually better (as in faster, less error-prone) than longer
formulas with multiple function calls.


You haven't convinced me that ROWS($1:#) is less robust than
ROW()-ROW(base_cell)+1 but I do agree that ROWS(base_cell:current_cell) is
better than both of the others. I *used* to use ROWS($1:#) just because it's
the easiest to understand and saves a couple of keystrokes.

--
Biff
Microsoft Excel MVP