Forum Discussion

tfears1's avatar
tfears1
Copper Contributor
Apr 10, 2018

$137.96 causes IF THEN formula to fail. I'm not making this up.

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!

Resources