Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Cell formatting in excel
How do I format to show lb & stones eg 24lb = 1st 10lb
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Cell formatting in excel
You can't do it by cell formatting.
=INT(A1/14)&"st "&MOD(A1,14)&"lb" -- David Biddulph "Odyssey 62" wrote in message ... How do I format to show lb & stones eg 24lb = 1st 10lb |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Cell formatting in excel
Hi,
I'm not sure about a format but with a number of pounds in A1 try this. =INT(A1*0.0714286)&" St " &INT((A1*0.0714286-INT(A1*0.0714286))/0.0714286)&" lbs" It will start losing accuracy at around 2500000 pounds. Mike "Odyssey 62" wrote: How do I format to show lb & stones eg 24lb = 1st 10lb |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Cell formatting in excel
Dividing by 14 is better than muliplying by a rough approximation to the
reciprocal. And for the remainder after the division, MOD(A1,14) is easier than INT((A1*0.0714286-INT(A1*0.0714286))/0.0714286) -- David Biddulph "Mike H" wrote in message ... Hi, I'm not sure about a format but with a number of pounds in A1 try this. =INT(A1*0.0714286)&" St " &INT((A1*0.0714286-INT(A1*0.0714286))/0.0714286)&" lbs" It will start losing accuracy at around 2500000 pounds. Mike "Odyssey 62" wrote: How do I format to show lb & stones eg 24lb = 1st 10lb |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Cell formatting in excel
David,
When I saw your answer i couldn't believe I'd made such a simple thing so complicated. Thanks. Mike "David Biddulph" wrote: Dividing by 14 is better than muliplying by a rough approximation to the reciprocal. And for the remainder after the division, MOD(A1,14) is easier than INT((A1*0.0714286-INT(A1*0.0714286))/0.0714286) -- David Biddulph "Mike H" wrote in message ... Hi, I'm not sure about a format but with a number of pounds in A1 try this. =INT(A1*0.0714286)&" St " &INT((A1*0.0714286-INT(A1*0.0714286))/0.0714286)&" lbs" It will start losing accuracy at around 2500000 pounds. Mike "Odyssey 62" wrote: How do I format to show lb & stones eg 24lb = 1st 10lb |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel cell Formatting | Excel Worksheet Functions | |||
Excel 2003 cell formatting | Setting up and Configuration of Excel | |||
MS Excel Cell Formatting | Excel Worksheet Functions | |||
Formatting a cell crashes Excel | Setting up and Configuration of Excel | |||
help with excel cell formatting | Excel Discussion (Misc queries) |