Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
adjusting sums
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! |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
adjusting sums
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 "ericaamousseau" wrote in message ... 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! |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
adjusting sums
ericaamousseau wrote:
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. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
adjusting sums
Will give incorrect result if a person has a weight gain instead of a
loss. Alas, it happens to the best of us! John wrote: 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 "ericaamousseau" wrote in message ... 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! |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
adjusting sums
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 "smartin" wrote in message ... Will give incorrect result if a person has a weight gain instead of a loss. Alas, it happens to the best of us! John wrote: 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 "ericaamousseau" wrote in message ... 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! |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
adjusting sums
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 "ericaamousseau" wrote: 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! |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
adjusting sums
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Adjusting columns | Excel Discussion (Misc queries) | |||
Adjusting series | Charts and Charting in Excel | |||
Adjusting Macro | Excel Discussion (Misc queries) | |||
How to add previous sums in a column to current sums in a column? | Excel Worksheet Functions | |||
Adjusting Formula | Excel Worksheet Functions |