Formula that updates a weight change in one cell based on monthly updates

ktraina

New Member
Joined
Mar 13, 2014
Messages
7
I have 12 columns each named for a month of the year. In the row below, a trainer inputs a client's weight in the cells associated with the month they take the measurement. At the end of that row, I have a column titled "Monthly Change" where I have created a formula that takes the current month's weight and subtracts it from the previous month's weight….giving us their progress. Is there a way I can add to that formula to update that particular cell as the months progress without having to change the formula each month? I only want one cell that updates at the end of the row rather than having 12 separate cells that change with the updates.

Thanks so much!
 

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).
The easiest way is to add a admin field like "Month #", which would be a number 1 to 12.

Now use the INDEX formula to count over by that number of months to get the current weight, and then count over one less to get the prior weight

Assuming you put the month # in Z1:

=If(Z1 = 1, 0, INDEX(A2:L2,,Z1) - INDEX(A2:L2,,Z1-1))
 
Upvote 0
without any helper but with some more typing..
assume, Jan, Feb and so on in column A, B and so on..
Weights in row 2,
Code:
=choose(counta(A2:L2),"",A2-B2,B2-C2,C2-D2,D2-E2,E2-F2,F2-G2,G2-H2,H2-I2,I2-J2,J2-K2,K2-L2)

If a more effient solution for the subtracting in my formula, please reply :)
 
Upvote 0
Change the cell formatting in sumcolumn aswell, type "-"0.0;"+"0.0 in the custom format ( for more or less decimals, just add more in the numbers )

Will show the loss as for example, 100-99= -1.0
 
Upvote 0
Ok, this is what I've got…..I didn't know I could actually post pictures of the spreadsheet or I would have done so earlier.

For the monthly change for February, there is a loss of 3 pounds using the formula =[@Column3]-[@Column2]:

image.tiff


SO….using my current spreadsheet, I would have to change the formula manually to read =[@Column4]-[@Column3] and so on as we went down the months.

image.tiff


I'm looking for the formula that I could put in the "O9" cell that would automatically update when i filled in April, May, June…..etc.

Does that make more sense? I'm a basic Excel user with a small grasp of formulas so thanks for your patience!!!

kt
 
Upvote 0
copy mine, change letters, instead of A and up, change to B and up. Allso change the 2 to a 9 in all places
switch the columns while at it :)
paste in O9

Once made it can be copied and then paste special, as formula, then the ranges changes to what ever row u put it in.
 
Upvote 0
That was perfect!!! Thanks so much! One additional question….If I wanted to have a column "P" that kept track of the Total Change, what might that formula look like? I have been playing with the numbers for the last ½ hour but at this point, my two year old has informed me that she is no longer interested in watching me play on the computer and we actually need to go outside and throw a ball around! Thanks thanks thanks!!!
 
Upvote 0

Forum statistics

Threads
1,213,506
Messages
6,114,024
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top