Page 1 of 2 1 2 LastLast
Results 1 to 10 of 17

Thread: Updating calculations as data expands

  1. #1

    Updating calculations as data expands



    Register for a FREE account, and/
    or Log in to avoid these ads!

    Hi, I am making a spreadsheet for my daughter's fitness clients. We are tracking weight/etc. Each month the weight/etc. changes. I can easily calculate each difference, but we want to have a formula to automatically update the TOTAL LOSS/GAIN from the beginning assessment. I have done much looking online, but have gotten confused with INDEX and VLOOKUP, etc. Please help. Thank you.
    TOTAL LOSS/GAIN
    Date 1-1-11 formula needed here to calculate diff from last assessment to first assessment 2-1-11 3-1-11 4-1-11
    weight 220 210 205 200
    waist 42 41 40 39
    hips 49 48 47 46
    Last edited by kathy1521; 2013-09-30 at 12:19 AM. Reason: error

  2. #2
    Magician NoS's Avatar
    Join Date
    Jan 2013
    Location
    British Columbia
    Posts
    675
    Articles
    0
    Excel Version
    Excel 2010 64bit
    Perhaps in C3 and drag down

    =($B3-OFFSET($C3,0,MATCH(9.9E+307,$D3:$Z3,1))) *(-1)

  3. #3

    help with formula - expanding data

    Quote Originally Posted by NoS View Post
    Perhaps in C3 and drag down

    =($B3-OFFSET($C3,0,MATCH(9.9E+307,$D3:$Z3,1))) *(-1)
    Thank you so much. It does work!! I am so excited!! However, I may end up with more than "D-Z" columns needed. How can I account for an indefinite number of columns such as AA, AB, etc....? Thank you again! I have added the spreadsheet to let you see what I am doing.
    Attached Files Attached Files
    Last edited by kathy1521; 2013-09-30 at 03:38 AM. Reason: adding spreadsheet

  4. #4
    Magician NoS's Avatar
    Join Date
    Jan 2013
    Location
    British Columbia
    Posts
    675
    Articles
    0
    Excel Version
    Excel 2010 64bit
    kathy1521, column Z was only used as an example, just change it to AZ or anything past what you expect to use for columns.

    I suspect someone will chime in on this with a solution using INDEX because of OFFSET being a volatile function so stay tuned for other suggestions.

    Thanks for including an example file, that always makes it way easier to see and understand what a poster is really after and what they are starting with.

  5. #5
    As promised, here's an index-match . In C11, of course. This will assume that there is an entry on every day (no blanks - or they won't be counted).

    =INDEX($E11:$ZZ11,1,COUNTIF($E11:$ZZ11,">"&0))-$B11

    Best of luck,

  6. #6

    thanks so much

    Quote Originally Posted by NoS View Post
    kathy1521, column Z was only used as an example, just change it to AZ or anything past what you expect to use for columns.

    I suspect someone will chime in on this with a solution using INDEX because of OFFSET being a volatile function so stay tuned for other suggestions.

    Thanks for including an example file, that always makes it way easier to see and understand what a poster is really after and what they are starting with.
    Thanks so much for the help. And it was so easy after you helped!! Yes, I just changed the Z to ZZ - figured if she went that far she would need a real database! Now I am working on the percentage of loss/gain...thank you again!

  7. #7
    Thanks so much for the help. I realize I need more help. I want to make it foolproof for someone else to enter data. I also don't want a spreadsheet filled with 0's waiting on data because then it can be pages long and I don't want to print all those pages if there is no real data entered there. I think I need to write macros to complete the next step if data is entered into a cell. I am a newbie at macros and get scared with adding a formula to it. For example, I would type in weight as ie. 200, then I want the BMI to be completed automatically using the weight data I just added in the above cell. Could someone help me figure that out? Many thanks!Then I would want to use the same type of macro but with other formulas as there are other cells that would use formulas also. Again, many thanks!

  8. #8
    Quote Originally Posted by bgoree09 View Post
    As promised, here's an index-match . In C11, of course. This will assume that there is an entry on every day (no blanks - or they won't be counted).

    =INDEX($E11:$ZZ11,1,COUNTIF($E11:$ZZ11,">"&0))-$B11

    Best of luck,
    Thanks so much. I did try it once and it didn't quite work. I will try again!

  9. #9
    Hello again,

    I'm not sure I understand completely, but I've made a couple modifications to the sheet from earlier. I used my formula in C11 - not familiar with offset, but it looks useful so I'm going to play with it . The way I usually handle autofilling formulas on forms like this is to have them return blank when they aren't needed (in this case, when BMI = 0 it means that there is not a weight input.). You can drag this formula out infinitely and it will just return blanks until someone puts in a weight.

    As for the VBA part, someone else will be much more apt to help. I'm self taught and lack a lot of the tools others possess .

    Hope this helps,
    Attached Files Attached Files

  10. #10
    Magician NoS's Avatar
    Join Date
    Jan 2013
    Location
    British Columbia
    Posts
    675
    Articles
    0
    Excel Version
    Excel 2010 64bit
    Hi Kathy1521,

    I don't think you are needing VBA or macros to handle what you need. As bgoree09's posted file shows using =IF formulas will eliminate all those zeros from showing and =IFERROR can be used to eliminate the #NUM! s and both allow for automatic updating when info is added.

    The only thing that would concern me is the use of COUNTIF in the INDEX formula. I don't know how your daughter's business works, but to me it seems that missing information (ie: blank cells) for a month or so and then new info for the following month would not be out of the question so I would use MATCH. If that is a likelyhood here is bgoree09's formula for C11 using MATCH

    =INDEX($E11:$ZZ11,1,MATCH(9.9E+307,$E11:$ZZ11,1))-$B11


    While trying to drag down formulas, no doubt you are also learning the evils of merged cells, it's better to use Format Cells -> Alignment -> Horizontal -> Center Across Selection.

    Good luck with your project.

Page 1 of 2 1 2 LastLast

Posting Permissions

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