Forum Discussion
Weight loss in lbs. and body fat %
Hi,
I am tracking weight and body fat % bi-weekly. I was wondering if there Is a way to update the total weight loss and body fat loss based on the updated data entered bi-weekly.
| 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 | -2.4 | -1.44 |
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.
10 Replies
serene06 To update your total weight loss, just subtract your starting weight from your latest weight entry. For body fat loss, subtract your initial body fat percentage from the current one. If you're looking for extra support, you could try checking out a medical weight loss service like https://medicalweightlosslehighvalley.com/. They offer personalized guidance to help you reach your goals.
serene06 alternatively I would suggest you redo your sheet into columns.
Date Weight (lbs) Body Fat(%) Weight Loss Body Fat% Change Current Weight Loss: -2.7 (negative means weight gain) start 166.4 35.1 0 0 Current Body Fat % decrease: 3 (negative means body fat gain) W2 168.8 31.3 -2.4 3.8 W4 169.1 32.1 -2.7 3 The attached is prettier but basically this will let you 'track' your weight better. If you use actual dates in that 1st column you could track avg weight loss / day. If you want you can easily make a graph of either your weight over time or your weight loss/change over time (or 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.
