Sum of percentages: Access vs. Excel

[dwaite]

I have a formula that totals 100% in Excel, yet same set of numbers input in Access gives a total of 100.00005%. Excel appears to be more accurate.

How do I get Access to give me the same total?

Answer must be in 5 decimal places. I have checked for rounding differences, which would apply only in excel where some of these numbers are the result of a formula, and woud only make a possible difference of .00001. However, in Access I am entering values only, so this doesn't really apply. Numbers in Access are entered as percent (i.e. 1% is entered as .01), but even if I do this in Excel, I get the same answer.

Here's the numbers:
81.36695
2.00000
.80000
5.00000
.25000
.10000
.23750
2.11000
1.77500
.90000
1.40000
.35000
.20000
.10000
.01000
.00010
1.00000
.90000
.05000
.00045
1.40383
.00145
.00203
.04269

Thanks for any possible help!

 

[NumbersMax]

I copied and pasted your numbers into Excel and totaled them using the Sum function. They came to 100.00000 as you mentioned. I then imported that spreadsheet into an access table and ran a select query to sum the numbers in the column. It also came to 100.00000.

You should probably check you numbers in Access again to see if you gained the extra .00005 when you entered them.

 

[Joe4]

How do you have the field formatted in Access?
How many decimal places is that field formatted for in Access?

 

[dwaite]

Input field is formatted as Percent with 5 decimal places. Total field is formatted the same.

 

[Joe4]

Input field is formatted as Percent with 5 decimal places

Numbers in Access are entered as percent (i.e. 1% is entered as .01)

That is not entering them as a percent, that is entering them as a numeric or decimal value. If formatted as a percent, 1% should be entered as 1, not .01.

Otherwise, 81.36695% is really being entered as .8136695, which means you would need 7 decimal places, not 5.

 

[dwaite]

You were right! It was a data entry error. I had check and re-checked.....but when I proofed by printing my post and comparing with the Access data entry, I found the problem. Thanks SO MUCH!

 

[dwaite]

That is not entering them as a percent, that is entering them as a numeric or decimal value. If formatted as a percent, 1% should be entered as 1, not .01.

Otherwise, 81.36695% is really being entered as .8136695, which means you would need 7 decimal places, not 5.

Very interesting. I did check the field properties before replying and they are set to 5 decimal places - and you are right, I do enter .8136695, which is 7 decimal places. Access automatically converts it to 81.36685 when I tab out of the field.

Problem solved - thanks for your help!