Formula converting kilos to stone - some odd results

The formula that appears to work for me is:

=QUOTIENT(ROUND(B2÷6.3503×14,0),14)&"st. "&MOD(ROUND(B2÷6.3503×14,0),14)&"lb."

Here's how I would perform the conversion. Once you understand this you can combine as need to consolidate into a single cell if you want:

enter the weight in kg in cell A2

B2=A2×2.20462262

this is shorthand for... select cell B2, then type (or copy and paste from here) the formula:

=A2×2.20462262

C2=INT(B2÷14)

D2=B2−C2×14

David,

I come at this a bit differently. Not that there's anything wrong with the Quotient, Mod and Round approach, but it does require that I open the function browser and refresh my memory on the syntax, so I avoid that.

Also, if you wish to control the number of digits displayed in your concatenated outcome, you will need to use interposing columns to set up the format of the values. For this example, I have set 1 place after the decimal for pounds. I wasn't sure from your comments what you wanted there, aside from consistency.

The exact, by declaration, conversion from kg to stones is 6.35029318:1. I use 6.35:1.because we're usually talking about weighing people, not molecules, when we use stones.

Here's my solution:

You may hide the two interposing columns, C and D in this case.

The expressions are...

Column C (Stones): =INT(B÷6.35)

Column D (Pounds): =CONVERT(B÷6.35−C, "kg", "lbm")

Column E (Text): =C&"st "&D&"lb"

Regards,

Jerry

David,

I think, in your first "oddity," you meant to type 82.5 kg, since 85.5 kg results in 13 st. 6 lb.

82.5 kg is about 12.99 st. so the QUOTIENT part of the formula results in 12 and .99 st. is more than 13.5 lb. so it rounds up to 14.

I think you probably want to round the conversion to the nearest pound before you do the QUOTIENT and MOD calculation:

=QUOTIENT(ROUND(B2*2.20462262,0),14)&" st."&MOD(ROUND(B2*2.20462262,0),14)&" lb."

The second "oddity" seems odd to me as well; I don't get that result.

Jeff

If I format the "kg" cell as "automatic", I get integer results for stones and pounds. If I format it as "number" with 1 decimal place, I get one decimal place displayed for both stones and pounds. So I think your cell with "75" in it must be formatted as a number with one decimal place while your other cells are formatted as automatic.

Here is one that is agnostic to the formatting of your "kg" cells. It doesn't care if those cells are formatted as numbers with decimal places (i.e., it will solve your "75kg" oddity). I don't know why this works but it seems to.

=VALUE(QUOTIENT(ROUND(B2÷6.3503×14,0),14))&"st. "&VALUE(MOD(ROUND(B2÷6.3503×14,0),14))&"lb."

No problem about the "solved" checkmark. I don't count my points, I'm here to help and learn (however, they may be the metric for the short list of "Top Participants in Community" and I am probably in danger of slipping off of that list).

You could give him another "Helpful".🙂

Jerry