I know it’s a complicated formula which some lovely people have already created and shared, but I just can’t quite get my head round adding to it to show ounces too. Can anyone help me elaborate on this to include the ounces please?
Thank you in advance, you clever, lovely people 😊
INT(CONVERT(B2,"kg","stone"))&"st "&ROUND(CONVERT(CONVERT(B2,"kg","stone")−INT(CONVERT(B2,"kg","stone")),"stone","lb"),0)&"lb"
Here's a way to do it with the CONVERT function:
=INT(CONVERT(B2,"kg","stone"))&" st " & INT(MOD(CONVERT(B2,"kg","lbm"),14))&" lb " & ROUND(MOD(CONVERT(B2,"kg","ozm"),16),2)&" oz"
Convert B2 to stone. INT to remove any fraction.
Convert B2 to lb then mod with 14 (14 lb to a stone) to get the pounds that don't make a full stone. INT to remove any fraction.
Convert to B2 to oz then mod with 16 (16 oz to a pound) to get the ounces that don't make a full pound. Round to 2 decimals, or use INT if that accuracy is not required.
Here's a way to do it with the CONVERT function:
=INT(CONVERT(B2,"kg","stone"))&" st " & INT(MOD(CONVERT(B2,"kg","lbm"),14))&" lb " & ROUND(MOD(CONVERT(B2,"kg","ozm"),16),2)&" oz"
Convert B2 to stone. INT to remove any fraction.
Convert B2 to lb then mod with 14 (14 lb to a stone) to get the pounds that don't make a full stone. INT to remove any fraction.
Convert to B2 to oz then mod with 16 (16 oz to a pound) to get the ounces that don't make a full pound. Round to 2 decimals, or use INT if that accuracy is not required.
I haven't figured out how to use the CONVERT function for that.
However, if you look for solutions in Excel, then this may give the best results:
INT(ROUND(A1*35.274,0)/224)&" st "&INT(MOD(ROUND(A1*35.274,0),224)/16)&" lb "&MOD(ROUND(A1*35.274,0),16)&" oz"
The basic idea is to convert first to the smallest unit (ounces). The ROUND(A1*35.274,0) in the formula does that.
Then you derive the other units from that.
There is a detailed explanation here.
SG
Etymology is interesting. Knowing the source of words and how they morphed into what they are today gives them much more meaning. People speak words as if they are mere words but so many have meanings/definitions built into them.
Better.
SG
Thank you ☺️
Thank you both. I am in awe of your abilities ☺️ Maths is not at all my forte. (Now, if you wanted the etymology of the word forte, that’s more my scene ⚔️!)
Converting Kg to Stones, Pounds and Ounces