Why sometimes your calculations don’t match your expectations.
As I was updating a report for work, I couldn’t get the numbers to quite match up. The task was simple enough, take the old report from Excel and automate it using R. Easy Peasy.
However, my coworkers Excel report had 83 and my report had 82.
Where did I go wrong?
After some soul-searching and an even more extreme amount of time on Stack Exchange, I had my answer. Rounding. This was my first time matching a report originally built in Excel with R. I had done this before with python, but python’s generic rounding behavior matches Excel’s.
See, rounding isn’t as simple as what was taught to many of us in school. If its five go up, less than five you go down. In fact, there’s a lot of different methods for rounding. Check out the Wikipedia article on Rounding to see what I am talking about. I won’t get into all the different rounding methods for the sake of my sanity and yours, but understand that rounding isn’t all that simple. If you want a more friendly take on rounding try Mathisfun.com.
There’s also a whole thing on IEEE standards, but I write this blog for less technical folks who want to know more about data so I won’t go into that. Just know that different programs may use different rounding methods.
The Issue I had.
So my issue was that my coworker was using Excel to create a rather simple report on some HR metrics that we do monthly. The values needed to be rounded to the nearest whole number for presentation, so he used excel to do that work. When I went to QA my work, my calculations were not matching up.
So what was happening?
Excel uses the more common method of “half round up”. All that means is that if it sees a “5” as the last digit, it will go up. In my case, it was rounding 82.5 to 83.
Meanwhile, R uses “round to even” which means it will round the value to the nearest even number when it is at the halfway point (in my case 82.5). I asked R to round to the nearest whole number and it spat back 82.
Fun. This is why sometimes calculations don’t match your expectations.
How do I fix this?
Well, it really depends on your audience for starters. Do they even care about that half digit? In my case, we were working with such small values to compute a percent that, sadly, yes it did matter. So I had to go digging around and fiddling about until I could write a function that would emulate the excel method of rounding. On review, maybe there’s a package I could find. Additionally, my consumer was also much more comfortable with excel than anything else so they really wanted it to match that behavior.
In other instances, you can alter the code. Excel can force round down or round up. SAS has functions that do even or odd rounds. Python is quite flexible when it comes to rounding but I might just love python.