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 , "")
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
"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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
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
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
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
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
Do you know if it is a MS version of basic? If so look for VB5 books.
mlmcc
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
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
mlmcc
ASKER
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