One of my customers asked if I could help them with a formula. The formula is found in article c2012665 in the Business Objects knowledge base and it converts a decimal number into fraction format. In other words it should convert .75 into 3/4 as a string. The formula used a clever loop but it had 3 problems:
1) If the input was an integer you would still get the fraction, but with a 0 on top.
2) If the input was between 0 and 1 (like .75) you would get the integer 0 in the output.
3) The formula was in BASIC syntax which makes it hard for many users to modify.
So I converted it to Crystal syntax and cleaned up the output. The result is below.
Note that this formula is set to round the decimal value to 4 places (see the 3rd line below). You can round it to more or less if you choose. But if you are using v8.5 you MUST round to no more then 2 decimal places or Crystal will start to error because there are limits on the number of iterations that v8.5 can do.
NumberVar raw :={your.field};
NumberVar wholeNumber := int(raw);
NumberVar decInput := round (raw - Wholenumber , 4);
NumberVar numerator :=1;
NumberVar denominator :=1;
NumberVar decimalFraction := numerator / denominator ;
Do ((If decimalFraction < decInput
Then numerator := numerator + 1
Else ( denominator := denominator + 1 ;
numerator := truncate(decInput * denominator)))
;decimalFraction := numerator / denominator )
While decimalFraction <> decInput;
(if wholenumber > 0
then totext(wholeNumber,0,"") + " "
else "")
&
(if numerator > 0
then totext(numerator,0 , "") & "/" & totext(denominator,0 , "")
else "")
Added 2/5/08:
The same customer later requested that the value be rounded to the nearest 1/16th. This can be done by using formula 22 on my Formulas page that allows you to round a number to the nearest ‘x’ value. You can either use a separate formula for the rounding or you can do the whole calculation in the first line of this formula. If you do the rounding in a separate formula you would use that rounding formula as your input at the top of this formula.
(For examples of my most popular formulas, please visit the FORMULAS page on my website.)