Weight chart Please see page 3 for final query

[natasha_tracy]

Hello everyone -

I am trying to create a weight chart, which works in stones, pounds and ounces. My first query is that each time I enter the weight in for each day, I need it to deduct 1ib 4oz from the weight I enter. At present I have seperate columns for stones, pounds and ounces. However, if I try to do a simple lbs minus 4 it doesn't work, because sometimes the pounds are less than four - so instead of taking the stone column down by one and then deducting the remaining pounds, it just puts the pounds columns to -3 or similar. The same applies to the oz columns - good old imperial eh!

Also, I would like my table to work out weight lost over a period of time - so, I have my starting weight at the top and then I want my most recent weight to be taken from the starting weight, leaving weight lost - I can't get this to work either. Is there a way to make it so that the calculation uses the most recent weight entry and does so automatically?

If anyone has any solutions to these queries, I would be very grateful...as I have to caculate this for a number of people and it would be much quicker if it did it all automatically upon entering the most recent weight.

 

[natasha_tracy]

Hello everyone - thank you so much and thanks to barry houdini for that last solution - that was exactly what I wanted - brilliant - you people are very clever - I am just a mere mortal.

Thank you all.

 

[natasha_tracy]

I have one last query that I'm no determined to solve - I would now like t work out how much weight is left to lose based on the current weight now and a target weight now in cells E3 (stone), F3 (lbs), G3 (oz). I have tried to use a variation on the formulae given on a previous page (which has now gone) and this calculates how much weight has been lost to date. I have altered this formula as follows, and it is working, but the Stone and pounds column is showing a value one too high and the ounces column is showing a value two too high - can anyone see what is wrong with my formula - it doesn't work if I just add a -1 to the end to the formula.

N4 = Stones: =INT((ABS(LOOKUP(99,E:E)*14+LOOKUP(14,F:F))*16+LOOKUP(16,G:G)-(E3*14+F3)*16+G3)/224)

O4 = Pounds: =INT((ABS(LOOKUP(99,E:E)*14+LOOKUP(14,F:F))*16+LOOKUP(16,G:G)+N4/224-(E3*14+F3)*16+G3)/16)

P4: Ounces: =INT((ABS(LOOKUP(99,E:E)*14+LOOKUP(14,F:F))*16+LOOKUP(16,G:G)-(N4*14-O4)-(E3*14+F3)*16+G3)/16)

So in columns E, F and G starting at row four, I'm entering the weight, stones, pounds and ounces - in cells E3, F3, and G3 I have the target weight, in this case 9st, 7lbs 0 oz so I need the sheet to calculate based on the most recent weight entry, how much is left to lost until the target of 9st, 7lbs 0 oz.

 

[natasha_tracy]

If anyone can help me solve the problem above, it would be much appreciated. I've been looking at it for hours and can't understand why it doesn't work and why it adds on one to the stones and pounds and two to the ounces.

 

[natasha_tracy]

Hi, I am still trying to work out, based on a target weight, how much more weight from my most recent entry I still have to lose to reach the target weight -

I have since tried something else, which is working better, but not 100% correct...

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. Can anyone help?

 

[barry houdini]

Hello natasha, sorry I didn't respond sooner but I haven't been around much recently.

Based on your last post, assuming you have the formulas shown for M6 and M7 and in N3, O3 and P3 you want to show the amount still left to lose to reach the target weight (if target weight has been achieved these will show 0,0,0)

In N3

=IF(M7<=M6,0,INT((M7-M6)/224))

in O3

=IF(M7<=M6,0,INT((M7-M6-N3*224)/16))

and in P3

=IF(M7<=M6,0,M7-M6-(N3*14+O3)*16)

 

[tusharm]

Duplicated in http://www.mrexcel.com/board2/viewtopic.php?t=290930

Hi, I am still trying to work out, based on a target weight, how much more weight from my most recent entry I still have to lose to reach the target weight -

I have since tried something else, which is working better, but not 100% correct...

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. Can anyone help?