Link to home
Start Free TrialLog in
Avatar of QTony
QTony

asked on

Need help with rounding decimal to fraction formula

I am a new user to Crystal (6 months) and I use it to create reports for a cabinet making program called CabinetVision. I recently have been struggling with what should be a simple task which is to convert a decimal generated number into a fraction.

With the help of this forum (myself 10% the forum 90%) I've gotten a decimal to fraction conversion formula up and running---sort of.

The problem is that it is not rounding properly.  The program I am using (see above)  is generating decimals numbers that  round themselves to the closest 1/16

Ex: 1/16 = .0625 or
Ex: 1/8 = .1250

The decimal to fraction conversion formula is not something I prepared; I  downloaded it from Business Objects' knowledge base.

Here are the fractions I want to generate side by side with the actual fractions generated by the conversion formula:

What I want                    What I'm getting

        0                                       0/2  (?)

      1/16                                    3/50

      1/8                                    13/100

      3/16                                  19/100

      1/4                                      1/4

      5/16                                   31/100

      3/8                                     19/50

      7/16                                   11/25

      1/2                                       1/2


and so on


I feel like I'm so close; This is a very important formula for me to get running, any and all help is always appreciated---Thanks

Here is the formula:  Note: {@DECIMAL} is my internal formula that generates the decimal number that needs to be converted          
                                        into a fraction


_____________________________________________________________________________________________________
   
                                       
' This formula uses Basic syntax.

'Note: {@DECIMAL} is my internal formula that generates the decimal number that needs to be converted  into a fraction

     

Evaluateafter ({@DECIMAL})  

Dim decInput as number

Dim decimalFraction as number

Dim numerator as number

Dim denominator as number

Dim wholeNumber as number

' This variable will hold the value to be converted

' to a fraction type value.

Dim testNum as number

' The number 1.75 will be converted to look like

' 1 3/4 as a string output.

testNum = {@DECIMAL}

wholeNumber = int(testNum)

' In CR 8.5 this line would be

' decInput = round(testNum - wholeNumber,2)

decInput = round(testNum - wholeNumber,2)

numerator = 1

denominator = 1

decimalFraction = numerator / denominator

do While (decimalFraction <> decInput)

If (decimalFraction < decInput) Then

numerator = numerator + 1

Else

denominator = denominator + 1

numerator = truncate(decInput * denominator)

End If

decimalFraction = numerator / denominator

loop

formula = totext(wholeNumber,0,"") + " " + totext(numerator,0 , "") & "/" & totext(denominator,0 , "")



     

 
SOLUTION
Avatar of peter57r
peter57r
Flag of United Kingdom of Great Britain and Northern Ireland image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of QTony
QTony

ASKER

Thanks Pete; here they are




What I want                       What I'm getting                  Decimal value

        0                                       0/2  (?)                                   .0000

     1/16                                       3/50                                   .0625

     1/8                                       13/100                                  .1250

     3/16                                     19/100                                   .1875

     1/4                                         1/4                                       .2500

     5/16                                     31/100                                   .3125

     3/8                                       19/50                                   .3750

     7/16                                  11/25                                .6875

    1/2                                          1/2                                   .5000

    9/16                                       14/25                                   .5625

    5/8                                        63/100                                  .6250

    11/16                                    69/100                                .6875

     3/4                                          3/4                                  .7500

   13/16                                     81/100                               .8125

     7/8                                        22/25                                  .8750

    15/16                                      47/50                                .9375      
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of QTony

ASKER

I considered that, but unfortunately as you can see in the remark in the formula (about halfway down) it says that in crystal 8.5 you can only use 2. Just to see what would happen, I did put "4" in and ran single records. The program returned a couple more correct fractions but most were still wrong. When I tried to run more than 1 record, I got an error message stating

"A loop was evaluated more than the maximum times allowed"

Also: I made a typo on the 7/16 decimal value; it should be .4375, not .6875
ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of QTony

