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?
