Thanks Thanks:  0
Likes Likes:  0
Page 1 of 2 12 LastLast
Results 1 to 10 of 20

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

  1. #1
    New Member
    Join Date
    Mar 2014
    Posts
    7
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default 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. #2
    Board Regular
    Join Date
    Jun 2002
    Posts
    2,128
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default 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. #3
    Board Regular
    Join Date
    Feb 2010
    Location
    Wisconsin
    Posts
    2,350
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    Quote Originally Posted by ChrisM View Post
    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. #4
    Board Regular Stridhan's Avatar
    Join Date
    Mar 2014
    Location
    Sweden
    Posts
    568
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default 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. #5
    Board Regular Stridhan's Avatar
    Join Date
    Mar 2014
    Location
    Sweden
    Posts
    568
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default 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. #6
    New Member
    Join Date
    Mar 2014
    Posts
    7
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default 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. #7
    Board Regular Stridhan's Avatar
    Join Date
    Mar 2014
    Location
    Sweden
    Posts
    568
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default 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. #8
    New Member
    Join Date
    Mar 2014
    Posts
    7
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default 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. #9
    Board Regular Stridhan's Avatar
    Join Date
    Mar 2014
    Location
    Sweden
    Posts
    568
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    Try
    Code:
    =INDIRECT(ADDRESS(b9,COUNTA(b9:m9)))-b9
    Last edited by Stridhan; Mar 14th, 2014 at 03:48 AM.

  10. #10
    Board Regular Stridhan's Avatar
    Join Date
    Mar 2014
    Location
    Sweden
    Posts
    568
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

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




Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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