A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Excel » New Users
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Difference between real number and a perfect fractions



 
 
Thread Tools Display Modes
  #1  
Old April 9th, 2010, 12:24 AM posted to microsoft.public.excel.newusers
Matthew[_16_]
external usenet poster
 
Posts: 2
Default Difference between real number and a perfect fractions

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?
  #2  
Old April 9th, 2010, 01:29 AM posted to microsoft.public.excel.newusers
Joe User[_2_]
external usenet poster
 
Posts: 757
Default Difference between real number and a perfect fractions

"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?


  #3  
Old April 9th, 2010, 03:49 AM posted to microsoft.public.excel.newusers
Joe User[_2_]
external usenet poster
 
Posts: 757
Default Difference between real number and a perfect fractions

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?



  #4  
Old April 11th, 2010, 05:09 AM posted to microsoft.public.excel.newusers
Matthew[_16_]
external usenet poster
 
Posts: 2
Default Difference between real number and a perfect fractions

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.
  #5  
Old April 11th, 2010, 09:04 AM posted to microsoft.public.excel.newusers
Joe User[_2_]
external usenet poster
 
Posts: 757
Default Difference between real number and a perfect fractions

"Matthew" wrote:
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 -----

"Matthew" wrote in 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 -----

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?


 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 10:25 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.