# how to prevent Excel from rounding

Our government's online system requires us to enter numbers that are not rounded. Many of the calculations are based on 1/6 and 2/3. I cannot enter .67. I have to enter .66 and our entries must add up to the total agreement. I cannot figure out how to take a column of numbers that I calculated to 3 decimal points (so I can see what the 2-decimal version should be) and have Excel how she the correct 2 decimal number without rounding. I have tried ROUND and I have tried checking "Precision as displayed" in Excel options but my 2-decimal column of numbers is still rounding. What am I missing?
###### Who is Participating?

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

What you are missing is that numbers like 1/3 are infinitely repeating and will always round somewhere. That said, it is a few decimals out and should not affect addition rounding.

So to enter 1/3 without rounding, you must enter 0.3333333333. Entering 0.33 is an input rounding error and if you add up enough, your total will be off.

This is mathematics, not Excel. Just input all your decimals every time.
0
Another way to do this is to always round to 2 decimal places and add the rounded numbers. This will always work and I do this all the time. But the rounded total has to be accepted as it may not be the theoretical total by one to two cents.
0
I understand what you're saying. This doesn't solve my problem though. The government system has validation checks that do not allow us to enter.67 when the value to 2 decimal placements ends in .66. The validation check is based on a value not exceeding half of a calculation where they do not round up. To make a long story short, I am doing all of my calculations in Excel to 3 decimal points so that I know what values to enter into the government system. When I add A1+B1, C1 is going to be 1-2 cents more than my government system will allow me to enter. If I could get Excel to (I guess) trim A1 and B1 to 2 decimals then C1 will be the correct value. I am trying to get this set up to minimize the work our admin team has to do as we have a lot of data entry to do and this rounding issue slowing us down ridiculously.
0
our messages crossed. yes the 1-2 cent difference is the issue that I am trying to address
0
so I guess I should ask: can I "TRIM" in Excel like I can in Access?
0
You do not have many choices. If you cannot accept rounded totals (only Governments are this foolish), then you have to enter to 4 decimal points at all times to prevent rounding errors 99 percent of the time.

As I said, it is just mathematics.
0
Trimming decimals is like rounding and won't solve the theoretical total issue.
0
Commented:
try something like this

Range("A1").NumberFormat = "0.00"
0
Commented:
or place this formula in C1
=left(A1,4)
0
I suspect the formulas to round the input leave the totaling problem I was outlining earlier. I think this would be the case.
0
Commented:
you are right, john
I remember a similar project I created .
I made  sure that the result of computation is limited to 2 decimal place (unrounded) before I post it to excel cell.
0
Yes, And I think the idea of rounding inputs as you and I have suggested is the best long term solution. It means a change in policy to accept rounded totals but (a) it can be done and (b) the results will always properly agree.
0
Commented:
I agree with John. If you cannot make use of accounting rounding then you must use 4 decimals and you will still have to adjust that penny error occasionally.

Krompton
0
Figured it out. Using ROUNDDOWN. Does exactly what I need.
0

Experts Exchange Solution brought to you by

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.