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.