Hi wonderful people,
I'm working on a spreadsheet for a weight loss program where a participant receives a financial reward for every full pound of weight they lose. Not sure if that title is exactly what I'm looking for, but it was a struggle to describe it in a few short words.
I am tracking their current weight in cell B2 and downward with weekly weigh in's. In column E, I am trying to track their weight loss that results in a reward, but am struggling with how to automate this.
For instance, if a participant started at 250.0 (B2) and weighed 249.1 (B3) a week later, they would get no reward because they lost 0.9 (E3) and they have to lose 1.0 to get the reward. Let's say the week after, they are 249.0 (B4), they would then get a reward because they lost a full 1.0 (E4).
So far, reasonably simple. The week after that, let's say their weight returned to 250 (B5), obviously no reward again. The next week though, they've dropped back to 249 (B6), but they can't get a reward for a pound of weight they already were rewarded for.
Essentially, what I'm getting at is it needs to count only weight loss that is a new full pound of weight loss from the original amount. i.e. in my example, the participant started at x.0 so all payouts for this participant would only happen when they hit x.0. Unless this criteria was met, I would want a - displayed; if they gained, -, if they only lost 0.9, -, no change, 0.
For the first instance of the formula (E3), I am using this formula:
=IF(B2-B3>=1,ROUND(B2-B3),"-")
However, I don't know how to take it further in terms of avoiding repeat weight loss etc. Maybe I need a helper column too?
Hopefully this makes sense. Thanks!
I'm working on a spreadsheet for a weight loss program where a participant receives a financial reward for every full pound of weight they lose. Not sure if that title is exactly what I'm looking for, but it was a struggle to describe it in a few short words.
I am tracking their current weight in cell B2 and downward with weekly weigh in's. In column E, I am trying to track their weight loss that results in a reward, but am struggling with how to automate this.
For instance, if a participant started at 250.0 (B2) and weighed 249.1 (B3) a week later, they would get no reward because they lost 0.9 (E3) and they have to lose 1.0 to get the reward. Let's say the week after, they are 249.0 (B4), they would then get a reward because they lost a full 1.0 (E4).
So far, reasonably simple. The week after that, let's say their weight returned to 250 (B5), obviously no reward again. The next week though, they've dropped back to 249 (B6), but they can't get a reward for a pound of weight they already were rewarded for.
Essentially, what I'm getting at is it needs to count only weight loss that is a new full pound of weight loss from the original amount. i.e. in my example, the participant started at x.0 so all payouts for this participant would only happen when they hit x.0. Unless this criteria was met, I would want a - displayed; if they gained, -, if they only lost 0.9, -, no change, 0.
For the first instance of the formula (E3), I am using this formula:
=IF(B2-B3>=1,ROUND(B2-B3),"-")
However, I don't know how to take it further in terms of avoiding repeat weight loss etc. Maybe I need a helper column too?
Hopefully this makes sense. Thanks!
Last edited:
