Constant Cell Reference

[Gary]

I have spread sheet I am creating that is to track wieght loss. So in D2 I
am putting the original weight. In D3 I put the next days weight, and in E3
I am showing the difference. When I go to the next day, to D4, when I copy
the formula (=D2-D3) to the row 4, instead of the D2 staying constant, it
then becomes =D3-D4.

How do I create a formula that the D2, the starting weight, is the cell from
which the other cells subtract from, without having to write each one
individually?

Thank you

Gary

 

[Pete_UK]

Change the first formula to this:

=D$2-D3

and then copy down. The $ symbol in front of the 2 tells Excel not to
change that when the formula is copied down. Look at Absolute
References in XL Help for more details.

Hope this helps.

Pete

 

[Gary]

Yes, perfect, and thank you for your clear explaination.

I'm not sure if I should just start a new post with a new question, but how
to I now have this formula return a 0 or leave the cell blank when there is
no data (new weight next dayu, D3). So I can paste the formula in cells for
the next month. Hope that is clear.

Thanks again

Gary
Change the first formula to this:

=D$2-D3

and then copy down. The $ symbol in front of the 2 tells Excel not to
change that when the formula is copied down. Look at Absolute
References in XL Help for more details.

Hope this helps.

Pete

 

[Pete_UK]

You can do that like this:

=IF(D3="","",D$2-D3)

and then copy this down as far as you need.

Basically, this says if D3 is empty then return a blank cell,
otherwise subtract D3 from D2 and show the result.

Hope this helps.

Pete

 

[Gord Dibben]

=IF(D3="","",D$2-D3)

Copy down as far as you wish.


Gord Dibben MS Excel MVP

 

[Gary]

Thank you again, perfect.

Gary

You can do that like this:

=IF(D3="","",D$2-D3)

and then copy this down as far as you need.

Basically, this says if D3 is empty then return a blank cell,
otherwise subtract D3 from D2 and show the result.

Hope this helps.

Pete

 

[Pete_UK]

You're welcome, Gary - thanks for feeding back.

Pete