Converting kilograms to stones and pounds

I'm weak on stones and pounds and such but I think this does what you want.

The formula in B2, filled down:

=INT(CONVERT(A2,"kg","stone")) & "st " & ROUND(CONVERT(CONVERT(A2,"kg","stone")−INT(CONVERT(A2,"kg","stone")),"stone","l b"),0)& "lb"

This looks complicated because it's all in one cell, but all it does is convert from kg to stone, then take the integer part of the result for the number of stone and converts the decimal part into lb and rounds that.

Highly recommend having a look at the CONVERT function, described here.

SG

David Gordon wrote:

the result given for 72.2 kg was 11.0st 5.0lb. Decimals included, exactly the result I get from my original formula.

Here's what I get.

The formula I have in B2 is:

=INT(CONVERT(A2,"kg","stone")) & "st " & ROUND(CONVERT(CONVERT(A2,"kg","stone")−INT(CONVERT(A2,"kg","stone")),"stone","l b"),0)& "lb"

Cell A2 is formatted like this:

Leave the Decimals at 'Auto'. Maybe that will work with your original formulas as well.

SG

Why would you want to?

kilos is neat and simple.

The mere exercise in how fiddly it is to work out archaic measures shows why they are such a bad idea and why just about nobody uses them.

Irregardless, the principle would be:

1. convert the kilos to pounds = X lbs

2. X/14 and round down = Y stones

3. X- (Yx14) = Z lbs*

To make life simpler for you do each of those calculations in separate columns and concatenate the result in a 4th column.

NB Not working out the ounces/dram/grain? 😀

To help you with the simple process of determining your weight:

Hi David,

The usual advice in this forum is to let Numbers do its job without using ROUND at every step in a formula.

Let's start with converting pounds (lbs) to pounds and stones.

Column A is entered data.

Column B has the formula in B2 (and Fill Down) =A2÷14

Column C has this formula =INT(B2)

Column D has this formula =A2−C2×14

Column E has this formula =C2&" Stones "&D2&" lbs"

Once we get that working, we can insert a new Column A (kilos) and convert kilos to pounds in the new column B

Hmm... more work needed to ROUND the "Pounds left over" Column.

I shall work on this and reply tomorrow.

Regards,

Ian.

Hi David,

I used a multiplier of 2.24 which (I believe) is kilograms to Imperial (British) pounds.

Perhaps you prefer US pounds, so multiply by 2.2046226

(The CONVERT function in Numbers and the Convert app in Launchpad > Dashboard both use 2.2046226)

Formula in B2 (and Fill Down) =A2×2.24

Formula in C2 =B2÷14

Formula in D2 =INT(C2)

Formula in E2 =B2−D2×14

Formula in F2 =ROUND(E2,0)

Formula in G2 =D2&" Stones "&F2&" lbs"

I am sure those formulas can be combined into a single, complex formula, but those are the steps.

Regards,

Ian.