Best data type & length for currency calculations that involve percentages

[DB2010MN26]

I have a database that I'm building off of a process that is currently done in excel. I want my percentages calculations to match what I get in excel but I'm finding the numbers to be off by as much as .4%. I'm pretty sure they issue has to do with the precision of its calculations but I need some advise on what the best settings are.

My percent numbers are currently set to the field size "Double" with a percent formatting. My currency numbers are set to the format Currency and decimal places on auto.

[John_G]

Hi -

The first thing you need to remember is that the format settings affect only how you see the results, not how the numbers are stored internally. Both Currency and Double have relatively high precision. If you are getting a difference of .4% between Excel and Access, that seems high. What I could suggest is first to remove all the % formats from your data, and just compare the real underlying numeric data.

Question - are you importing from Excel to Access and then comparing, or redoing the calculations in Access?

John

[RayMilhon]

You need to check the formatting of the appropriate fields in both Access and Excel and make sure they match.

Excel:
Pct = Double, Fixed, 1 decimal place for 99.9%
Numerator = Long
Denominator = Long

Access:
Table formats
Numerator = Long
Denominator = Long

form/report format
PCT = Double, Fixed, 1 decimal place