New Excel Forum

This forum has been moved to TeachExcel.com

Ask all future questions in the New Excel Forum.

ExcelKey

Convert Fractions To Decimals

Free Excel Macros

Convert Fractions To Decimals

Postby Sisyphus » Sun May 27, 2012 11:01 pm

As a companion to the function ReducedFraction, published on this forum, that converts decimals to fractions I offer the following function which does the exact opposite:-
Code: Select all
Option Explicit

Public Function FractionToDecimal(ByVal Num As String) As Variant
   
    Dim Fd As Variant
    Dim S() As String
    Dim Dn As Double
   
    S = Split(Num)
    If LBound(S) = UBound(S) Then
        Fd = Num
    Else
        Fd = Val(S(LBound(S)))
        S = Split(S(UBound(S)), "/")
        If UBound(S) > LBound(S) Then
            Dn = Val(S(UBound(S)))
            If Dn = 0 Then Dn = 1
            Fd = Val(S(LBound(S))) / Dn * Sgn(Fd) + Fd
        End If
    End If
    FractionToDecimal = Fd
End Function
Whereas ReducedFraction is limited in its scope to fractions commonly used to express measurements in inches where the denominator is a power of 2, the above function has no such limitations. However, caution is advised when using both functions to juggle fractions back and forth between their formats. ReducedFraction does round the original decimal to the nearest defined highest denominator so that the reversed conversion won't necessarily be the same as the original decimal. For this reason it is advisable to do all calculations in the original format and convert only the total.

The following function demonstrates how to call FractionToDecimal:
Code: Select all
Private Sub test()
    Debug.Print FractionToDecimal("13 3/4")
End Sub
Observe that the parameter is declared as String.
Have a great day! :D

Sisyphus
I do this for "honour and country" - much less of the latter, actually.
If I helped you, award points, plenty of them.
If I bored you, deduct points for being too long-winded. (I know, :lol)
Sisyphus
Former Moderator
 
Posts: 4454
Joined: Dec 7, 2011
Location: Shanghai
Reputation: 203
Excel Version: 2010

Return to Excel Macros

Who is online

Users browsing this forum: No registered users and 27 guests