I wrote:
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 -----

"Joe User" joeu2004 wrote in message
...
"Matthew" wrote:
0.61518624 = Current real value = 1,922,457 / 3,125,000
0.615384615 = Exactly 8/13 two significant denominator digits.
0.032% = difference express as a percentage.
Anything in the denominator 100 is statistical irrelevant
how can i get an excel cell to do this automatically?

Of course, Excel cannot do any computation "automatically". I think you
are asking how to write formulas to do what you need.

The question is: what exactly do you need?

So that everyone does not have to find and try to fathom
http://en.wikipedia.org/wiki/Orbital_resonance, perhaps you can explain
how you would do the above computation manually -- i.e. determining that
8/13 is the closest rational number to the real number that you computed,
within the parameters that your specific (namely, denominator = 100).

Or did you just plunk that example from wiki page, and you have no idea
yourself?

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). As bad as that might sound, it should
be a very fast computation on modern computers.

But is that what you are looking for: the closest rational number to the
real number?

(The percentage difference between the two is then a trivial Excel
formula.)


----- original message -----


"Matthew" wrote in message
...
In a nut shell I am looking to display the margin of error from a
perfect fraction. So I can determine if a orbit is is too close to
being in perfect resonance and hence unstable.

0.61518624 = Current real value = 1,922,457 / 3,125,000
0.615384615 = Exactly 8/13 two significant denominator digits.
0.032% = difference express as a percentage.

Anything in the denominator 100 is statistical irrelevant

how can i get an excel cell to do this automatically?