Kgs to lbs to Stones and pounds (UK)

Yes, this is easy once you have the two numbers separated — you just join them together with the & symbol.

If your Stones number is in one cell (say C2) and your Pounds number is in another (say D2), put this in the display cell:

=C2&" st "&D2&" lbs"

So with your numbers that gives you exactly what you wanted: 15 st 12 lbs.

If you would rather do the whole thing from the kg value in one formula, assuming the kg is in A2, this is the version I use in Excel:

=INT(CONVERT(A2,"kg","lbm")/14)&" st "&ROUND(MOD(CONVERT(A2,"kg","lbm"),14),0)&" lbs"

How it works: CONVERT turns kg into pounds, INT(.../14) gives the whole stones, and MOD(...,14) gives the leftover pounds, then & joins them into one piece of text with " st " and " lbs" in between.

Two small things worth knowing. First, if the pounds ever round up to 14, you need to carry one over to stones, otherwise you will see something silly like "15 st 14 lbs". Doing it from total pounds, as in the formula above, avoids that. Second, just as a cross-check, 101 kg is actually 222.7 lb rather than 222.2 lb, which works out to 15 st 13 lb — I only noticed because I check mine  when the spreadsheet and the scale disagree.

Hope that gets the sheet working the way you want it.

AustinM64 

Assuming your columns are Column A = Stones and B = Pounds.

A2 = 15

B2 = 12

Put the formula in column C.  =A2&"st "&B2&"lbs."

Result = 15st 12lbs

Two additional points, if I may: 1) how do you ROUND to the nearest half a pound? So, e.g. 13.6 lbs rounded to 13.5 rather than 14, and 2) how do you get the cell to recognise 14lbs as one stone? Thanks.

AustinM64