Converting to fractional inches (follow-up Q)

[StevenData]

Greetings,
I am trying to convert a column of lengths in the numeric format of decimal feet (i.e. 2.1, 2.2,…2.10,12.11). how can I convert this to fractional inches(i.e. 2.08, 2.17,…,2.83,2.92). A great contributor of this forum,dk has suggested using the following macro, which beautifully works for all values except those containing the fractions .10 and .11(i.e. 2.10, 2.11). could you please help me to make dk’s macro work for all valid values.

sub convertfeetandinches()
dim cl as range
for each cl in selection.cells
if isnumeric(cl.value) then
cl.value=format(int(cl.value)+(cl.value - int(cl.value))*10/12,"#,##0.00")
end if
next
end sub
Sincerely, Steve

[Dave Patton]

I am not a programmer and I did not see the original question. You can consider the following:

2.01 2.0833 1/12 0.083
2.02 2.1667 1/6 0.167
2.03 2.2500 1/4 0.250
2.04 2.3333 1/3 0.333
2.05 2.4167 5/12 0.417
2.06 2.5000 1/2 0.500
2.07 2.5833 7/12 0.583
2.08 2.6667 2/3 0.667
2.09 2.7500 3/4 0.750
2.1 2.8333 5/6 0.833
2.11 2.9167 11/12 0.917

revised line

cl.Value = Format(Int(cl.Value) + (cl.Value - Int(cl.Value)) * 100 / 12, "#,##0.0000")

[ This Message was edited by: Dave Patton on 2002-03-30 21:21 ]

[Fryer Tuck]

Your problem is that it is treating the value to the right of the decimal place as decimal format. This is thrown for a loop with 11 and 12 inches.

Try this:
cl.value=format(int(cl.value)+ if(Int(cl.value)<>cl.value,Right(cl.value,Find(".",cl.value)-1)/12,0),"#,##0.00")

[dk]

Hello Steven,

Sorry about the error in my original post. Here is some revised code which produces the following results:-

Before After
2.1 2.08
2.2 2.17
2.3 2.25
2.4 2.33
2.5 2.42
2.6 2.50
2.7 2.58
2.8 2.67
2.9 2.75
2.10 2.83
2.11 2.92
2.12 3.00

Sub ConvertFeetAndInches()
Dim cl As Range
For Each cl In Selection.Cells
    If IsNumeric(cl.Value) Then
        cl.Value = Format(Int(cl.Value) + Right(cl.Value, Len(cl.Value) - InStr(1, cl.Value, ".")) / 12, "#,##0.00")
    End If
Next
End Sub

HTH,
D

[nisht]

See if this function can be useful to you..

this function was written by damon... and i got it from a questioner.

I have added few codes to make it useable for negative values.
Actually this is for converting for inches to foot.
working with inches is always a problem with us on the site.

metric is best system... still we work on inches.

Function FormatFeetInches(r1 As Range, Optional SmallestFrac As Integer = 16) As String

' DecimalIn is the number of inches to be expressed as a whole part and
'fraction thereof
' Format will display the result in string format, reducing the
'Denominator
' to the smallest possible within the constraints of bringing the number
'to the nearest
' sixteenth. The optional parameter SmallestFrac allows you to set the
'largest
' denominator to something other than sixteenths (set to 8 for eights,
'etc.). The resulting
' number will be displayed in the format 2'-7 3/8"

Dim WholePart As Integer
Dim FractPart As Single
Dim Numerator As Integer
Dim Denominator As Integer
Dim Feet As Integer
Dim flag As Boolean

DecimalIn = r1.Value
'Debug.Print R1.Address
If DecimalIn < 0 Then
DecimalIn = Abs(DecimalIn)
flag = True
End If

WholePart = Fix(DecimalIn)
If WholePart < 12 Then
Feet = 0
Else
Feet = WholePart 12
WholePart = WholePart Mod 12
End If
FractPart = DecimalIn - Fix(DecimalIn)
Denominator = SmallestFrac
Numerator = Fix(FractPart * SmallestFrac + 0.5) '0.5 Rounds to nearest
'16 th

' Reduce numerator and denominator by factors of 2
Do Until Numerator / 2 <> Numerator 2 Or Numerator < 2
Denominator = Denominator / 2
Numerator = Numerator / 2
Loop

' Format the result into a string
If Numerator <> 0 Then
FormatFeetInches = CStr(WholePart) & " " & CStr(Numerator) & "/" & CStr(Denominator) & """"
Else
FormatFeetInches = CStr(WholePart) & """"
End If

If Feet <> 0 Then
FormatFeetInches = CStr(Feet) & "'-" & FormatFeetInches
End If

If flag = False Then
FormatFeetInches = FormatFeetInches
Else
'r1.Interior.ColorIndex = 12
FormatFeetInches = "-" & FormatFeetInches
End If
End Function

I hope this can work for you.

nishith desai
http://www.pexcel.com



[ This Message was edited by: nisht on 2002-03-31 02:59 ]

[Dave Patton]

Attention DK

I get the same result with your formula as the formula I quickly edited except I get a different result for 2.10.
Please check the formula with 2.10; both 2.01 and 2.10 give 2.08.

[dk]

Dave,

I think it was established in Steven's earlier post that the cells containing the data would be formatted as text - that is the assumption I've made. If the values are going to be formatted as numbers then the code wouldn't work and I think it could be tricky to do. I guess that you'd need to check the NumberFormat property of the cell and go from there but I'll wait and see what Steven says before doing anything else.

Regards,
Dan.

[lenze]

There's a function in the Analysis Tool Pack that does this. DOLLARDE(A1,12) will convert 2.06 (2 ft 6 inches) to 2.5. DOLLARFR()will convert it back You can uscustom formatting in your cell 00 "ft".00 "in" to make display 2 ft. 06 in when you enter 2.06

[StevenData]

Thank you again for posting thoughtful replies to my post. The format is Numeric.
Sincerely,
Steve

[Yogi Anand]

Hi StevenData:
In response to an earlier posting by you on the same topic, where we had quite a lively discussion, my final post was:

2 ft&in.00 =INT(B21)+RIGHT(B21,2)/12
2 ft&in.01 2.08
2 ft&in.02 2.17
2 ft&in.03 2.25
2 ft&in.04 2.33
2 ft&in.05 2.42
2 ft&in.06 2.50
2 ft&in.07 2.58
2 ft&in.08 2.67
2 ft&in.09 2.75
2 ft&in.10 2.01
2 ft&in.11 2.92
2 ft&in.12 3.00

StevenData, please note that I have used two digit representation for inches -- otherwise you run into problems differentiating between 2.1 and 2.10

Looks like you still do not have an answer to your liking. Please post what is it that does not work for you and then let us take it from there!


_________________
Yogi Anand
Edit: Deleted inactive web site reference from hard code signature line

[ This Message was edited by: Yogi Anand on 2003-01-19 17:41 ]