I created a spreadsheet on my MacBook (OSX 10.13.3), running Excel for Mac 2011 (v14.7.7 (170905)) to track business expenses for tax purposes. See attached. I've got 4 categories of expenses, Supply, Service, Misc. and Travel. Within Supply and Service, I've created sub-codes for different types of Supply or Service. I have created formulas to provide me with subtotals for the sub-categories. In order to ensure that I don't forget to enter a sub-code when entering my data (yes, this is because I sometimes forgot to do that), I wrote an IF THEN formula, which basically said: If the sum of the sub-category totals minus the sum of all entries = zero, then print "OK," and if not, then print "NOT OK". A NOT OK display means I forgot to enter the subcode. See cells C35 and F34. Here is a sample: =IF(SUM(B31:B34)-B29=0,"OK","NOT OK"). It worked.
Until, that is, I entered a new Supply entry in cell B11, value $137.96 (with the subcode in C11). The math all checks out with a calculator, but Excel says NOT OK. So I added a checksum next to it, cell B35, to prove it with a numerical value. The checksum said it should be OK. Comparing the two sums "=(allsums - subtotals sums)=0" returns TRUE.
If you delete the data in cells B11 and C11, OK comes back. If you use $137.90-$137.92, OK. If you use $137.99, OK. But if you use $137.93-$137.98, NOT OK (even though I've got the corresponding subcode in column C). You can make two entries in two rows, one for part of $137.96 (e.g. $137.90), and the other to make up the difference (e.g. $0.06), NOT OK (even though I've got the corresponding subcode in column C).
This is simple math (addition and subtraction), so I can't fathom how I might end up with an irrational fractional number from division that might cause a rounding error. None of the values in column B are resulting from formulae (e.g. splitting a value in 2, by using division) -- they are hard entered as numbers only.
The formula works fine for the other category, Service, in Column E. Even if you make an entry for $137.96 (with a required subcode).
I may be a git when it comes to computerating, but I'm not dumb as a post; this situation has me flummoxed Help me, Excel Wan-Kenobi; You're my only hope!
