Google Groups no longer supports new Usenet posts or subscriptions. Historical content remains viewable.
Dismiss

Formula for subtracting from same number

1,502 views
Skip to first unread message

WisconsinMom

unread,
Dec 30, 2007, 3:47:01 PM12/30/07
to
I'm setting up a "biggest loser" spreadsheet for a group. In one column, I'm
putting in the weight at weigh-in each week. In another column, I want to
keep track of how much has been lost from the original weighin.

So, if I have in cell b2 the first/original weighin, b3 I have the next
weeks weighin, b4 the 3rd week weighin and so on to b53 the last week of the
year. In cell c3 I have the formula =b2-b3 to calculate the weight loss.
Now, in cell c4, I put in =b2-b4 to calculate the total loss in 2 weeks from
the original weight. I can keep going down and manually entering this, but I
want to know if there is a way of "filling down" and keeping the first cell
constant (the b2 original weight) while changing the second number subtracted
being that current weeks weight (b3, b4, b5 etc)
I've tried everything and filling down, I then get b3-b4, b4-b5 instead of
b2-b4, b2-b5.

I hope I haven't confused you!

David Biddulph

unread,
Dec 30, 2007, 3:54:40 PM12/30/07
to
You need to look in Excel help at the difference between absolute references
and relative references.
If you use =B$2-B3, and copy down a row it will be =B$2-B4. the dollar sign
preceding a row number or column letter will stop it being incremented in
the copy operation.
--
David Biddulph

"WisconsinMom" <Wiscon...@discussions.microsoft.com> wrote in message
news:E1C090B9-3CC7-4444...@microsoft.com...

JE McGimpsey

unread,
Dec 30, 2007, 3:52:37 PM12/30/07
to
Take a look at "The difference between relative and absolute references"
in XL Help. One way:


C3: = B$2-B3

Copy down.


In article <E1C090B9-3CC7-4444...@microsoft.com>,

Mike H

unread,
Dec 30, 2007, 3:52:00 PM12/30/07
to
Hi,

Your using relative references and what you want is absolute references. try
this in C3 and drag down

=$B$2-B3

Mike

FLKulchar

unread,
Dec 30, 2007, 4:54:47 PM12/30/07
to
You need to use absolute reference versus relative reference.
In cell C3, use your input:

B2-B3...then put your cursor between the "B" and the "2" and press F4.

You will see the B2 reference change to $B2, B$2, and $B$2 successively.

Use the $B$2...copy and paste..the B2 reference will remain!

FLKulchar


"WisconsinMom" <Wiscon...@discussions.microsoft.com> wrote in message
news:E1C090B9-3CC7-4444...@microsoft.com...

WisconsinMom

unread,
Dec 30, 2007, 5:13:00 PM12/30/07
to

"Mike H" wrote:

> Hi,
>
> Your using relative references and what you want is absolute references. try
> this in C3 and drag down
>
> =$B$2-B3


Thank you!!! I knew I was forgetting something...the $'s!!! Thanks so much
for jogging my memory.

0 new messages