Excel - Constant Cell Reference

Asked By Gary on 26-Feb-09 09:34 AM
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 replied on 28-Feb-09 01:51 AM
Change the first formula to this:

=3DD$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

I
in E3
copy
it
rom
Gary replied on 26-Feb-09 09:59 AM
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 replied on 28-Feb-09 01:51 AM
You can do that like this:

=3DIF(D3=3D"","",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

ow
is
s for
2 I
n
opy
, it
Gord Dibben replied on 26-Feb-09 10:19 AM
=IF(D3="","",D$2-D3)

Copy down as far as you wish.


Gord Dibben  MS Excel MVP
Gary replied on 26-Feb-09 10:36 AM
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 replied on 28-Feb-09 01:51 AM
You're welcome, Gary - thanks for feeding back.

Pete