Penny Rounding Problem
A computer rounding problem that I like to call “The Penny Rounding Problem” has been around for many, many years. At least two movies have been made with this problem a core element: The Office, and Superman III. The basic problem is that a column of numbers should add up to the total at the bottom. But they do not.
Mark Reynolds is currently at Southwestern Energy where he works in the Fayetteville Shale Drilling group as a Staff Drilling Data Analyst. In this position, he pulls his experiences in data processing, data analysis, and data presentation to improve Southwestern Energy’s work in the natural gas production and mid-stream market.
Recently, Mark has been working toward improved data collection, retention, and utilization in the real-time drilling environment.
For example: 1/3 is represented as .33, or even .333. But if you add .33 together 3 times, you get .99, not 1.00 – a penny off. This is why your final mortgage payment (if you ever actually paid it off) is never exactly the same as the monthly amount. Even worse, take 2/3 or .67. Multiple .66666… by 3 and you get 2.00; multiply .67 by 3 and you get 2.01.
Solving the problem is relatively simple, but requires diligence. Individual calculations must be individually rounded to the correct number of decimal places.
When I teach Excel at the college, I require the student to explicitly ROUND the answer to any mathematical operation involving
- possible sub-penny answers (divide by three, multiply by .0475, etc.)
- down-stream use of the answer.
Taken individually — addition of two numbers will never generate sub-penny digits, non-currency measurements (weight, speed, etc) do not bother people when the totals are off by small decimal fractions, and if the result to the mathematical calculation is never to be used then no one cares.
So when an interest equation is entered into Excel
= A3 * A4 / 12,
you should change it to be
= ROUND( A3 * A4 / 12, 2 ) so that the answer is rounded to 2 decimal places.
So can Richard Pryor get rich by taking all of the rounded, fractional pennies and putting them in his account? This is called Salami Slicing and Snopes calls it a legend. But do gas stations do it with your pump price? read here for the answer