adjusting sums

[ericaamousseau]

I was not sur if there was a way to have a formula move cells as you type. I
am not explaining it right, but say I have a spreadsheet for weight loss for
a group of people if the begginging weight is in cell A1 and the next week's
weight is in B1 and week after that is in C1 can I have the subtraction
"follow" the numbers to show weight lost each week? :

A1 B1 Formula
196 194 2

next week would be

A1 B1 C1 Formula
196 194 190 4

So that when I typed in C1 the formula shifted so as to ignor A1 and
subtract C1 from B1. Is this possible? thanks!

 

[John]

HI
With this formula in B2 =MAX(A$1:B$1)-MIN(A1:B1) and copy along to F2, i
get the result you see below.hoping this is what you're looking for.
A1 B1 C1 D1 E1 F1
200 196 190 188 175 164
4 6 2 13 11
HTH
John

 

[smartin]

I was not sur if there was a way to have a formula move cells as you type. I
am not explaining it right, but say I have a spreadsheet for weight loss for
a group of people if the begginging weight is in cell A1 and the next week's
weight is in B1 and week after that is in C1 can I have the subtraction
"follow" the numbers to show weight lost each week? :

A1 B1 Formula
196 194 2

next week would be

A1 B1 C1 Formula
196 194 190 4

So that when I typed in C1 the formula shifted so as to ignor A1 and
subtract C1 from B1. Is this possible? thanks!

This will "follow" your data entry by looking for the right-most entry
on the row and subtracting it from the penultimate entry.

This is an array formula, so you must press Ctrl+Shift+Enter (not just
Enter) after you are done typing:

=INDEX($A1:$C1,1,MAX(($A1:$C1<>0)*COLUMN($A1:$C1))-1)-INDEX($A1:$C1,1,MAX(($A1:$C1<>0)*COLUMN($A1:$C1)))

You will likely need to adjust the "$C1" references to match the
right-most column where you will enter data.

Another way (much simpler) would be to add a formula in B2 like

=B1-A1

Fill right, then just look at the values.

 

[smartin]

Will give incorrect result if a person has a weight gain instead of a
loss. Alas, it happens to the best of us!

 

[John]

Hi Smartin
You're right, the best is the simplest one you got =B1-A1.
When it's to easy you always look further then needed.
But that's the way we get better.
All the Best
John

 

[Suleman Peerzade]

Hi,

I would suggest that go on fillling the data from A1 to L1 and in M1 type
the formula
=A1-L1 this will give an answer for a complete course of weight loss. Say in
this case the course would be of 3 months i.e 12 weeks
Now divide the figure you got in M1 i.e M1/12 this will give you the weight
loss data per week.

Even if the person lossing or gaining during the course it makes no
difference cause you are looking for only two values one is the start value
and 2nd is the end value.
--
_______________________
Click "Yes" if it helps
________
Thanks
Suleman Peerzade

 

[MyVeryOwnSelf]

I have a spreadsheet for

weight loss for a group of people if the begginging weight is in cell
A1 and the next week's weight is in B1 and week after that is in C1
can I have the subtraction "follow" the numbers to show weight lost
each week? :

A1 B1 Formula
196 194 2

next week would be

A1 B1 C1 Formula
196 194 190 4

One way:
=OFFSET($A$1,0,COUNTA($A$1:$J$1)-2)-OFFSET($A$1,0,COUNTA($A$1:$J$1)-1)

Adjust to suit.