Weight Chart again - please help - this is driving me mad

[natasha_tracy]

Hello all,

This is a query relating to the Weight chart I have been trying to do - I'm not sure if anyone has any answers, but people stopped replying to my last topic on this, I think because it was too long to read -

I have a Weight Chart which allows me to input a person's weight each day in Stones, pounds and ounces. With the help from the experts on this site, I have managed to get the spreadsheet to tell me how much weight has been lost to date from the starting weight.

However, what I would like to do now is to calculate how much weight is left to lose based on a target weight. I have been trying to work this out for days and it isn't quite working and I can't for the life of me work out why - below is what I have done:


E3 = Target weight (stones)
F3 = Target weight (pounds)
G3 = Target weight (ounces)
E4:E4: is current weight (stones)
F4:F4: is current weight (pounds)
G4:G4: is current weight (ounces)

M6=(E3*14+F3)*16+G3

M7=(LOOKUP(99,E:E)*14+LOOKUP(14,F:F))*16+LOOKUP(16,G:G)

N3=INT((ABS(M7)-M6)/224)

O3==IF(INT((ABS(M7)+N3/224-M6)/16)=14, 0, INT((ABS(M7)+N3/224-M6)/16))

P3=IF(INT((ABS(M7)+(N3*14-O3)-M6)/16)=16, 0,INT((ABS(M7)+(N3*14-O3)-M6)/16))


This seems to work for the stones and pounds, but the ounces aren't working - but this could be because I haven't got the formulas correct for stones and pounds. Can anyone help?

That said- I would welcome any help that would give me the answer I want, even if it is nothing like what I have above.

 

[SteveO59L]

Just a thought, but would it not be easier to do in Kg ?

 

[natasha_tracy]

I've thought about this already - yes it would and it wouldn't - the people that I'm doing this for do not understand Kg - it doesn't mean anything to them - old school. Also, I've managed to get everything else working, which has taken me ages and it just seems silly to give up now, when it's probably the smallest change to the formula that will make it work.

 

[natasha_tracy]

Surely there is someone here amongst this forum of Excel experts that can help me??

Just a thought, but would it not be easier to do in Kg ?

I've thought about this already - yes it would and it wouldn't - the people that I'm doing this for do not understand Kg - it doesn't mean anything to them - old school. Also, I've managed to get everything else working, which has taken me ages and it just seems silly to give up now, when it's probably the smallest change to the formula that will make it work.

If it is possible to do it in Kg and then convert it to imperial, the I'm up for that... It's become a challenge to sort this now - I can't bear not knowing how to do it and I've tried everything

 

[SteveO59L]

Send me a pm, with your e-mail, and I will send you a sample sheet

 

[natasha_tracy]

Send me a pm, with your e-mail, and I will send you a sample sheet

I've PM'ed you - thank you so much.

 

[tusharm]

Re: Weight Chart again - please help - this is driving me ma

I don't know why you are using LOOKUP, but if you have the weight in Stones, Pounds, and Ounces in E3, F3, and G3, your formula to convert to ounces is correct.

You can apply that to both the target weight and the current weight to get both in ounces.

Subtract the target weight from the current weight to calculate the "weight to lose" in ounces.

To convert the weight in ounces to stones, pounds, and ounces, use the following and untested formulas:

Ounces =MOD(Total_ounces, 16)
Total_Pounds =INT(Total_ounces / 16)
Pounds =MOD(Total_Pounds, 14)
Stones =INT(Total_Pounds / 14)

Hello all,

This is a query relating to the Weight chart I have been trying to do - I'm not sure if anyone has any answers, but people stopped replying to my last topic on this, I think because it was too long to read -

I have a Weight Chart which allows me to input a person's weight each day in Stones, pounds and ounces. With the help from the experts on this site, I have managed to get the spreadsheet to tell me how much weight has been lost to date from the starting weight.

However, what I would like to do now is to calculate how much weight is left to lose based on a target weight. I have been trying to work this out for days and it isn't quite working and I can't for the life of me work out why - below is what I have done:


E3 = Target weight (stones)
F3 = Target weight (pounds)
G3 = Target weight (ounces)
E4:E4: is current weight (stones)
F4:F4: is current weight (pounds)
G4:G4: is current weight (ounces)

M6=(E3*14+F3)*16+G3

M7=(LOOKUP(99,E:E)*14+LOOKUP(14,F:F))*16+LOOKUP(16,G:G)

N3=INT((ABS(M7)-M6)/224)

O3==IF(INT((ABS(M7)+N3/224-M6)/16)=14, 0, INT((ABS(M7)+N3/224-M6)/16))

P3=IF(INT((ABS(M7)+(N3*14-O3)-M6)/16)=16, 0,INT((ABS(M7)+(N3*14-O3)-M6)/16))


This seems to work for the stones and pounds, but the ounces aren't working - but this could be because I haven't got the formulas correct for stones and pounds. Can anyone help?

That said- I would welcome any help that would give me the answer I want, even if it is nothing like what I have above.

 

[natasha_tracy]

Send me a pm, with your e-mail, and I will send you a sample sheet

Thank you, thank you, thank you SteveO59L. It's now working.

I made a couple of changes to the formulas, to include lookup so that it takes the most recent entry in the weight chart - that way the weight can be tracked over time, without having to delete it and enter the new weight each week.

It's all working though, so thank you.

Over and Out - at least for the weight thing.