formula to follow preceeding value

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi,

Is there a function in Excel that will read the cell directly above itself in a column and follow on as the next number. I am currently using the formula ="C4"+1 etc, but this is not an ideal formula as when I insert or delete rows things start to go wrong !.

Any help v much appreciated

Thanks,

Mike
 
Jepp

It's something like this "=SUM(R[-1]C)"

R = rows C= column and in this case the [-1] means current ROW -1 (but the same column :-)

"=SUM(R[-1]C[-1])" should thefore be one up and one left, and then you can add +1 or whatever....

Kjell
 
Hi Mike
maybe ROW() is the function you're looking for. Put
=ROW()
in call A1 and copy down

HTH
Frank
 
Mike,

=INDIRECT(ADDRESS(ROW()-1,COLUMN()))+1

HTH,
Bernie
MS Excel MVP

Mike said:
Hi,

Is there a function in Excel that will read the cell directly above
Click to expand...
itself in a column and follow on as the next number. I am currently
using the formula ="C4"+1 etc, but this is not an ideal formula as
when I insert or delete rows things start to go wrong !.
 
Kjell,

That's the same formula that he has, just in RC notation. It has the
same problem that he is talking about.

HTH,
Bernie
MS Excel MVP

Kjell said:
Jepp

It's something like this "=SUM(R[-1]C)"

R = rows C= column and in this case the [-1] means current
Click to expand...
ROW -1 (but the same column :-)
"=SUM(R[-1]C[-1])" should thefore be one up and one left, and then you can add +1 or whatever....

Kjell
Click to expand...
 
another way if you always want to add 1 to the preceeding row try the
following for column A
=INDIRECT("A" & ROW()-1)+1

Now you can delete rows in between. the formula will still work
Frank
 
Back
Top