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?
 
Joanne BaileyDatabase AdministratorAuthor Commented:
Figured it out. Using ROUNDDOWN. Does exactly what I need.
0
 
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
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
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
 
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
 
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.