Software

Ask something
Discussion Locked

fractional value not equal to the sum

0 helpful
3 replies

by dvgoad

Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.

Using MSAccess 2002. Report with three columns of numbers. Column 1 is a whole number. Column 2 is a percentage formatted as percentage with 2 decimal. Column 3 formatted standard with 0 decimals. The math across rounds up/down correctly. However, when I add column 3 down... it doesn't add up!!!

EXAMPLE:
Column 1 is a given number.
Column 2 is the percentage.
Column 3 = column one * column two
47,320 32.93% 15,582
47,580 33.29% 15,839
47,300 33.39% 15,793
47,280 33.22% 15,706
47,680 32.48% 15,486
47,260 33.37% 15,771
47,200 33.02% 15,585
Total in Access: 109,764
On Calculator: 109,762

Where did two go? Is there special formatting I need to do in column 3 as well as the total field?

3 replies

by thechas

While I'm not an Access expert, you can run into the same type of problem in Excel.
This is a truncation error.

When you calculate out the full data set manually, you come up with 109,764.31
This is what Access is calculating and just displaying the results to a rounded zero decimal place value.

Actually, the Access total is technically more accurate than the total of the displayed data.

You need to add a statement or format detail to truncate the calculated data to zero decimal places.
Or, if appropriate, change the format of the calculated value to show the first 2 decimal places.

Chas

by thechas

Assuming that Access has the same functions as Excel, you need to add a 4th column and invoke either the INT, RND, or TRUNC function on the calculated data from the 3rd column.

The function you use is based on how you wish to handle the data.
The INT and TRUNC functions round the number down.
The ROUND function rounds the numbers up or down to the nearest stated decimal place.

Chas

by otl

Even going to a tenth of the whole number makes a drastict change in the answer;

47,320 32.93% 15,582.5
47,580 33.29% 15,839.4
47,300 33.39% 15,793.5
47,280 33.22% 15,706.4
47,680 32.48% 15,486.5
47,260 33.37% 15,770.7
47,200 33.02% 15,585.4

109,764

Should be using to the hundredth place, as your percentage does !

This discussion is closed to new replies.