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

N

natasha_tracy

New Member
Joined
Aug 20, 2007
Messages
49
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.
 
Sort by date Sort by votes
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.
 
Upvote 0
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 ?
Click to expand...

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.
Click to expand...

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
 
Upvote 0
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.
Click to expand...
 
Upvote 0
Send me a pm, with your e-mail, and I will send you a sample sheet
Click to expand...

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.
 
Upvote 0
You must log in or register to reply here.

Similar threads

S
  • Question Question
Optimizing Price with Weightings to get to Best Price Mix - Use Solver?
Replies
1
Views
360
StephenCrump
S
C
  • Solved
Formula assistance - identifying full or part counts
Replies
5
Views
348
RobP
R
H
  • Solved
Index and match formula that ignores dynamic value
2
Replies
14
Views
1K
FormR
FormR
Z
  • Solved
Formula required please
Replies
3
Views
91
AhoyNC
A
S
  • Question Question
Formula for Equity Vesting
Replies
6
Views
2K
Dave Patton
D

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top