Formula or Macro

Discuss the spreadsheet application

Formula or Macro

Postby JAR » Wed Jan 23, 2008 5:01 pm

Hi All,

I need to convert millimeters into feet and inches and round down to the nearest inch. For example: 2500 mm = 8'-2". I would like the cell to acually display the ' and ".

A bit stuck on this one.

Thanks!

Jim
JAR
 
Posts: 2
Joined: Wed Jan 23, 2008 4:30 pm

Re: Formula or Macro

Postby JohnV » Wed Jan 23, 2008 5:55 pm

This appears to work for Inches to Feet and Inches.
=INT(A1/12)&"' - "&MOD(A1;12)&""""
I don't know the divisor for centimeters.
JohnV
Volunteer
 
Posts: 1585
Joined: Mon Oct 08, 2007 1:32 am
Location: Kentucky, USA

Re: Formula or Macro

Postby TerryE » Wed Jan 23, 2008 6:02 pm

You can use custom format codes to add units so if A1=2500 then with a cell formula =A1/304.8format of #.0 "Ft" will display 8.2 Ft

However you can't work base 12 and you can't infix so you need a dog of a formula:
    =INT(ROUND(A1/25.4)/12)&"'-"&MOD(ROUND(A1/25.4);12)&""""
This will display 8'-2". Note that you put two " characters inside a string to get one displayed.

You need to do the explicit round first because you want 2' 11.888" to display as 3' 0" not 2' 0".
Ubuntu 11.04-x64 + LibreOffice 3 and MS free except the boss's Notebook which runs XP + OOo 3.3.
TerryE
Volunteer
 
Posts: 1402
Joined: Sat Oct 06, 2007 10:13 pm
Location: UK

Re: Formula or Macro

Postby Villeroy » Wed Jan 23, 2008 6:08 pm

A1: value in mm (positive or negative)

[Edit: the ft-portion needs to be cut, not rounded]
B1: =CONVERT_ADD(A1/1000;"m";"ft")
C1: =INT(ABS(B1))
D1: =(ABS(B1)-C1)
E1: =CONVERT_ADD(D1;"ft";"in")
F1: =TEXT(C1*SIGN(B1);"0'")&"-"&TEXT(E1;"0""")
Last edited by Villeroy on Wed Jan 23, 2008 6:32 pm, edited 2 times in total.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, no OpenOffice, LibreOffice 6.4
User avatar
Villeroy
Volunteer
 
Posts: 28846
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Formula or Macro

Postby JAR » Wed Jan 23, 2008 6:25 pm

You need to do the explicit round first because you want 2' 11.888" to display as 3' 0" not 2' 0".

Actually 2' 11.888" I need to display as 2'-11"

So this should work ????

=INT(ROUND(A1/25.4)/12)&"'-"&MOD(ROUND(A1/25.4);12)&""""

Thanks!
JAR
 
Posts: 2
Joined: Wed Jan 23, 2008 4:30 pm

Re: Formula or Macro

Postby huw » Wed Jan 23, 2008 6:44 pm

What about CONVERT_ADD()? Or is that StarOffice specific?
huw
Volunteer
 
Posts: 417
Joined: Wed Nov 21, 2007 1:57 pm

Re: Formula or Macro

Postby TerryE » Wed Jan 23, 2008 8:01 pm

JAR wrote:Actually 2' 11.888" I need to display as 2'-11"
Bugger, I missed the round down bit. As long as the values are positive then JohnV's formula is more like it: =INT(A1/304.8)&"'-"&MOD(INT(A1/25.4);12)&""""
Ubuntu 11.04-x64 + LibreOffice 3 and MS free except the boss's Notebook which runs XP + OOo 3.3.
TerryE
Volunteer
 
Posts: 1402
Joined: Sat Oct 06, 2007 10:13 pm
Location: UK


Return to Calc

Who is online

Users browsing this forum: No registered users and 27 guests