Forum Discussion
Weight loss in lbs. and body fat %
serene06 In the attached I expanded the file/table to include ID and week and formatted as a table to use structured references. The formulas find the value for the row with the same ID AND says "start" under the Week and subtract from there. I do this all under 1 table as it does make a number of things easier (i.e. any kind of group metrics like my clients lost a total of or average of ...).
i also added another tab to help you pull ONLY a single person's info. I used a pivot table but you could also use the FILTER() functions.
=$A$3-INDEX($C$3:$J$3,LARGE(IF((C3:J3<>"")*(MOD(COLUMN(C:J),2))=1,COLUMN(C:J))-2,1))=100-($B$3/INDEX($C$3:$J$3,LARGE(IF((C3:J3<>"")*(MOD(COLUMN(C:J),2)=0),COLUMN(C:J))-2,1))*100)Maybe with this formulas. Enter the formulas with ctrl+shift+enter if you don't work with Office365 or 2021.
OliverScheurich, Thx for the quick response! I copied both formulas in my spreadsheet but did not get a value. The cell only displays the formula. The columns are different in my spreadsheet due to other captured data. The data starts in column D, row 5 and ends in column M, row 5.
| Start Weight (lbs) | Body Fat (%) | W1 (lbs) | BF1 (%) | W2 (lbs) | BF2 (%) | W3 (lbs) | BF3 (%) | W4 (lbs) | BF4 (%) | Total Weight Loss/Gain (lbs) | Total Body Fat Loss/Gain (%) |
| 166.4 | 35.1 | 168.8 | 31.3 | {=$D$5-INDEX($F$5:$M$5,LARGE(IF((F5:M5<>"")*(MOD(COLUMN(F:M),2))=1,COLUMN(F:M))-2,1))} | {=100-($E$3/INDEX($F$3:$M$3,LARGE(IF((F3:M3<>"")*(MOD(COLUMN(F:M),2)=0),COLUMN(F:M))-2,1))*100)} |
Unfortunately i didn't understand where the data is located in your sheet. You can try the updated attached file with data in range D5:M5.
Can you tell me how to upload the file since I do not see the paper clip?
I can attach files this way:
Reply -> Open full text editor -> Browse
Otherwise it could be possible to open your profile and to select Messages and then to upload a file and to write a message.