Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Page 1 of 2 12 LastLast
Results 1 to 10 of 13

Thread: Converting to fractional inches (follow-up Q)

  1. #1
    New Member
    Join Date
    Mar 2002
    Posts
    10
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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 dks 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

  2. #2
    Board Regular
    Join Date
    Feb 2002
    Location
    Calgary, Alberta Canada
    Posts
    3,424
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default

    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 ]

  3. #3
    Board Regular
    Join Date
    Mar 2002
    Posts
    64
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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")

  4. #4
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Sydney, Australia
    Posts
    2,940
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

    Code:
    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

  5. #5
    Board Regular
    Join Date
    Feb 2002
    Location
    Ahmedabad Gujarat
    Posts
    303
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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.

    ni****h desai
    http://www.pexcel.com



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

  6. #6
    Board Regular
    Join Date
    Feb 2002
    Location
    Calgary, Alberta Canada
    Posts
    3,424
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default


    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.


  7. #7
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Sydney, Australia
    Posts
    2,940
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default

    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.


  8. #8
    MrExcel MVP lenze's Avatar
    Join Date
    Feb 2002
    Location
    Helena, MT
    Posts
    13,690
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  9. #9
    New Member
    Join Date
    Mar 2002
    Posts
    10
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

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

  10. #10
    MrExcel MVP
    Join Date
    Mar 2002
    Location
    Michigan USA
    Posts
    11,454
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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 ]

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •