Decimals to Fractions - Newbie ?

[pasternik]

Hi,

I really need some help here, I have imported an excel spreadsheet into my current DB, and I have a column that in excel is shown as a fraction, but when imported is shown as a decimal. I have bought the Refer file also thru Filemaker and it gives an example as:

 

Int(Number) & " " & Round((Number - Int(Number)) *

(Case(

Mod((Number - Int(Number)) * 2; 1) = 0; 2;

Mod((Number - Int(Number)) * 3; 1) = 0; 3;

Mod((Number - Int(Number)) * 4; 1) = 0; 4;

Mod((Number - Int(Number)) * 5; 1) = 0; 5;

Mod((Number - Int(Number)) * 6; 1) = 0; 6;

Mod((Number - Int(Number)) * 7; 1) = 0; 7;

Mod((Number - Int(Number)) * 8; 1) = 0; 8;

Mod((Number - Int(Number)) * 9; 1) = 0; 9;

Mod((Number - Int(Number)) * 10; 1) = 0; 10;

Mod((Number - Int(Number)) * 11; 1) = 0; 11;

Mod((Number - Int(Number)) * 12; 1) = 0; 12;

Mod((Number - Int(Number)) * 13; 1) = 0; 13;

Mod((Number - Int(Number)) * 14; 1) = 0; 14;

Mod((Number - Int(Number)) * 15; 1) = 0; 15;

Mod((Number - Int(Number)) * 16; 1) = 0; 16;

Mod((Number - Int(Number)) * 17; 1) = 0; 17;

Mod((Number - Int(Number)) * 18; 1) = 0; 18;

Mod((Number - Int(Number)) * 19; 1) = 0; 19;

Mod((Number - Int(Number)) * 20; 1) = 0; 20;

Mod((Number - Int(Number)) * 21; 1) = 0; 21;

Mod((Number - Int(Number)) * 22; 1) = 0; 22;

Mod((Number - Int(Number)) * 23; 1) = 0; 23;

Mod((Number - Int(Number)) * 24; 1) = 0; 24;

Mod((Number - Int(Number)) * 25; 1) = 0; 25;

Mod((Number - Int(Number)) * 26; 1) = 0; 26;

Mod((Number - Int(Number)) * 27; 1) = 0; 27;

Mod((Number - Int(Number)) * 28; 1) = 0; 28;

Mod((Number - Int(Number)) * 29; 1) = 0; 29;

Mod((Number - Int(Number)) * 30; 1) = 0; 30;

Mod((Number - Int(Number)) * 31; 1) = 0; 31; 32));0) & "/" &

(Case(Mod((Number - Int(Number)) * 2; 1) = 0; 2;

Mod((Number - Int(Number)) * 3; 1) = 0; 3;

Mod((Number - Int(Number)) * 4; 1) = 0; 4;

Mod((Number - Int(Number)) * 5; 1) = 0; 5;

Mod((Number - Int(Number)) * 6; 1) = 0; 6;

Mod((Number - Int(Number)) * 7; 1) = 0; 7;

Mod((Number - Int(Number)) * 8; 1) = 0; 8;

Mod((Number - Int(Number)) * 9; 1) = 0; 9;

Mod((Number - Int(Number)) * 10; 1) = 0; 10;

Mod((Number - Int(Number)) * 11; 1) = 0; 11;

Mod((Number - Int(Number)) * 12; 1) = 0; 12;

Mod((Number - Int(Number)) * 13; 1) = 0; 13;

Mod((Number - Int(Number)) * 14; 1) = 0; 14;

Mod((Number - Int(Number)) * 15; 1) = 0; 15;

Mod((Number - Int(Number)) * 16; 1) = 0; 16;

Mod((Number - Int(Number)) * 17; 1) = 0; 17;

Mod((Number - Int(Number)) * 18; 1) = 0; 18;

Mod((Number - Int(Number)) * 19; 1) = 0; 19;

Mod((Number - Int(Number)) * 20; 1) = 0; 20;

Mod((Number - Int(Number)) * 21; 1) = 0; 21;

Mod((Number - Int(Number)) * 22; 1) = 0; 22;

Mod((Number - Int(Number)) * 23; 1) = 0; 23;

Mod((Number - Int(Number)) * 24; 1) = 0; 24;

Mod((Number - Int(Number)) * 25; 1) = 0; 25;

Mod((Number - Int(Number)) * 26; 1) = 0; 26;

Mod((Number - Int(Number)) * 27; 1) = 0; 27;

Mod((Number - Int(Number)) * 28; 1) = 0; 28;

Mod((Number - Int(Number)) * 29; 1) = 0; 29;

Mod((Number - Int(Number)) * 30; 1) = 0; 30;

Mod((Number - Int(Number)) * 31; 1) = 0; 31; 32))

 

