Help needed with weight loss calculation

S

SteveO59L

Well-known Member

Joined

Apr 21, 2004

Messages

7,899

• Feb 13, 2007

• #2

Suggest you enter the Stones/Pounds in seperate cells

 

Upvote 0

P

pgc01

MrExcel MVP

Joined

Apr 25, 2006

Messages

19,897

• Feb 13, 2007

• #3

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

 

Upvote 0

C

cdpaul

New Member

Joined

Sep 2, 2005

Messages

41

Office Version

1. 365

Platform

1. Windows

• Feb 13, 2007

• #4

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

 

Upvote 0

P

pgc01

MrExcel MVP

Joined

Apr 25, 2006

Messages

19,897

• Feb 13, 2007

• #5

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

 

Upvote 0

barry houdini

MrExcel MVP

Joined

Mar 23, 2005

Messages

20,825

• Feb 13, 2007

• #6

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)

 

Upvote 0

C

cdpaul

New Member

Joined

Sep 2, 2005

Messages

41

Office Version

1. 365

Platform

1. Windows

• Feb 13, 2007

• #7

Absolutely brilliant, thank u.

 

Upvote 0