Matthew said:
The formula is pretty simple For an exact 8:13 ratio,
[....] Actual ratio is 0.61518624
Sure! But I understood that you want to go the other way. That is: given
a real number, find a rational number (ratio of two integers) whose real
value is closest.
Is that what you want? (As I asked previously.)
Is there a command that will return the numerator and
denominator for a selected value
To my knowledge, no.
See the wiki page at
http://en.wikipedia.org/wiki/Continued_fraction for a
general algorithm. But you want to limit the denominator to less than 100.
I offered a simple(-minded) UDF that will do exactly what you ask for, as I
understand your requirement. Just change "for d = 1 to 100" to "for d = 1
to 99". (You had mistakenly written: "Anything in the denominator > 100 is
[...] irrelevant" previously. Apparently you meant >=100.)
However, the following algorithm is based on the aforementioned wiki page.
Usage:
=bestFraction(0.61518624)
formatted as Percentage. That returns just the margin of error between
given real number and closest ratio with denominator <= 100.
Or select A1:C1 (any 3 cells in a row), and enter the above as an array
formula[*], formatting A1 as Percentage.
[*] Enter the array formula by pressing ctrl+shift+Enter instead of just
Enter. The formula should appear in the Formula Bar with curly braces
around it, viz. ={formula}. You cannot type the curly braces yourself;
Excel displays them to denote an array formula. If you make a mistake,
select A1:C1, press F2, edit as needed, then press ctrl+shift+Enter.
If these UDFs are not what you want, please explain why not. Are you trying
to solve a different problem than "find the closet rational number to the
real number"?
If the latter, perhaps you can clarify what is "given" v. what you need to
derive.
UDF....
Option Explicit
Function bestFraction(r As Double)
Dim n As Integer, i As Integer, x As Double
Dim t As Double, num As Double, denom As Double
Dim num0 As Double, denom0 As Double, facts(1 To 17) As Double
n = 1
facts(1) = Int(r)
x = r - facts(1)
Do Until x = 0 Or n = 17
'is denom >=3 digits?
num = facts(n)
denom = 1
For i = n - 1 To 1 Step -1
t = num
num = num * facts(i) + denom
denom = t
Next i
If Len(denom & "") >= 3 Then n = n - 1: Exit Do
num0 = num
denom0 = denom
x = 1 / x
n = n + 1
facts(n) = Int(x)
x = x - facts(n)
Loop
'num0/denom0 is rational number closest to input (r)
'with denom0 < 100
'In A1:C1, return:
'A1: margin of error between r and num0/denom0
'B1: "num0:denom0"
'C1: real value of num0/denom0
x = num0 / denom0
bestFraction = Array(x / r - 1, num0 & ":" & denom0, x)
End Function
----- original message -----
Sorry about the delayed response.
The denominator should not go over three significant digits, not be
greater than 100 so anything less than 100 will be fine i.e. 1-99
The formula is pretty simple For an exact 8:13 ratio, after 8 years,
Venus has made 13 revolutions.
Actual ratio is 0.61518624, so after 8 years, Venus has made
8/0.61518624 = 13.004192 revolutions or 13.004192 – 13 = 0.004192 *
360 = 1.5 degrees over the 8 years.
Is there a command that will return the numerator and denominator for
a selected value.. I was looking at the Quotient functions but I don’t
think that will work.
----- previous message -----
A "dumb" way to do that is to write a UDF that tries
all denominators (d) <= 100 and all numerators (n) < d
to find the n/d that is closest to the given real number
(UDF argument).
A less dumb approach based on the same idea.... For all d <= 100, n =
round(d*r,0). So find the pair (n,d) that corresponds to the smallest
abs(round(d*r,0)/d - r), where r is the "real value".
Although this is more elegant to do with a UDF, we can do it in Excel. If
the "real value" is in A1, put the following formula into X1 and copy down
through X100: =ABS(ROUND($A$1*ROW(),0)/ROW()).
Then B1 and C1 can be the rational number numerator (n) and denominator (d),
calculated as: in B1: =ROUND(A1*C1,0); and in C1:
=MATCH(SMALL(X1:X100,1),X1:X100,0).
The difference can be computed by: =B1/C1/A1-1 formatted as Percentage. If
you would like the fraction displayed in a single cell, then: =B1&"/"&C1
I believe we can avoid all of the intermediate cells. But I think the
resulting array formula would be very messy, with lots of duplicate
computation. At that point, I would opt for the following UDF.
Usage: Select B1:C1, enter the array formula =bestFraction(A1), and press
ctrl+shift+Enter. Format C1 as Percentage.
The formula should appear in the Formula Bar with curly braces around it,
viz. ={formula}. You cannot type the curly braces yourself; Excel displays
them to denote an array formula. If you make a mistake, select B1:C1, press
F2, edit as needed, then press ctrl+shift+Enter.
UDF....
Option Explicit
Function bestFraction(r As Double)
Dim d As Integer, n As Double, e As Double
Dim dM As Integer, nM As Double, eM As Double
eM = 1E+300
For d = 1 To 100
'use n = WorksheetFunction.Round(d * r, 0)
'if you have qualms about VBA's banker's rounding
n = Round(d * r, 0)
e = Abs(n / d - r)
If e < eM Then nM = n: dM = d: eM = e
Next
'return A1:B1 with:
'A1 = fraction as text
'B1 = percentage difference
bestFraction = Array(nM & "/" & dM, nM / dM / r - 1)
End Function
----- original message -----
