Thanks:  0
Likes:  0

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

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!

2. ## Re: Formula that updates a weight change in one cell based on monthly updates

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))

3. ## Re: Formula that updates a weight change in one cell based on monthly updates

Originally Posted by ChrisM
Assuming you put the month # in Z1:
To build on ChrisM's solution, you could use this formula in Z1:

=MONTH(TODAY())

That way, the month number will automatically update.

4. ## Re: Formula that updates a weight change in one cell based on monthly updates

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

5. ## Re: Formula that updates a weight change in one cell based on monthly updates

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

6. ## Re: Formula that updates a weight change in one cell based on monthly updates

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]:

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.

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

7. ## Re: Formula that updates a weight change in one cell based on monthly updates

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.

8. ## Re: Formula that updates a weight change in one cell based on monthly updates

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!!!

9. ## Re: Formula that updates a weight change in one cell based on monthly updates

Try
Code:
`=INDIRECT(ADDRESS(b9,COUNTA(b9:m9)))-b9`

10. ## Re: Formula that updates a weight change in one cell based on monthly updates

change to
Code:
`=INDIRECT(ADDRESS(row(),COUNTA(b9:m9)))-b9`

## User Tag List

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•