Updating calculations as data expands

K

kathy1521

New member
Joined
Sep 29, 2013
Messages
11
Reaction score
0
Points
0
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-114-1-11
weight
220
210
205200
waist
42
41
4039
hips
49
48
4746
 
Last edited:
Perhaps in C3 and drag down

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

NoS said:
Perhaps in C3 and drag down

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

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.
 

Attachments

  • DRAFT - assessment.xlsx
    14.8 KB · Views: 20
Last edited:
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.
 
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

NoS said:
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.
Click to expand...

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!
 
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!
 
bgoree09 said:
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,
Click to expand...

Thanks so much. I did try it once and it didn't quite work. I will try again!
 
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 :smile:. 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 :smile:.

Hope this helps,
 

Attachments

  • DRAFT - assessment_sample.xlsx
    15.4 KB · Views: 15
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.
 
Thanks so very much for all of your help. I think the spreadsheet is a go. Your help was very much appreciated!!
 
Hello Kathy1521
I agree with NoS about the Macros and use of VBA. You will need a template sheet in order to construct a control document for a new client. Put all your static data (eg row/column headings), formatting into it together with the formulae to do your statistics.
You might be able to use a formula to calculate the dates if they conform to some kind of predictable pattern.
If your concerned about lots of zeros, firstly decide on a cell that can be used to activate the formulae (e.g. when its <> blank). Then place your working formulae inside an IF statement. We might decide that the formulae should "sleep" until a clients weight is entered in Cell B11. So using NoS's formula we can do this with:
=IF(B11= "", "", INDEX($E11:$ZZ11,1,MATCH(9.9E+307,$E11:$ZZ11,1))-$B11).

Im not sure how you intend to design your data storage, but you could use the template idea for individual client sheets, or multiple blocks of client records in a single sheet. With the latter, having established a dataset for one client, dare I say that you could record a macro to build a group template by multiple copy and paste of the data rows?

Good luck

Hercules
 
Hercules1946 said:
Hello Kathy1521
I agree with NoS about the Macros and use of VBA. You will need a template sheet in order to construct a control document for a new client. Put all your static data (eg row/column headings), formatting into it together with the formulae to do your statistics.
You might be able to use a formula to calculate the dates if they conform to some kind of predictable pattern.
If your concerned about lots of zeros, firstly decide on a cell that can be used to activate the formulae (e.g. when its <> blank). Then place your working formulae inside an IF statement. We might decide that the formulae should "sleep" until a clients weight is entered in Cell B11. So using NoS's formula we can do this with:
=IF(B11= "", "", INDEX($E11:$ZZ11,1,MATCH(9.9E+307,$E11:$ZZ11,1))-$B11).

Im not sure how you intend to design your data storage, but you could use the template idea for individual client sheets, or multiple blocks of client records in a single sheet. With the latter, having established a dataset for one client, dare I say that you could record a macro to build a group template by multiple copy and paste of the data rows?

Good luck

Hercules
Click to expand...
Thank you Hercules for the new formula. It lets the formula sleep until new data is input. It worked in 2 formulas. Yeah! However, I am trying to do the same "sleeping" formula in 2 areas that have an array. Flex test 'best trial' is one example ----(E35,E36,E37). If there is no input in E35,E46,E37 then I don't need a MAX(E35:E37). I hope that makes sense. Flex test 1 = 8, Flex 2 =9, Flex 3 = 10 so the MAX would be Flex 3 and 10 would be automatically entered in E38.
 
attached adjusted spreadsheet

View attachment fitness - help 10-1-13.xlsx I think I have attached my spreadsheet which I have tweaked a little as well as using formulas for 3 areas. I need help with a formula that will sleep in 2 rows --- "body fat % row" and "best flex" row. thanks so much
 
Hi Kathy
On Row 13, add an IFERROR function in Cell E13 as shown in red below. This tells Excel to override the default response if your formula encounters an error condition. Then copy E13 across the other Cells on the row.

=IFERROR((495/(1.1714-0.063*(LOG(E23+E24+E25+E26))-0.000406*E7)-450)/100, "")

On Row 38, change your first IF condition to = 0 (not = "", which it will never be with a numeric calculation)
=IF(MAX(H$35:H$37)= 0,"",MAX(H$35:H$37))

Hercules
 
Hercules, you are terrific! Thanks so much!! It does look like it's working. My daughter will be thrilled. I am because I was trying to figure it out for her. It looks so easy when it works! The IF and IFERROR formulas confuse me. Again, thank you!!
 
If you look at the help available on formulae, you'll find IF and IFERROR explained in some detail, with examples you can play with.

glad to be of help, and thanks for the other contributions.

Bgoree09 - Dont let anyone Knock self taught - I learn't that way as well :smile:

Hercules
 
You must log in or register to reply here.
Back
Top