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?
Joanne BaileyDatabase AdministratorAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

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.

JohnBusiness Consultant (Owner)Commented:
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
JohnBusiness Consultant (Owner)Commented:
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
Joanne BaileyDatabase AdministratorAuthor Commented:
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
Acronis Data Cloud 7.8 Enhances Cyber Protection

A closer look at five essential enhancements that benefit end-users and help MSPs take their cloud data protection business further.

Joanne BaileyDatabase AdministratorAuthor Commented:
our messages crossed. yes the 1-2 cent difference is the issue that I am trying to address
0
Joanne BaileyDatabase AdministratorAuthor Commented:
so I guess I should ask: can I "TRIM" in Excel like I can in Access?
0
JohnBusiness Consultant (Owner)Commented:
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
JohnBusiness Consultant (Owner)Commented:
Trimming decimals is like rounding and won't solve the theoretical total issue.
0
Rey Obrero (Capricorn1)Commented:
try something like this

Range("A1").NumberFormat = "0.00"
0
Rey Obrero (Capricorn1)Commented:
or place this formula in C1
=left(A1,4)
0
JohnBusiness Consultant (Owner)Commented:
I suspect the formulas to round the input leave the totaling problem I was outlining earlier. I think this would be the case.
0
Rey Obrero (Capricorn1)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
JohnBusiness Consultant (Owner)Commented:
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
KromptonCommented:
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
Joanne BaileyDatabase AdministratorAuthor Commented:
Figured it out. Using ROUNDDOWN. Does exactly what I need.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

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

Start your 7-day free trial
JohnBusiness Consultant (Owner)Commented:
How does rounding down solve your problem. It leaves you where I said. The resulting total will not be theoretically correct.

Are you playing games with us?
0
Joanne BaileyDatabase AdministratorAuthor Commented:
found a solution. Others suggested policy changes which I have cannot affect.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.