# Thread: numbers rounding problem

1. ## numbers rounding problem

I am getting 2 different results in Grade Point Averages (GPA) on a GPA Report and on the Transcript.
I have 2 tables involved: tblGradeValues with a field GradeValue (a number field, field size single, format standard, decimals 2) and tblTranscripts with a field Units ( a number field, field size single, format standard, decimals 2). Access 97

When I run the GPA Report my results for Students: David is 3.23 GPA, Norbert is 4.0, Barbara is 3.91 and Sam is 4.50.

When I run the Transcripts: I show grades for each of 8 semesters with a semester GPA and a Cummulative GPA after each semester. Results: David is 3.20 GPA, Norbert is 3.97, Barbara is 3.90 and Sam is 4.50.

So my problem is that each semester is rounded (to some point -- I know not where). Then as each semester is added for the cumulative GPA the rounding effects the final cumulative GPA.

I can't have this discrepency how do I tell Access to round each of these reports at a point that will make the result consistent?

I have check my report fields and they are all : standard 2 dec. By the way I only want to show 2 decimals.

<img src=/S/brickwall.gif border=0 alt=brickwall width=25 height=15>

2. ## Re: numbers rounding problem

The problem may be more subtle than that actually - Does each student take the same number of hours each semester? If not, you can't take an average of the grade point averages for each semester. There is also a potential rounding problem in that the numeric field size you are using is a floating point number with about 6 places of accuracy. But when you start doing repeated calculations on those kind of numbers, the accuracy quickly diminishes. Are you storing the calculated result in a temporary table? If so the number will be rounded to 2 places if you tell it to. Otherwise it will be used with full accuracy. You could try using the double precision data type, which gives you 15 to 16 decimals of accuracy. But the bottom line is the only way to accurately calculate the GPA is to all up all the hours, and add up all the calculated points, and then divide the sum of the points by the hours. Hope this helps.

3. ## Re: numbers rounding problem

Each student does take the same number of hours each semester. But, the units are added up for each student each semester on the transcript so that shouldn't make any difference. I don't think I know how to store the results in a temporary table I will try that. I don't know what double precision data type is I will try to look that up. Thanks You stirred my brain. Pat

4. ## Re: numbers rounding problem

A couple points:

- the number of decimals in the field defintion has no effect on the precision with which the data is *stored* -- it affects only the number of decimals with which it is displayed. See Help for additional information.

- floating point calculations are not exact, although you would usually expect to see differences in "lesser significant" figures. You might want to consider doing your calculations with Integers and scaling the results.

- your answer to Wendell makes me wonder whether you both mean the same thing when you say: "Each student does take the same number of hours each semester". You could mean that 1) *all students* have the same hours in each semester or 2) all hours for *each student* is the same for all all students. this is not just semantics -- it makes a significant difference on the calculation of averages.

Consider the following for a single student in two semesters:
2
2
2

3
3

Clearly, the average of semester 1 is "2" and the average of semester 2 is "3". But what is the overall average? Is it (2+3)/2 = 2.5 or is it (2+2+2+3+3)/5 = 12/5 = 2.4

I think that was the point that Wendell was making. The only way to correctly calculate the overall average is to add all the points and divide by the total number of hours. I would tend to caluculate the average manually in the report by summing the components, rather than by using an Avg function. That way, I would be certain what the calculation entailed, plus I would have the intermediate results for checking...

Hope this helps.

5. ## Re: numbers rounding problem

I think I understand this and I found making a temporary table is the way to do it. However, I still wonder what is the best number design to use: double, single, etc. ?
Thank you for your help.
Pat

6. ## Re: numbers rounding problem

Rounding is a problem everyone has bumped up against. You have to understand there is a difference between the # of decimal positions you are displaying, and the actual # of positions in your data.

Most math creates a double floating-point decimal for the result. A result of that you display as 1.23 may actually be 1.2344444444! The best way to handle this is to actually round the intermediate results to 2 decimals; so that 1.23444444 becomes 1.23!

7. ## Re: numbers rounding problem

Thank you for the explaination. How do I actually round in the temporary table? You don't mean I hope change the field to text and edit it.
Pat

8. ## Re: numbers rounding problem

No, you don't have to "edit" the numbers! First of all, make sure your numbers are declared as currency. Then, any time you have a calculation (especially if they use multiplication and/or division), always round the result. For example, now you may have this expression:

GPA = ...some calculation...

Instead, do this: GPA = Roundit(...some calculation)

Your Roundit function will look like this:

Public Function Roundit (byref SomeNumber) as Currency

Roundit = CCur( CCur(SomeNumber/100) *100)

End Function

This function will only round to 2 places. You can find a more flexible rounding function at my website (follow link below).

9. ## Re: numbers rounding problem

Thank you I think I have it now. I don't know much SQL so I am slow at this.
Pat

10. ## Re: numbers rounding problem

Since you managed to escape posting your version of Access, I'll throw in the point that Access 2000 has a built in Round() function, although it doesn't work the same way that Excel's rounding function does and surprises those who expect it to be the same.

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•