Rounding Errors

microphish

New Member
Joined
Apr 30, 2002
Messages
1
I am computing annual budget numbers in Excel (whole number amounts) that have to be
spread into 12 months based on a specific monthly spread.
The Budget System that I am uploading these numbers to lops off any decimal amount
and then adds the 12 months together. Of course, this causes a rounding
error and the Annual total in Excel doesn't match the resummed value in my Budget System.

I am trying to head this problem off in Excel by only uploading monthly, whole numbers
by using the ROUND formulas when I spread the Annual amount into 12 months.
Inevitably, as in the budget system, the sum of the months has an absolute variance
from the Annual base number of 1 to 6 usually. Is there a way to force a match to the base without pickout out random months and adding back the 4 or 5. The numbers are
large enough that the 4 or 5 variance is insignificant but over 10,000 entries, the report numbers between Excel and the Budget System vary significantly. Any suggestions?
 

Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).
Any rounding you do will introduce errors. That is just a fact of life. You said the Budget system "lops off" the decimals. I assume you mean it just takes the integer portion of the number without rounding. To get the two to match, you will have to do the same in Excel without rounding by using the =INT() function. Of course this will introduce a whole new set of errors but at least both sets of numbers will agree.
 
Upvote 0
Not sure if this will help, but check under Tools>Options>Calculations and check the box "Percision as Displayed" This will force excel to base it's calculations only on values as they appear on screen. This usually will prevent most rounding errors.
 
Upvote 0
When working with precise numbers for financial reasons, do not use Excel. Excel is known to have floating point errors when rounding and calculating. This can be circumvented by using precision as displayed, but that doesn't always work.
 
Upvote 0
If your budget program does, in fact, lop off the decimals AND if the sum of the columns does not include the lopped off decimals, try using:

=FLOOR(value,1)

to round down to the nearest integer
 
Upvote 0

Forum statistics

Threads
1,213,526
Messages
6,114,122
Members
448,550
Latest member
CAT RG

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top