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.

 

[barry houdini]

Here's a way you could use to take away 1 pound 4 ounces in another set of columns

Book1
	A	B	C	D	E	F	G	H	I
1	Actual					Revised
2	Date	Stones	Pounds	Ounces		Stones	Pounds	Ounces
3	Start Weight	10	11	0		10	9	12
4	28/08/2007	10	8	4		10	7	0
5	29/08/2007	10	8	4		10	7	0
6	30/08/2007	10	7	0		10	5	12
7
Sheet1

formula in F3 copied down

=IF(COUNT(B3:D3)=3,B3-(C3*16+D3<20),"")

formula in G3 copied down

=IF(COUNT(B3:D3)=3,MOD(C3-1-(D3<4),14),"")

formula in H3 copied down

=IF(COUNT(B3:D3)=3,MOD(D3-4,16),"")

I've "hard-coded" the 1 pound 4 ounces but you could make it more flexible by putting these values in cells to reference if you wish

 

[wisewood]

Right...

Assuming Row 1 is your headers, and data starts from row 2.

Put your date, stones, pounds, ounces into row2 and the following formulae into the cells below.

Stones: =IF(C3=13, B2-1, B2)
Pounds: =IF(C2>0, C2-1, 14-1)
Ounces: =IF(D2>3, D2-4, (15+D2)-4)

Hope this is what you were after.

 

[natasha_tracy]

Hello Wookie,

Thanks for that - I can't get it to work though when I paste it into Excel - I retype the = sign and Excel has a problem with the formula and I'm guessing the syntax. As I don't understand the formula, I have no idea how to put it right...

Thanks for the help though - that would certainly do it, if I could get it to work -

 

[ADAMC]

This is untidy but if your not changing the values except the first value of the week? Are we getting close?

Book1
	A	B	C	D
1	STONE	POUNDS	OUNCES
2	10	11	10	10Stones11Pounds10Ounces
3	10	11	6	10Stones11Pounds6Ounces
4	10	10	2	10Stones10Pounds2Ounces
5	10	9	-2	10Stones8Pounds14Ounces
6	10	8	-6	10Stones7Pounds10Ounces
7	10	7	-10	10Stones6Pounds6Ounces
8	10	6	-14	10Stones5Pounds2Ounces
9	10	5	-18	10Stones3Pounds14Ounces
10	10	4	-22	10Stones2Pounds10Ounces
11	10	3	-26	10Stones1Pounds6Ounces
Sheet1

 

[natasha_tracy]

Hi everyone - thank you so much - and yes ADAMC - that's great - this one really works Brilliant! Thank you all. That works great for my 1 pound 4 oz issue. J

Just need to work out how to calculate weight lost - I think I can figure it out from the same formula as ADAMC though.

 

[ADAMC]

My post isnt perfect infact its awful and quite untidy...i actually posted the wrong screenshot but you can probably grasp the concept...........

if you want to calculate total weight loss for a week......
If weight lost is always 1lb 4oz every day for 5 days, wouldnt the weight lost always be the same...or am i missing something again.?

 

[natasha_tracy]

My post isnt perfect infact its awful and quite untidy...i actually posted the wrong screenshot but you can probably grasp the concept...........

if you want to calculate total weight loss for a week......
If weight lost is always 1lb 4oz every day for 5 days, wouldnt the weight lost always be the same...or am i missing something again.?

Hi - no weight loss isn't alway 1lb 4oz - that is something else -

The final thing I wanted to calculate, was - how much weight is lost, or gained since the original start weight, for example 10 stone, 11 pounds, 0 ounces. So, if the next day, the weight is 10 st, 10 lb, 14 oz, then weight lost would be 1lb, 2 oz. If on the following day the weight is 10, 9lb, 11 oz, then total weight loss would then be 2lb, 5 oz from the start weight. (I think I've done my maths right).

What I want is a formula that uses the start weight and the most recently entered weight to then calculate weight lost, or gained (as the case may be) in a separate place. What I really want, is for Excel to pick up the most recent entry automatically without me having to change the formula - not sure if that's a bit ambitious though.

 

[ADAMC]

If i understand correctly my formula might help....you can set it to a range instead of a cell:

=SUM(A2:A5,INT(SUM(B2:B5)/14))&" Stones "&MOD(SUM(B2:B5,INT(SUM(C2:C5)/16)),14)&" Pounds "& MOD(SUM(C2:C5),16)&" Ounces"
Stones is now A2-A5
POUNDS IN B2-B5
OUNCES IS C2-C5

It will sum as you add: IF you only add whether it was a plus or a minus....? (I think) play around with it:

Book1
	A	B	C	D
1	STONE		OUNCES
2	5	1	1	3Stones13Pounds10Ounces
3	1	1	0
4	-2	-1	-2
5	-1	-1	-5
Sheet1

See how i added the increases to keep track of the total weight so far...it could then be added or subtracted from the original weight....Im off for the day now but if you need more help post back and see what we can do!

 

[wisewood]

Just out of interest - why chart weight loss in ounces as well as pounds, that seems a bit extreme to me. After all, an inch of hair all over your head could add a few ounces.

 

[barry houdini]

I'm not sure if this would fit in with the deduction of 1 lb 4 oz but it might help.....[revised]

Book1
	A	B	C	D	E	F	G	H	I
1	Actual				181	Current Weight		Loss
2	Date	Stones	Pounds	Ounces		Stones	Pounds	Ounces
3	Start Weight	10	11	0		0	11	5
4	28/08/2007	10	8	4
5	29/08/2007	10	8	4
6	30/08/2007	10	7	0
7	31/08/2007	10	0	11
8	01/09/2007	10	2	6
9	02/09/2007	9	13	11
10
Sheet1

This will automatically give the difference between the last set of values shown and those in row 3.

Formula in E1 shows the total number of ounces lost or gained. This is used for the other formulas but perhaps you can hide this or custom format cell as ;;;

Formula in E1

=(B3*14+C3)*16+D3-((LOOKUP(99,B:B)*14+LOOKUP(14,C:C))*16+LOOKUP(16,D:D))

formula in H1

=IF(E1>=0,"Loss","Gain")

formula in F3

=INT(ABS(E1)/224)

formula in G3

=INT((ABS(E1)-F3*224)/16)

formula in H3

=ABS(E1)-(F3*14+G3)*16