Rounding Errors

microphish · May 1st, 2002, 02:08 PM

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?

Steve Hartman · May 1st, 2002, 02:17 PM

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.

lenze · May 1st, 2002, 02:19 PM

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.

zacemmel · May 1st, 2002, 02:27 PM

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.

Barry Katcher · May 1st, 2002, 03:02 PM

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

May 1st, 2002, 03:02 PM	#5
Barry Katcher Board Regular Join Date: Feb 2002 Location: Dog Beach, Florida. Yeaahh! Posts: 4,038	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 __________________ Barry- Photo Restoration/Enhancement http://www.smiledogproductions.com click below for detour

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

May 1st, 2002, 02:08 PM	#1
microphish New Member Join Date: Apr 2002 Posts: 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?

May 1st, 2002, 02:17 PM	#2
Steve Hartman Board Regular Join Date: Feb 2002 Location: Houston,Texas Posts: 418	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.

May 1st, 2002, 02:19 PM	#3
lenze MrExcel MVP Join Date: Feb 2002 Location: Helena, MT Posts: 13,690	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.

May 1st, 2002, 02:27 PM	#4
zacemmel Board Regular Join Date: Apr 2002 Location: Redmond, WA Posts: 636	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.