If you already make use of the IF function you may have come across the situation where IF does not seem to work, particularly when you are comparing to True and False answers.
Table of contents
YouTube IF not working with TRUE/ FALSE in Excel
Why is the TRUE ignored in Excel
So below in row 14 (cell B14) it clearly says true and in the Formula we have typed B14=”TRUE”, yet Excel wants to give us a zero answer as if it is false.
The reason for this is that the ‘words’ TRUE and FALSE are actually the numbers 1 and 0 in Excel (in most cases). So when you are creating the logical test in the IF function, you must treat it like a number i.e. no inverted commas.
The syntax should be
=IF (B14=TRUE, $C$7,0 ) – notice no inverted commas
You can even test it by using a number rather i.e.
=IF (B14=1, $C$7,0 )
The ‘number’ words happen if you get excel to spit out a TRUE/ FALSE answer. For Excel it is a 1 or 0 .
It also happens when you type it into a cell. The clue would be if I type in True (notice the lowercase) and when I click enter it changes on its own to TRUE (all uppercase). It also typically goes from left aligned to center aligned.
Where you need to be careful is if someone has typed true in some inverted commas. Then Excel sees it as a word in which case your IF needs to have inverted commas.
So all in all quite a difficult thing to spot. A way to possibly check is to create a calculation that multiplies the ‘word’ with a one. As shown below, the TRUE FALSE that it sees as numbers turn into numbers (1 x 1= 1 and 0 x 1= 0) and the ones that are actually words give us a #VALUE error message (Excel asking you why you are trying to multiply with words).
Once you know what you are dealing with, you will get the right version in you IF function i.e.
NO INVERTED COMMAS- when Excel recognises it as a number (0= False, 1=True)
and
INVERTED COMMAS- when Excel sees it as we do, which is as a word.
Want to learn more about Microsoft Excel? If you prefer attending a course and live in South Africa look at the Johannesburg MS Excel 3 Day Advanced Course or the Cape Town MS Excel 3 Day Advanced training course. If you prefer online learning or live outside South Africa, look at our online MS Excel training courses.
Related
IF function not working with numbers