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. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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 | |
|
|