Converting Kg to UK Stone & pounds

[GoHoSo]

Hello!

Can’t find a formula that works to change weight from Kgs to UK stones & pounds.

Have found and used but it doesn’t return the expected result
=ROUND(CONVERT(B25,”kg”,”like”)/14,0)&”st”&MOD(ROUND(CONVERT(B25,”kg”,”lbm”),0),14)&”lb”

The answer to 14.3kg should be 2st 3lb but it gives 2st 10lbs.

Please can anyone help?

[Bo_Ry]

Plaese try
=SUBSTITUTE(TEXT(DOLLARFR(CONVERT(B25,"kg","stone"),14)*100, "0""st ""00""lbs.""")," 0"," ")

3.52 lbs round up to 4

or round down
=INT(CONVERT(B25,"kg","stone"))&"st "&INT(MOD(CONVERT(B25,"kg","lbm"),14))&"lbs."

[Greg M]

Hi there,

For a kilogram value entered in Cell A1 the following formula is a bit clunky and could possibly be streamlined, but it seems to give the result you want:

Please Login or Register  to view this content.

Hope this helps.

Regards,

Greg M

[GoHoSo]

Both of these gave a #N/A answer - not sure what I'm doing wrong

[GoHoSo]

Hi Greg, thank you! But I can't get this formula to work... it gives me #N/A answer

[davsth]

We are probably down to how you have entered your data and we cant see!

Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

[Greg M]

Hi again,

The attached workbook uses the formula I posted earlier and seems to work.

Hope this helps,

Regards,

Greg M

[Richard Buttrey]

Hi

Another option

Please Login or Register  to view this content.

The results is stones : pounds.decimal