Answered by:
Having problems Converting to VB.net Decimal to Fractions
Question

Good Day Everyone!
Can someone please help me to convert the following formula:
=TEXT(A1,"0" &IF(ABS(A1ROUND(A1,0))>1/256, " 0/"&CHOOSE(MATCH(MIN(ABS(ROUND(A1*{2,4,8,16,32,64,128},0) ' A1* {2, 4, 8, 16, 32, 64, 128})/{2,4,8,16,32,64,128}),ABS(ROUND(A1*{2,4,8,16,32,64,128},0) ' A1* {2, 4, 8, 16, 32, 64, 128})/{2,4,8,16,32,64,128},0),2,4,8,16,32,64,128),""))
This is works awesome in Excel and converts decimals to simple fractions.
I`m having problems I guess understanding the :
ROUND(A1*{2,4,8,16,32,64,128},0
Multiplying cell value to Array?
Any help appreciated.
Alex
 Edited by Alex20122012 Monday, July 31, 2017 7:07 PM
Answers

Here is another similar to the excel equation.
Public Class Form3 Private Sub Form3_Load(sender As Object, e As EventArgs) Handles MyBase.Load Dim value As Double = 10.563 Label1.Text = value.ToString & " = " & MakeFraction128(value) End Sub Public Function MakeFraction128(length As Double) As String Dim n128 As Long = Math.Abs(CLng(Math.Round(length * 128, 0))) Dim f128 As Long = n128 Mod 128 Dim fraction As String = "", denom As Integer = 128 If f128 > 0 Then Do While f128 Mod 2 = 0 f128 \= 2 denom \= 2 Loop fraction = String.Format("{0}/{1}", f128, denom) End If Dim sign As String = If(length < 0, "", "") MakeFraction128 = sign & Math.Floor(Math.Abs(length)).ToString & " " & fraction End Function End Class
 Edited by tommytwotrain Wednesday, August 2, 2017 10:40 AM
 Marked as answer by Alex20122012 Wednesday, August 2, 2017 1:25 PM
All replies

Good Day Everyone!
Can someone please help me to convert the following formula:
=TEXT(A1,"0" &IF(ABS(A1ROUND(A1,0))>1/256, " 0/"&CHOOSE(MATCH(MIN(ABS(ROUND(A1*{2,4,8,16,32,64,128},0) ' A1* {2, 4, 8, 16, 32, 64, 128})/{2,4,8,16,32,64,128}),ABS(ROUND(A1*{2,4,8,16,32,64,128},0) ' A1* {2, 4, 8, 16, 32, 64, 128})/{2,4,8,16,32,64,128},0),2,4,8,16,32,64,128),""))
This is works awesome in Excel and converts decimals to simple fractions.
I`m having problems I guess understanding the :
ROUND(A1*{2,4,8,16,32,64,128},0
Multiplying cell value to Array?
Any help appreciated.
Alex
Hi
Don't quite know if this is what you want, but,here it is. This example takes a string (of a Double  in TextBox1) and returns the number expressed as a fraction (in TextBox2).
' Form1 with TextBox1, TextBox2 ' and Button1 ' no error checking included Option Strict On Option Explicit On Public Class Form1 Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load ' test fraction TextBox1.Text = "1.25" End Sub Function reduction(s As String) As String Dim lst As New List(Of String) Dim a() As String = Split(s, "/") Dim top As Integer = CInt(a(0)) Dim bott As Integer = CInt(a(1)) For divisor As Integer = 1 To bott If bott Mod divisor = 0 Then If top Mod divisor = 0 Then lst.Add((top \ divisor).ToString & "/" & (bott \ divisor).ToString) End If End If Next Return lst(lst.Count  1).ToString End Function Private Sub Button2_Click(sender As Object, e As EventArgs) Handles Button2.Click Dim s As String = (CDbl(TextBox1.Text)  CInt(Math.Floor(CDbl(TextBox1.Text)))).ToString.Substring(2, (CDbl(TextBox1.Text)  CInt(Math.Floor(CDbl(TextBox1.Text)))).ToString.Length  2) Dim len As Integer = (CDbl(TextBox1.Text)  CInt(Math.Floor(CDbl(TextBox1.Text)))).ToString.Length  2 Dim s1 As String = s & "/1" & StrDup(len, "0") TextBox2.Text = CInt(Math.Floor(CDbl(TextBox1.Text))).ToString & " " & reduction(s1) End Sub End Class
Regards Les, Livingston, Scotland
 Edited by leshay Monday, July 31, 2017 9:41 PM

Can someone please help me to convert the following formula:
Your best procedure is to ask this in an Excel forum to get an description of how the function works, and then convert that description to VB code.
ROUND(A1*{2,4,8,16,32,64,128},0) is equivalent to
{ROUND(A1*2,0),ROUND(A1*4,0),ROUND(A1*8,0),ROUND(A1*16,0),ROUND(A1*32,0),ROUND(A1*64,0),ROUND(A1*128,0)}
and needs to be considered in terms of its use within a MIN function.
 Proposed as answer by KareninstructorMVP, Moderator Tuesday, August 1, 2017 1:39 AM

Hi
Here is a much better version than the example I posted above. This was found HERE the rights belong to Euclid :)
' Form1 with TextBox1, TextBox1 ' and Button1 ' no error checking included Option Strict On Option Explicit On Public Class Form1 Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load ' test fraction TextBox1.Text = "0.001589546" End Sub Private Sub Button2_Click(sender As Object, e As EventArgs) Handles Button2.Click TextBox2.Text = GetFraction(CDbl(TextBox1.Text)) End Sub ''' <summary> ''' Return a fraction string from a double. ''' </summary> ''' <param name="d">The double to convert.</param> ''' <returns>The converted string.</returns> ''' <remarks>Code written by Troy Lundin on May 3, 2007</remarks> Function GetFraction(ByVal d As Double) As String ' Get the initial denominator: 1 * (10 ^ decimal portion length) Dim Denom As Int32 = CInt(1 * (10 ^ d.ToString.Split("."c)(1).Length)) ' Get the initial numerator: integer portion of the number Dim Numer As Int32 = CInt(d.ToString.Split("."c)(1)) ' Use the Euclidean algorithm to find the gcd Dim a As Int32 = Numer Dim b As Int32 = Denom Dim t As Int32 = 0 ' t is a value holder ' Euclidean algorithm While b <> 0 t = b b = a Mod b a = t End While ' Return our answer Return CInt(Math.Floor(d)) & " " & (Numer / a) & " / " & (Denom / a) End Function End Class
Regards Les, Livingston, Scotland
 Edited by leshay Tuesday, August 1, 2017 2:20 PM

Can someone please help me to convert the following formula:
=TEXT(A1,"0" &IF(ABS(A1ROUND(A1,0))>1/256, " 0/"&CHOOSE(MATCH(MIN(ABS(ROUND(A1*{2,4,8,16,32,64,128},0) ' A1* {2, 4, 8, 16, 32, 64, 128})/{2,4,8,16,32,64,128}),ABS(ROUND(A1*{2,4,8,16,32,64,128},0) ' A1* {2, 4, 8, 16, 32, 64, 128})/{2,4,8,16,32,64,128},0),2,4,8,16,32,64,128),""))
Alex
Hi Alex,
I don't clear what you provide here, but about converting decimal to fractions, you can refer to these steps.
Step 1: Write down the decimal divided by 1,
Step 2: Multiply both top and bottom by 10 for every number after the decimal point. (For example, if there are two numbers after the decimal point, then use 100, if there are three then use 1000, etc.)
Step 3: Simplify (or reduce) the fractionBest Regards,
Cherry
MSDN Community Support
Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com. 



If you copy this in Excel in A2 cell as it is and type 0.563 for example in cell A1 you will see how accurate its converts to a simple fraction. I was just looking for an alternative with similar precision in .NET.
The precision in the Excel formula is due to the combined effect of the dividend factors used and the rounding. Once you have broken that formula down into suitable small separate elements it will be possible to recode it in VB, although it will look somewhat different.

Alex,
I saw this earlier this afternoon and initially thought it wouldn't take all that much to replicate  at least in function.
I was wrong  it took a lot!
Mine works differently but hopefully it'll get you where you want. Please do note that I have *NOT* tested it well at all and I won't be amazed if you find a glitch in it (but please do let me know).
The challenge was with the way I chose to round: I chose to do it myself with iteration of increasingly lower values and you choose the precision of that. The higher the precision, the better the result but the longer it'll take to find it.
Try this if you'd care to and let me know how you fare with it:
Option Strict On Option Explicit On Option Infer Off Public Class Form1 Private Sub Form1_Load(sender As System.Object, _ e As System.EventArgs) _ Handles MyBase.Load Dim testValue As Decimal = 0.563D Dim precision As Int32Fraction.PrecisionValue = Int32Fraction.PrecisionValue._10 Dim sw As New Stopwatch sw.Start() Dim testFractions As IEnumerable(Of Int32Fraction) = _ Int32Fraction.GetFractionFromDecimal(testValue, precision) sw.Stop() Dim best As Int32Fraction = testFractions.Last Dim sb As New System.Text.StringBuilder sb.AppendLine("The most precise fraction calculated for a value") sb.AppendLine(String.Format("of {0} (based on the precision parameter", testValue)) sb.Append(String.Format("of {0}) is: ", precision)) sb.Append(best.ToString & ".") MessageBox.Show(sb.ToString, String.Format("Elapsed Time: {0:f2} Seconds", sw.Elapsed.TotalSeconds)) Stop End Sub End Class Public NotInheritable Class Int32Fraction Public Enum PrecisionValue _10 _100 _1000 _10000 _100000 End Enum Private _integral As Integer Private _numerator As Integer Private _denominator As Integer Private Sub New(ByVal numerator As Integer, _ ByVal denominator As Integer) _numerator = numerator _denominator = denominator End Sub Private Sub New(ByVal wholePortion As Integer, _ ByVal numerator As Integer, _ ByVal denominator As Integer) _integral = wholePortion _numerator = numerator _denominator = denominator End Sub Public Shared Function _ GetFractionFromDecimal(ByVal value As Decimal, _ ByVal precision As PrecisionValue, _ ParamArray acceptableDenominators() As Integer) As IEnumerable(Of Int32Fraction) Dim retVal As IEnumerable(Of Int32Fraction) = Nothing If acceptableDenominators.Length = 0 Then acceptableDenominators = New Integer() {2, 4, 8, 16, 32, 64, 128} End If If value <> 0 Then Dim valueIsNegative As Boolean = value < 0 Dim integerPortion As Integer = Math.Abs(CInt(Fix(value))) Dim decimalPortion As Decimal = Math.Abs(value)  integerPortion Dim pow As Integer = GetPowerValue(decimalPortion) Dim num As Integer = CInt(decimalPortion * (10 ^ pow)) Dim den As Integer = CInt(1 * (10 ^ pow)) Dim primes() As Integer = PrimeGenerator.GetPrimes(num) Dim initialFraction As New Int32Fraction(num, den) Dim tempList As New List(Of Int32Fraction) For Each acceptable As Integer In acceptableDenominators Dim temp As Int32Fraction = GetFinalFraction(initialFraction, primes, acceptable, precision) If integerPortion > 0 Then temp = New Int32Fraction(integerPortion, temp.Numerator, temp.Denominator) End If If temp IsNot Nothing Then If valueIsNegative Then temp = New Int32Fraction(temp.Numerator * 1, temp.Denominator) End If End If If temp.Denominator = acceptable Then tempList.Add(temp) End If Next If tempList.Count > 0 Then retVal = tempList.ToArray End If End If Return retVal End Function Public ReadOnly Property Denominator As Integer Get Return _denominator End Get End Property Public ReadOnly Property Integral As Integer Get Return _integral End Get End Property Public ReadOnly Property Numerator As Integer Get Return _numerator End Get End Property Public Overrides Function ToString() As String Return String.Format("{0}{1}/{2}", _integral, _numerator, _denominator) End Function Private Shared Function _ GetPowerValue(ByVal value As Decimal) As Integer Dim retVal As Integer = 0 If value.ToString.Contains("."c) Then Dim tempLng As Long = 0 Do value *= 10 retVal += 1 Dim s As String = value.ToString.Substring(value.ToString.IndexOf("."c) + 1) If Long.TryParse(s, tempLng) Then If tempLng = 0 Then Exit Do End If End If Loop End If Return retVal End Function Private Shared Function _ GetFinalFraction(ByVal fractionToReduce As Int32Fraction, _ ByVal primes() As Integer, _ ByVal divisor As Nullable(Of Integer), _ ByVal precision As PrecisionValue) As Int32Fraction Dim retVal As Int32Fraction = Nothing Dim tempFraction As Int32Fraction = GetReduced(fractionToReduce, primes) If tempFraction Is Nothing Then tempFraction = fractionToReduce End If If divisor.HasValue AndAlso divisor.Value > 1 Then If tempFraction.Denominator = divisor.Value Then retVal = tempFraction Else Dim multiplier As Integer Select Case precision Case PrecisionValue._10 multiplier = 10 Case PrecisionValue._100 multiplier = 100 Case PrecisionValue._1000 multiplier = 1000 Case PrecisionValue._10000 multiplier = 10000 Case PrecisionValue._100000 multiplier = 100000 End Select Dim numerator As Integer = tempFraction.Numerator * multiplier Dim denominator As Integer = tempFraction.Denominator * multiplier Do Until tempFraction.Denominator = divisor.Value numerator = 1 If numerator > 0 Then tempFraction = GetReduced(New Int32Fraction(numerator, denominator), primes) If tempFraction IsNot Nothing AndAlso tempFraction.Denominator = divisor.Value Then retVal = tempFraction Exit Do End If Else retVal = fractionToReduce Exit Do End If Loop End If Else retVal = tempFraction End If Return retVal End Function Private Shared Function _ GetReduced(ByVal fractionToReduce As Int32Fraction, _ ByVal primes() As Integer) As Int32Fraction Dim retVal As Int32Fraction = Nothing If fractionToReduce IsNot Nothing AndAlso _ primes IsNot Nothing AndAlso _ primes.Length > 0 Then For Each i As Integer In primes If fractionToReduce.Numerator Mod i = 0 AndAlso _ fractionToReduce.Denominator Mod i = 0 Then retVal = New Int32Fraction(fractionToReduce.Numerator \ i, _ fractionToReduce.Denominator \ i) End If Next End If If retVal IsNot Nothing Then retVal = GetReduced(retVal, primes) Else retVal = fractionToReduce End If Return retVal End Function End Class ''' <summary> ''' This class is from this thread: ''' http://www.vbforums.com/showthread.php?690129Primenumbergenerator ''' </summary> ''' <remarks></remarks> Public Class PrimeGenerator Private Class DeselectableNumber Public Value As Integer Public bStruckOut As Boolean End Class Public Shared Function GetPrimes(ByVal max As Integer) As Integer() Return FindPrimes(CreateNumberList(max)) End Function Private Shared Function CreateNumberList(ByVal MaxNumber As Integer) As DeselectableNumber() 'Create an array of type DeselectableNumber. One entry for 'every number between 0 to MaxNumber Return Enumerable.Range(0, MaxNumber + 1).Select(Of DeselectableNumber)(Function(n) New DeselectableNumber With {.Value = n, .bStruckOut = False}).ToArray End Function Private Shared Function FindPrimes(ByVal NumList As IList(Of DeselectableNumber)) As Integer() Dim lLastPrime As Integer Dim i As Integer Dim lStruckCount As Integer Dim bRemainArePrime As Boolean 'Reached a point where all remaining numbers are prime Dim lstPrimes As New List(Of Integer) lLastPrime = 2 Do Until (lStruckCount >= NumList.Count  1 Or bRemainArePrime = True) For i = lLastPrime To NumList.Count  1 With NumList.Item(i) If .Value Mod lLastPrime = 0 And .bStruckOut = False Then .bStruckOut = True lStruckCount = lStruckCount + 1 If .Value = lLastPrime Then lstPrimes.Add(lLastPrime) End If End If End With Next For i = lLastPrime To NumList.Count  1 If NumList.Item(i).bStruckOut = False Then lLastPrime = NumList.Item(i).Value : Exit For Next 'If the next multiple of the next prime number is greater than 'our upper limit then all the remaining numbers that were 'not struck our are prime numbers If lLastPrime * 2 > NumList.Count  1 Then bRemainArePrime = True End If Loop 'All remaining unstruck numbers are prime If bRemainArePrime Then For i = 2 To NumList.Count  1 With NumList(i) If .bStruckOut = False Then lstPrimes.Add(.Value) End With Next End If FindPrimes = lstPrimes.ToArray End Function End Class
When the test program gets to "Stop", hover your mouse over the local variable "testFractions" and you'll see there are several to choose from, varying in precision. Since it's an enumerable and is ordered, the .Last one has the highest precision based on the precision setting I chose there.
The .ToString override ("09/16") is based on AIA notation standards but you can see what I have there so change it any way you prefer.
"A problem well stated is a problem half solved.”  Charles F. Kettering
 Edited by Frank L. Smith Tuesday, August 1, 2017 9:54 PM ...typo

Here is another similar to the excel equation.
Public Class Form3 Private Sub Form3_Load(sender As Object, e As EventArgs) Handles MyBase.Load Dim value As Double = 10.563 Label1.Text = value.ToString & " = " & MakeFraction128(value) End Sub Public Function MakeFraction128(length As Double) As String Dim n128 As Long = Math.Abs(CLng(Math.Round(length * 128, 0))) Dim f128 As Long = n128 Mod 128 Dim fraction As String = "", denom As Integer = 128 If f128 > 0 Then Do While f128 Mod 2 = 0 f128 \= 2 denom \= 2 Loop fraction = String.Format("{0}/{1}", f128, denom) End If Dim sign As String = If(length < 0, "", "") MakeFraction128 = sign & Math.Floor(Math.Abs(length)).ToString & " " & fraction End Function End Class
 Edited by tommytwotrain Wednesday, August 2, 2017 10:40 AM
 Marked as answer by Alex20122012 Wednesday, August 2, 2017 1:25 PM

Alex,
If you're interested in using the approach that I showed yesterday, I've reworked it so that it now uses parallelization to improve the speed.
It's more than this (I'll explain afterward), but the following sums up the change from yesterday:
Parallel.ForEach(acceptableDenominators, _ Sub(acceptable) Dim temp As Int32Fraction = GetFinalFraction(initialFraction, primes, acceptable, precision) If integerPortion > 0 Then temp = New Int32Fraction(integerPortion, temp.Numerator, temp.Denominator) End If If temp IsNot Nothing Then If valueIsNegative Then temp = New Int32Fraction(temp.Numerator * 1, temp.Denominator) End If End If If temp.Denominator = acceptable Then tempList.Add(temp) End If End Sub)
Because parallelization means that the order is indeterminate, the class now has this also:
Implements IComparable Implements IComparable(Of Int32Fraction)
The reason that I've done that is so that I can then write a LINQ query against the returned collection to sort them properly:
Dim testFractions As IEnumerable(Of Int32Fraction) = _ Int32Fraction.GetFractionFromDecimal(testValue, precision) Dim qry As System.Linq.IOrderedEnumerable(Of Int32Fraction) = _ From f As Int32Fraction In testFractions _ Order By f
The end result is that it's greatly reduced the time it takes to process everything:
If you're willing to wait, let it continue:
If you want to use this, let me know and I'll move the classes to a file, zip the file up, and upload it for you. That way there's no copy and paste involved.
"A problem well stated is a problem half solved.”  Charles F. Kettering

I know, i started my own conversions and though it`d take less time. once i dive in .... hell of a code ....I can`t believe .net dont have a function like one.
Thank you much for this. Checking it now. 35 sec. is a little bit too much since I have to convert around 50 numbers (for the cut list).
Alex

I know, i started my own conversions and though it`d take less time. once i dive in .... hell of a code ....I can`t believe .net dont have a function like one.
Thank you much for this. Checking it now. 35 sec. is a little bit too much since I have to convert around 50 numbers (for the cut list).
Alex
Please make it obvious who you're talking to; we don't all see this forum the same way.
*****
If it's to me, regarding the second screenshot above, that's 0.35 seconds (about 1/3 second), not 35 seconds.
With a lot of them to do you might want to roll all of that into a background thread.
"A problem well stated is a problem half solved.”  Charles F. Kettering


Alex,
If you're interested in using the approach that I showed yesterday, I've reworked it so that it now uses parallelization to improve the speed.
It's more than this (I'll explain afterward), but the following sums up the change from yesterday:
Thank you Frank! tommytwotrain NAILED IT
Alex