# Thread: Updating calculations as data expands

1. ## Updating calculations as data expands

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

2. Perhaps in C3 and drag down

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

3. ## help with formula - expanding data

Originally Posted by NoS
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.

4. 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. 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. ## thanks so much

Originally Posted by NoS
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. 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. Originally Posted by bgoree09
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. 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,

10. 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.

Page 1 of 2 1 2 Last

#### Posting Permissions

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