=if returns false when it should return true

[Rusty Shackleford]

Let me start off by admitting that I am a novice. I created a spreadsheet to work as an envelope budget system. The way envelope budgeting works is money goes into one account (i.e. a checking account). Although the money is held in a single account it is virtually distributed among several "envelopes" thus earmarking money for a particular purpose. As this is the case, the balance of the one account in which all of the money is actually held should be equal to the sum total of all the virtual "envelopes." To ensure that my account balance and my envelope balance always agree I used an =if that was supposed to change the title and color of my heading if ever the two numbers did not agree. The formula is pretty straight forward. It is: =IF(J7=K7,"Checking Account Register","SYSTEM OUT OF BALANCE"). Most of the time it works great. However, occasionally it returns false when it is actually true. Each time it has incorrectly evaluated the data cell K7 is supposedly less than cell j7. However, that is simply not the case. I can look at both values and see that they are in fact the same. What is even more perplexing is that it will only return false for a certain range of numbers. For example: if the account balance is $871.40 everything is fine, but if the account balance is anywhere from $871.41 to $1000.01 it returns false in spite of the fact that it is true. These are just example numbers. In reality it has malfunctioned several times, each time with a different range of numbers. At first I thought it might have something to do with formatting in cells j7 and k7, but that is not the case. The values never go beyond 2 decimal places, because it is currency and it only adds and subtracts. So that's it. I don't know what I am doing wrong, and I have talked to a lot of people, but no one has been able to solve this problem. Anyone have any ideas?

 

[NARAYANK991]

Hi Rusty ,

This is a common problem ; two values , which are decimal values , should never be compared for exact equality ; as far as possible , always include a tolerance.

Thus , if you wish to compare a value in A1 , which may be 871.41 , with another value in B1 , which may or may not be 871.41 , use a check such as :

=IF(AND(A1 <= B1 + 0.0001 , A1 >= B1 - 0.0001) , "Match" , "Do not Match")

What this does is , it checks for whether A1 is as close to B1 as doesn't make much of a difference ; even if there are differences in Excel's representation of the decimal numbers , unless the two values really differ , they will satisfy this tolerance check.

This is especially so if either or both of the values , in A1 and B1 , are calculated values , coming from other formulae.

Narayan

 

[Rusty Shackleford]

Thanks, Narayan! The formula works perfectly. One more question. I had the cells containing the title "register" set up with conditional formatting to change color when the j7 and k7 were not the same value. The =if now works perfectly, but the conditional format has the same problem. For the conditional format I simply set it up to change color when j7>k7 or when K7>j7. Is there a better way to accomplish this as well?

 

[NARAYANK991]

Hi Rusty ,

Use the same technique in the CF formula ; since you want it to change color when J7 is not equal to K7 , use something like this :

=OR(J7 > K7 + 0.0001 , J7 < K7 - 0.001)

This will be true if J7 differs from K7 by more than 0.0001.

The difference between the two will not be this much if the two are really equal.

Narayan

 

[Rusty Shackleford]

Thanks, Narayan! Both solutions work beautifully. I've learned something new.