ASKER

It worked great!  Thanks so much. As I said earlier, I'm new at this and so I divided the points in the following manner:

300 to frodoman for giving me a working answer

75 to pete57r for looking at the problem (Pete answered a question for me earlier today)

125 for mimcc for looking at both my questions today and taking the time to answer them.

Guys: If this type of point division doesn't conform with the usual mores of this site, please let me know what I did wrong so that I can correct it in the future. Be frank if you think that will help me

This formula is going to really streamline some things for me---Thanks again

I'm leaving  for an appointment but I will be back i app. 3 hours if there are any comments I need to answer.

QTony

Hey QTony.

Typically the points awarded should relate to the specific question at hand so in this case, the normal process would have been to give all the points to me for solving your problem.  You would split the points if two(+) people gave you different answers that were both viable or if one person gets things started and someone else finishes it off (for example if Pete had first suggested that the rounding is the problem and I then gave you the specific solution you might want to split points because I build on his observation).

It isn't normally expected that you award points just because someone looked at a question - any unfortunately you may find that some of the comments posted really aren't helpful at all [this won't be the case with pete57r or mlmcc].  The exception to this is if several people work really hard on a problem but it turns out to be "unsolvable" then some people will choose to award points simply to acknowledge the effort.  This isn't officially the EE policy, but it seems to be common practice.

I'm giving you this feedback because you asked - I have no problem with how you divided the points and ultimately it's up to you to decide what responses are helpful to you.

cheers,

frodoman
Avatar of QTony

ASKER

frodoman,

Thanks that advice; I'll keep that in mind for my next question. After running a few reports using the fraction converter, I found one minor glitch which is that is when the decimal value returns a 0.000 value the resulting fraction string is displayed as:

"0/2"

Example 30.0000 coming out as 30 0/2

I'm upset at myself for missing this; I was in a rush to make my appointment

Other than that, every single fraction is coming in perfectly. In about five minutes I am going to post the question on haow to correct this monor discrepancy (If it's too complicated to fix, what I figured I could do is filter out those records with a "0.000" value and use the subscript function on them to rip the first two positions for values 1-99 and the first three positions for values 100-999; no cab-t inet will ever get larger than this) Still, that's probably not the most efficient way to do it and it would be nice to have it running properly.

Oh and by the way could you or anyone else recommend a "how to" book on Basic? One thing is that the cabinet program I was telling you about, CabinetVision,  uses a form of basic for its user created standards. It's my understanding that it uses a fairly old version, circa 1996.. so that any book couldn't be too skewed towards newer formulas that didn't exist at that time. ---Thanks
Most books available today will be for VB6 or VB.Net.

Do you know if it is a MS version of basic?  If so look for VB5 books.

mlmcc
Avatar of QTony

ASKER

Thanks. To answer your question, the tag line when you enter the UCS area of CabinetVision  reads: "The power of UCS lies beneath the interface-created macro which can be put into it's own language similar to Visual Basic.

I think it might be something homemade. What's difficult  about using it is that there is no real "manual" that comes with the program; you have to cobble together knowledge about it by looking at examples and going on to the CabinetVision bulletin board. With Crystal, it wasn't that hard to get started; I bought a book called "Crystal Reports for Beginners" and within a couple of months I was able to produce some functional custom reports for my client. Because each cabinet shop has unique reporting requirements, in my opinion, to make the best use of CabinetVision you have to learn Crystal. Many users don't though and you see frequent complaints about the fact that the "stock" reports that come with CabinetVision are not suited for one user or the other.

I'll tell you, between learning CabinetVison (Which has a reputation for having a steep learning curve), its UCS feature and Crystal, it's been an intellectually challenging 6 months.

I'll take your advice and  go on Ebay to look for a VB5 book. I think  I'll also look for a more comprehensive Crystal book.

I'm sure you'll see more of my questions

QTony
You might also look for books on Basic.

mlmcc