Today I have been asked one question about IF formula statement in Excel. One person is trying to check cell value using IF and getting return FALSE but he is claiming it should be TRUE. Because this cell contain same value as he is comparing with. Like this is holding value 2 and he is trying to check with 2 it self but getting FALSE.
Before going to write reason one small explanation of IF statement in EXCEL. IF block is looks like below:
IF (Boolean Comparison, TRUE part, FALSE part)
And it should start with = symbol because its formula not value.
At below I am showing one example that is 2 checking with 2 but returning FALSE. This is not bug of Excel, you are doing comparison wrong way.
Example
Picture 1
Wow IF is comparing with 2 but getting FALSE, How?
Solution and Explanation
Check in formula A1 is comparing with “2” not 2. Here 2 has placed inside quotation mark (“”) and this mark actually converting 2 into string values not number. But your cell is holding value in Number. As a result it is showing FALSE.
Values and Formula in Other Way
Now I am changing cell value slightly.
Picture 2
Oh great, its now showing TRUE!
Might be you are thing how? It reason is in below picture see it.
Picture 3
Check here I have placed a single quotation mark (‘) before the number. By placing this mark the value has converted to String type and IF can compare properly, that is comparing value with same type String and String.
In other programming language like C this type comparison should throw error message but Excel don’t do it. Excel just try to check it and found Number is comparing with String so these never be TRUE and it throws FALSE.
Solution for Number values
Proper Number type IF comparison is as below:
Picture 4
This is actually comparing number type value 2 and returning TRUE.
TIPS: Check in Picture 1 and Picture 2, A1 cell value 2 has changed its alignment. It has done automatically, in real life we starting writing any line from Left to Right but any Number like Currency value from Right to Left by following weighted number system process, 10^0, 10^1, 10^2 and so on (Right to Left). So when I am placing Single Quotation Mark (‘) it is converting Number to String and changing position in cell.
