consecutive numbers

G

Guest

I want to have a column which has a formula which multiplies an absolute cell
by a number which increases incrementally.
=1*$A$4
=2*$A$4
etc. up to about 100. How can I enter the consecutive number into the
formula so I can replicate it? I don't want an extra column with the numbers
1-100!
Thanks
 
G

Guest

I had thought of that but it doesn't necessarily start in row 1 so I have got
past that by maybe using ROW(A1)-4 if it starts in row 5 for example but then
it would not work if rows were inadvertently added above so was wondering if
there was a safer formula?
Debbie
 
B

Bernie Deitrick

Debbie,

Row(A1) will return 1 no matter what cell you start in. It will increment to Row(A2) - which will
return 2 - when incremented.

If you want to make sure that you can insert rows without affecting your formula, use

=(ROW()-ROW($A$5)+1)*$A$4

where the formula is entered into a cell on row 5.

HTH,
Bernie
MS Excel MVP
 
G

Guest

Of course!! What a ninny I am.
Thanks

Bernie Deitrick said:
Debbie,

Row(A1) will return 1 no matter what cell you start in. It will increment to Row(A2) - which will
return 2 - when incremented.

If you want to make sure that you can insert rows without affecting your formula, use

=(ROW()-ROW($A$5)+1)*$A$4

where the formula is entered into a cell on row 5.

HTH,
Bernie
MS Excel MVP
Click to expand...
 
H

Harlan Grove

T. Valko said:
Another one:

=ROWS($1:1)*$A$4
Click to expand...
....

Better to start with a single cell, e.g., if the formula were entered
in cell X99,

X99:
=ROWS(X$99:X99)*$A$4

since any entries/changes anywhere in row 1 would cause your formula
to recalc.
 
G

Guest

If I do this
=ROWS($1:1)*$A$4
Click to expand...
Then insert a row above it all goes up the creak. I am not sure I understand
this function but I do understand the ROW() function
Thanks
 
G

Guest

The last one I understand having looked what the ROWS function does however
why is this better than using the ROW() function?
Thanks
 
B

Bernie Deitrick

Phippsy,

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.

HTH,
Bernie
MS Excel MVP
 
T

T. Valko

If I enter this formula in B2:

=ROWS($1:1)*$A$4

Then insert a new row 1, the formula correctly changes the references to:

=ROWS($2:2)*$A$5

Which is the equivalent of:

=1*$A$5

How does inserting a new row above the formula cell mess up the ROWS
function?
 
H

Harlan Grove

Bernie Deitrick said:
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.
Click to expand...
....

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

Peo Sjoblom

It is simpler to use ROWS than using ROW the way you do since you need two
row functions, one to offset any possible inserted rows on top thus you need
to use a cell in the same row as the formula to "anchor" it. I always use it
the same way as in Harlan's example and I find it much easier to use and as
he showed it also adapts to row deletion so then we can also say it is
better than using ROW
 
T

T. Valko

Harlan Grove said:
...

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.
Click to expand...

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

Guest

Well guys I am now totally confused and need a week to unravel what you have
all suggested. I am sure I will plump with one eventually and thank you so
much for your interest and time. This is in fact not for me but for a
colleague and will leave him to decide!
Thakns
Debbie
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Finding location (or start cell) of highest consecutive values 3
How to count the maximum number of consecutives? 9
Counting consecutive negative value 7
How can I do this? 7
Excel Count of Consecutive Data 0
Count Consecutive string of same number 6
How to Create Macro to Increase Cell Value to one and Print Worksheet 4
"Cross"-Auto filling: consecutive columns into rows 1

Top