My problem is I don't know how to use it!! I have the field in the DB set up as a text field, but when I set it as a calculation field, and use the above and press ok, I get errors!! Can anyone help me to use this function?

 

Jenni

 

P.S. I can upload a sample of my DB if anyone can help me with this problem, please let me know.

[Ender]

In the calc above, it's looking for a field called "Number", which would be a field of type Number too. The calc code will need to reside in a Calculation field with text result.

 

If you still get errors, let us know what they are.

[pasternik]

THANK YOU SO MUCH!!! I can't believe it was that simple!!

 

But I have more questions, why does it have a 0 in front of the fraction? and is there any way to carry out the Int. to 6 places along with the decimal to 6 places? I am calculating the fractional interest of mineral owners and some of them own as an example 2598/697520 or it could be 1/764321 (just an example) how do I get filemaker to represent these fraction formats correctly?

 

Thank you so much for your help!!! smiley-laughing

[Ender]

It's important to remember that that calc above is an approximation of a fraction to within 1/32 of the actual number. Although greater accuracy could be achieved by extending the function out, the numbers you seem to be after would require this calc to be unreasonably large.

 

A better solution is to use a recursive technique to arrive at the fraction within whatever degree of accuracy you desire. Fortunately, some smart people have already worked out the algorithm for this, and indeed have built FileMaker examples for us to examine.

 

The techniques I like use Custom Functions. Custom Functions are defined by the developer and can be recursive. Unfortunately, you'd need the Advanced version of FileMaker pro to install them in your files.

 

Here is a very detailed description of the algorithm and a Custom Function that converts a decimal to a fraction, from our good friend Mikhail:

 

http://edoshin.skeletonkey.com/2006/01/fraction_approx.html

 

And some other techniques are discussed in this recent thread:

 

http://www.fmforums.com/forum/showtopic.php?tid/161229

[pasternik]

Ender,

I wanted to thank you for all your help. I am new to Filemaker but very enthusiastic about what it can actually do. I did how ever come upon another forum, that gave me the exact calculation I was looking for..

 

Let ( [precision=10000;

p0=Abs(Fraction_M);n0=Mod(p0;1);

p1=1/Case(n0 ;n0;1);n1=Mod(p1;1);

p2=1/Case(n1 and (p1/n1

p3=1/Case(n2 and (p1*p2/n2

p4=1/Case(n3 and (p1*p2*p3/n3

p5=1/Case(n4 and (p1*p2*p3*p4/n4

p6=1/Case(n5 and (p1*p2*p3*p4*p5/n5

p7=1/Case(n6 and (p1*p2*p3*p4*p5*p6/n6

d=Round(p1*p2*p3*p4*p5*p6*P7;0); n=Round(p0*d;0);nf=Round(n0*d;0);

sgn=Choose(Sign(Fraction_M)+1;"-";"";"");

whole=Div(n;d);

fraction=Case(d=1;"";nf;nf&"/"&d;"")];

Trim(sgn&Case(not p0;0;whole;whole&" ";"")&fraction))

 

Precision was actually set to 100, originally but I set it to 1000 for testing purposes, and it works perfectly!! smiley_cool

I work in the Oil & Gas Business, and I am setting up a very complex DB, calculating fractional interests of Mineral Owners, I have come along way, but I am sure I will have many more questions in the near future, hopefully you and others like you, will be there to help me along the way. smiley-laughing

 

Thank You Again!!

 

Jenni