Baffled By Simple IF Formula Response - Need Help

[smurphyatl]

I'm a financial analyst and have been using Excel for years and consider myself reasonably proficient. I've built a simple spreadsheet for my personal use to track Budget vs Actual for a new home currently under constructon.


Here is the formula: +IF(D94=D95,"OK","CHECK AGAIN")


It simply checks the total expenses against the total added to the budet to make sure I don't miss anything. If the totals match, everything is OK, otherwise I check again. About a week ago this formual stopped working.


Here are the current contents of the two cells:


$642,544.0600

$642,544.0600


But my formula returns "Check Again," even though the numbers are the same? And here is something even crazier. When I enter expenses, if I make them round numbers (no cents) the formula continues to work fine, but when I carry the values to the hundreths space, the formula stops working (again even though the values are identical as shown above)?


I'm baffled. Anyone who can answer this one for me is truly an Excel master.

 

[keymaster]

Welcome to Chandoo.org forums Smurphyatl...


Thanks for your question. Take a minute to explore our forums and get to know us.


Regarding your question:


There are a few reasons why this could be happening.


1. Did you accidentally turn off Automatic formula calculation?


If so, go to formula ribbon and set up calculation option to "Automatic" instead of "Manual". This way, Excel should re-calculate the formula whenever the values change.


2. Did you set up one of the 2 cells (D94, D95) as TEXT?


When you format a cell as text, although it looks like number or currency value, Excel treats it as TEXT for formula purposes and could throw an error or give in-correct results. To fix this, just select the range D94:D95 and format it as currency.


3. Are there any spaces before or after the numbers?


If you put an extra space or two before or after the number, although it looks like $642,544.0600, when referred thru formula it returns " $642,544.0600" and this could create un-wanted results.


Also see our tutorial on Excel formulas not working? What to do

 

[Colin Legg]

This might help:


http://www.cpearson.com/excel/rounding.htm

Basically, computers hold numbers in binary, not decimal, so two numbers which appear to be the same in decimal (how you see it), might not be the same in binary (how the computer sees it). Applying rounding is a workaround, or you can take the absolute difference of the two numbers and allow for a tolerance such as less than 1 cent, eg.

[pre]

=IF(ABS(D94-D95)<0.01,"OK","CHECK AGAIN")

[/pre]

 

[smurphyatl]

Thanks Chandoo. I checked your three possibilities, but none were the cause. I don't know why this was happening, but I suppose it doesn't matter now because Colin Legg fixed it for me. When I modified the formula the way he suggested, the problem was fixed. Thanks to both of you for your assistance.