Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Page 2 of 2 FirstFirst 12
Results 11 to 13 of 13

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

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

    Default

    I agree with yogi.. for differentiating between 2.1 and 2.10.

    actually yogi we in civil engineer spell like

    2 foot 1 inch.. i.e 2.1

    2 foot 10 inch l.e 2.10

    now this something unavoidable in practical use.

    because computer treats 2.1 as 2 and 10 same way it treats 2.10 as 2 and 10

    well this some times creats a problem for me in designs and estimates.

    well i tried to find out the lenght of the value in a cell..say if it is general or text formatted

    2.1 will return 3 i.e the lenght of the string
    2.10 will return 4.

    now this somehow does not work in vba code i tried to us string functions also and then decided people should enter 2.10 .. 2.01 etc.
    ni****h desai
    nisht@pexcel.com
    http://www.pexcel.com

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

    Default

    Friend,

    I have able to solve your problem..

    here function fractionalinch can due the convertion on numberic values.. it will convert

    2.1 to 2.83, 2.2 to 2.17 etc


    if you write

    2.01 then to 2.83, 2.02 to 2.17 etc..

    so it depends on you..

    my adive to you is to give entry as

    2 foot 1 inch as 2.01 ....
    2 foot 2 inch as 2.02
    '
    '
    2 foot 10 inch as 2.10

    ; this is the correct way to give entry...

    okay ..

    now to your solution.. see another function...fractionalpart.

    now this is the thing what exactly you wanted.

    all you need to do is to format your cells to text and then use this function.

    Function fractionalinch(x As Single) As Single
    Dim sngfrac As Single
    Dim sngvalue As Single


    sngvalue = Fix(x)
    x = (x - sngvalue) * 100
    sngfrac = x
    If sngfrac <= 12 Then
    sngfrac = sngfrac
    Else
    sngfrac = sngfrac / 10
    End If

    fractionalinch = Round((sngfrac / 12), 2)
    fractionalinch = sngvalue + fractionalinch
    End Function

    Function fractionalpart(x As Range)
    Dim strright As String
    Dim strleft As String

    If x.NumberFormat <> "@" Then
    Exit Function
    End If

    strleft = Left(x, InStr(x, ".") - 1)
    strright = Right(x, Len(x) - InStr(x, "."))
    fractionalpart = CSng(strleft) + Round(CSng(strright) / 12, 2)
    End Function


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


  3. #13
    MrExcel MVP Jay Petrulis's Avatar
    Join Date
    Mar 2002
    Location
    Chicago, IL USA
    Posts
    2,040
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi All,

    Numerous threads have been done on this topic, but there is no way to distinguish between 2.1 and 2.10 unless the cells are formatted as text. If numeric formatting is required, 2.1 must go to 2.01 for this to work properly.

    If these are text values, most of these will work.

    Yogi, your formula is correct for the values (with the usual caveats already discussed) except for 2.0 (or 2). Then, it it gives erroneous results.

    Bye,
    Jay

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
  •