# Sum of percentages: Access vs. Excel

#### dwaite

##### New Member
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.

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!

### Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a \$25,000 loan, 5% annual interest, 60 month loan.

#### NumbersMax

##### Board Regular
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

##### New Member
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

##### New Member
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

##### New Member
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!

1,191,173
Messages
5,985,096
Members
439,940
Latest member

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back