Combining decimal and imperial?

[Weaselwithagun]

Hi all,

Does anyone know how i'd go about converting a decimal weight (stone then lb's), into exact pounds?

I.E. the cell says the weight is 11.12 stone - if i multiply this by 14 i get 155.68, but it should really be 166.32 (as there are 14 lb's in a stone so although scales would say 11.12 via decimal it'd be 11.88).

Is there a clever way of letting the lay person enter what the scales say, then in the background converting this to the correct number so that it can be calculated into whole lbs?

...I'm not sure I've explained this very well?

Thanks.

[Fluff13]

You can use
=DOLLARDE(A2,14)*14

By my calculation 11 stone 12 pounds is 166 pounds not 166.32

[Sam Capricci]

have you tried =CONVERT(11.12,"stone","lbm") ?

[Fluff13]

@Sambo kid
That will result in 155.68 as it treats the number as a decimal.

[Sam Capricci]

@Fluff13, I see what you are saying, I didn't catch that subtlety in the first post, now I see what Weaselwithagun meant, thanks for the clarification.

[Weaselwithagun]

You can use
=DOLLARDE(A2,14)*14

By my calculation 11 stone 12 pounds is 166 pounds not 166.32

I've either misunderstood this, or it doesn't work?

if you change the number from 11.12 to 10.6 you should end up with about 146, but it comes back to 200?

[XLent]

yes, so you need to address the fact that 6 lb is .06 -- with .6 DOLLARDE will treat as (10 + 60/14) i.e. 14.29 * 14 --> 200

so, either correct the entry (to use .06), or adjust the value in the DOLLARDE -- of course you need to consider what 10.1 means if you leave as-is...

[davsth]

you have to format the entry as text if you wish to have 11.10 being treated differently to 11.1
=LEFT(A1,SEARCH(".",A1,1)-1)*14+RIGHT(A1,LEN(A1)-SEARCH(".",A1,1))

is the inelegant solution, but copes with 11.6

[Sam Capricci]

if not solved yet, this will get you close to it though will not match the decimal...
=CONVERT(LEFT(A2,2),"stone","lbm")+RIGHT(A2,SEARCH(".",A2)-1)

[Fluff13]

As has been said, it would be best to ensure users enter anything less the 10lbs as .0? otherwise you are in a world of pain.

[Weaselwithagun]

yes, so you need to address the fact that 6 lb is .06 -- with .6 DOLLARDE will treat as (10 + 60/14) i.e. 14.29 * 14 --> 200

so, either correct the entry (to use .06), or adjust the value in the DOLLARDE -- of course you need to consider what 10.1 means if you leave as-is...

This makes perfect sense - i should've realised the decimal problem - thanks for everyone's help

[Fluff13]

You're welcome & thanks for the feedback

[torachan]

Use a small aid (userform)
Click anywhere in column A ( from A2 downwards )
torachan.