Formatting for Weights pounds & ounces

N

nc

Can anyone help. I am trying to track my weight on an excel spreadsheet, but when I enter my weight is it possible to format it in stones and pounds. As when I want to subtract one weight from another it doesn't recognise that there are only 14 llb in stone so the calculations don't work out ie. 12st less 2lb should be 11.12 but I can't work out how to get this result.

Any help with this will be appreciated.
 
M

Mark Graesser

nc,
If you enter your weight in the form of 6.02, which equals 6 stones and 2 pounds, then you can use the following formula:

=(INT(((INT(A1)*14+(A1-INT(A1))*100)-(INT(B1)*14+(B1-INT(B1))*100))/14))+(((((INT(A1)*14+(A1-INT(A1))*100)-(INT(B1)*14+(B1-INT(B1))*100))/14)-INT(((INT(A1)*14+(A1-INT(A1))*100)-(INT(B1)*14+(B1-INT(B1))*100))/14))*14)/100

This will work for weights entered in A1 and A2.

Basically this converts the numbers completly into pounds, subtracts, and reconverts to stones and pounds.

I am working on a user defined function to shorten this mess. I will repost if I come up with something.

Good Luck,
Mark Graesser
(e-mail address removed)

----- nc wrote: -----

Can anyone help. I am trying to track my weight on an excel spreadsheet, but when I enter my weight is it possible to format it in stones and pounds. As when I want to subtract one weight from another it doesn't recognise that there are only 14 llb in stone so the calculations don't work out ie. 12st less 2lb should be 11.12 but I can't work out how to get this result.

Any help with this will be appreciated.
 
N

Norman Harker

Hi nc!

You don't say how you are entering your weight.

If entering in lbs:

=INT(A1/14)+MOD(A1,14)/100
166 returns: 11.12
Interpreted as 11 stone 12 lbs
156 returns: 11.02


--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top