I have an issue with Microsoft Excel fighting me on rounding. This is for a spreadsheet for work, and I'm a perfectionist, so this is driving me nuts for two reasons. I'll try my best to explain it.

The sheet is to figure out price distribution for our list sales. Basically, we have 29 different boards in my building, and people can purchase listings from any number of boards they want. The cost is based on how many items are in each list, so the portion that goes to each board is always unique. I have taken to doing the distribution calculations myself by hand on a calculator because I am tired of having to double-check everything every time I use the sheet. Basically, how it works is this:

There is a column to list each board (just as an identifier, not for calculations), the next column is the number of list items for each board for the purchase, and the next column is the price for each individual board. Above these three columns there are two additional boxes: one is for the total number of list items, and one is for the total cost of the entire purchase. The third column, the column that lists the individual board prices, is where the calculations are done. Basically, to get the cost of each individual board, you divide the number of board items by the total number of items and then multiply that by the total price. Because the result is a price, it needs to be rounded to two decimal places.



So, my problem is this: Excel seems to round things by the wrong decimal places. Whether I use the ROUND function or go through the formatting, or try both at the same time (which I never expected to work anyway, but I was desperate), I always get the same, technically incorrect, result.

The most recent example is this: One purchase has 1,312,319 list items total, and the total cost is $895.33. One of the boards has 41,048 list items. To get my cost for that board, you have to divide 41,048 by 1,312,319 (which just gives you what percent that board is of the total order) and then multiply that by $895.33 (because you want to know how much money that percentage is of the total). When I do that math on the calculator at my desk, the value is 28.004937. Because the decimal in the thousandths place is a 4, the decimal in the hundredths place, 0, should not change, so my price should be $28.00. However, no matter what I try to do in Microsoft Excel, it always displays the price as $28.01, indicating that it is first rounding the thousandths place using the ten thousandths place, and then rounding the hundredths place using the thousandths place. It's rounding twice, rather than just rounding the hundredths place by the thousandths place.

In my job, we have to account for every single penny, so if I'm off by even a penny, that can cause a major delay (not to mention a major headache) as I try to find the issue. Now, I probably never would have noticed and gone on just adjusting one of the individual boards by a penny or so as needed (because it makes sense when truncating such a long decimal that overall totals could be off by a penny or so), but when I needed to only separate into a few boards once and was in a hurry, I just banged it out on my calculator quickly and put it in the sheet later. That's when I noticed my numbers weren't quite matching, and I went and re-did my math on the calculator and found this issue.

I apologize for such a long post, but I have searched all over the internet, and tried fiddling with all sorts of settings, and I can't seem to figure this out. The only other thing I could think of at this point is that, somehow, my calculator is wrong, but I am NOT going to sit down with pencil and paper and do it out by hand to see (ahaha).

Please, any help would be HUGELY appreciated.

When I do your calculation in Excel I get:

=41048/1312319*895.33 = 28.0050093308

and when I do the same with Windows calculator I get: 28.0050093308

Maybe your calculator is at fault.

You know, that would be just my luck. I pulled out the calculator and did the math again, and it is still saying 28.004937. But, I pulled out my iPhone and used that calculator, and it came out the same as what you got, as did the calculator on the computer. So, yeah, now I wonder how many OTHER calculations I got wrong with this calculator.... How the heck is a CALCULATOR wrong? *chucks calculator at wall*

Either way, this question is kind of moot anyway, as I just trashed the old spreadsheet and built a new one from the ground up, and it not only is working perfectly, but is even better than the original. My boss was impressed and very pleased with it. So some good came out of this, at least. Still have no clue how this calculator could be doing math wrong.... That's kind of its only function in life.

It is not "wrong" per se. Ironically, it is the calculator, not Excel that is "rounding twice".

More accurately, the calculator is simply rounding (or truncating) to a different number of decimal places (or significant digits).

[EDIT] And it might be a decimal calculator; that is, it might use decimal instead of binary representation internally.

We can simulate the calculator result by doing the following in Excel:

A1: 41048
A2: 1312319
A3: 895.33
A4: =ROUNDDOWN(A1/A2, 7)
A5: =A4*A3

A4 displays 0.0312789, and A5 displays 28.004937537.

Presumably, the calculator displays only 6 decimal places, truncating.

What calculator did you use? Perhaps the number of decimal places (or significant digits) is configurable.

It's not a fancy calculator or anything. It's just your standard, no frills, no nothing but the most basic of unctions calculator. There is nothing you can customize about it. All you've got are the basic 10 numbers, add, subtract, multiply, divide, equals, square root, and the memory buttons. And a little solar panel and a screen. You know, one of the ones from back when life was simpler. I don't remember offhand how many digits it has. Either way, I made a completely new spreadsheet and scrapped the other. And I stick with the calculator on my iPhone or computer. So, "problem" solved. Ahaha. Thanks for taking the time t explain, though. I appreciate it!