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

=ROW(A1)*$A$4, and copy down.
--
David Biddulph

"Phippsy" wrote in message
...
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

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

"David Biddulph" wrote:

=ROW(A1)*$A$4, and copy down.
--
David Biddulph

"Phippsy" wrote in message
...
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

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


"Phippsy" wrote in message
...
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

"David Biddulph" wrote:

=ROW(A1)*$A$4, and copy down.
--
David Biddulph

"Phippsy" wrote in message
...
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

Of course!! What a ninny I am.
Thanks

"Bernie Deitrick" wrote:

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


"Phippsy" wrote in message
...
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

"David Biddulph" wrote:

=ROW(A1)*$A$4, and copy down.
--
David Biddulph

"Phippsy" wrote in message
...
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

Another one:

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

--
Biff
Microsoft Excel MVP


"Phippsy" wrote in message
...
Of course!! What a ninny I am.
Thanks

"Bernie Deitrick" wrote:

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


"Phippsy" wrote in message
...
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

"David Biddulph" wrote:

=ROW(A1)*$A$4, and copy down.
--
David Biddulph

"Phippsy" wrote in message
...
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

"T. Valko" wrote...
Another one:

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

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.

If I do this
=ROWS($1:1)*$A$4
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

"Harlan Grove" wrote:

"T. Valko" wrote...
Another one:

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

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.

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

"Harlan Grove" wrote:

"T. Valko" wrote...
Another one:

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

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.

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


"Phippsy" wrote in message
...
The last one I understand having looked what the ROWS function does however
why is this better than using the ROW() function?
Thanks

"Harlan Grove" wrote:

"T. Valko" wrote...
Another one:

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

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.

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?

--
Biff
Microsoft Excel MVP


"Bernie Deitrick" <deitbe @ consumer dot org wrote in message
...
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


"Phippsy" wrote in message
...
The last one I understand having looked what the ROWS function does
however
why is this better than using the ROW() function?
Thanks

"Harlan Grove" wrote:

"T. Valko" wrote...
Another one:

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

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.

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

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


--
Regards,

Peo Sjoblom


"Bernie Deitrick" <deitbe @ consumer dot org wrote in message
...
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


"Phippsy" wrote in message
...
The last one I understand having looked what the ROWS function does
however
why is this better than using the ROW() function?
Thanks

"Harlan Grove" wrote:

"T. Valko" wrote...
Another one:

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

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.

"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

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

"Peo Sjoblom" wrote:

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


--
Regards,

Peo Sjoblom


"Bernie Deitrick" <deitbe @ consumer dot org wrote in message
...
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


"Phippsy" wrote in message
...
The last one I understand having looked what the ROWS function does
however
why is this better than using the ROW() function?
Thanks

"Harlan Grove" wrote:

"T. Valko" wrote...
Another one:

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

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.