Help needed with weight loss calculation

[cdpaul]

Im trying to do a simple weight loss sheet that would show the loss/gain since the last weigh in and an overall.

I want to show the results in pounds but enter the data in stones and pounds, ie 13:12 (not sure wot i should put between the 13 and 12)

Something like this:

DATE WEIGHT LOSS/GAIN OA. LOSS/GAIN
31 jan 13:12
06 feb 13:10 (sum=-2) (sum=-2)
13 feb 13:08 (sum=-2) (sum=-4)

I realise this is probably very easy for u guyz but although i think i can do the sum i dont know how to do the stones and pounds part.

Hope ive made sense and some one can help.

Thanx
Paul

 

[SteveO59L]

Suggest you enter the Stones/Pounds in seperate cells

 

[pgc01]

Hi Paul
Welcome to the board

You can enter the weight with the ":" between the stones and pounds and use the time functions to calculate the loss/gain (I used 1 stone = 14 pounds that I found on the web).

In C4:

=(HOUR(B4)*14+MINUTE(B4))-(HOUR(B3)*14+MINUTE(B3))
Copy down

In D4:

=(HOUR(B4)*14+MINUTE(B4))-(HOUR($B$3)*14+MINUTE($B$3))
Copy down

Hope this helps
PGC

Book1
	A	B	C	D	E
1	DATE	WEIGHT	LOSS/GAIN	OA.LOSS/GAIN
2
3	31-Jan	13:12
4	06-Feb	13:10	-2	-2
5	12-Feb	13:08	-2	-4
6	18-Feb	13:12	4	0
7	24-Feb	14:01	3	3
8	02-Mar	13:13	-2	1
9	08-Mar	13:10	-3	-2
10	14-Mar	13:08	-2	-4
11	20-Mar	13:07	-1	-5
12
Sheet3

 

[cdpaul]

Thanx pcg01

Ive done wot u said but where i havent yet inputted any data the sum is coming up with:

Date Weight Loss/Gain Overall Loss/Gain

-190 -195
0 -195
0 -195
0 -195



Can i stop this happening?

Paul

 

[pgc01]

Hi Paul

Yes, you have to add a test if you already entered data in that row.

In C4:

=IF(B4="","",(HOUR(B4)*14+MINUTE(B4))-(HOUR(B3)*14+MINUTE(B3)))

In D4:

=IF(B4="","",(HOUR(B4)*14+MINUTE(B4))-(HOUR($B$3)*14+MINUTE($B$3)))

Copy down

This way the rows that still have no data will display blank cells.

Hope this solves the problem
PGC

Book1.xls
	A	B	C	D	E
1	DATE	WEIGHT	LOSS/GAIN	OA.LOSS/GAIN
2
3	31-Jan	13:12
4	06-Feb	13:13	1	1
5	12-Feb	13:11	-2	-1
6	18-Feb	13:09	-2	-3
7	24-Feb
8	02-Mar
9	08-Mar
10	14-Mar
11	20-Mar
12
Sheet3

 

[barry houdini]

You could also do this by using the DOLLARDE function from Analysis ToolPak. You'd have to input weights as 13.13 (with a decimal point) rather than 13:13 with a colon and then you'd use this formula in C4

=IF(B4="","",(DOLLARDE(B4,14)-DOLLARDE(B3,14))*14)

and this one in D4

=IF(B4="","",(DOLLARDE(B4,14)-DOLLARDE(B$3,14))*14)

 

[cdpaul]

Absolutely brilliant, thank u.