IF function not working with numbers? A recent question from a client, it is important to understand what a number is in Excel and what certain Excel functions do to numbers.
Table of contents
YouTube IF function not working with numbers
IF function not treating a number as a number
Shown below a simple exercise to extract a number from an ID, and in this case, check if it is bigger or less than 5 as this indicates gender.
Although we know the 1st and 3rd ID’s should result in the answer Female, it is showing as Male.
In column B we have used a MID function to pull out the 7th character. The key here is that MID (and LEFT, RIGHT and CONCATENATE) all convert themselves into ‘text’, even if it only contains a number. When we say text, it does not turn a 5 into the word ‘five’. It sees the 5 as a “5“ (the quotes are the important part).
When Excel sees quotes (“”) around anything, even a simple number, it treats it as text and so 5<>”5″.
So in the below image, if we use the Function Wizard (a great tool to show what Excel is actually seeing and not what you think it is seeing), you will see that Excel says FALSE to the logical test of 0<5. This is because cell B2 is “5” and not 5 (so the IF function will not work with numbers).
Correcting IF functions to see numbers
Depending on what you are trying to do you either need to change the text “5” into the number 5 or else change the 5 in the IF function into a “5”.
Using VALUE to make Excel see numbers
The VALUE function does exactly what we want in that it converts a “5” into a 5. Again the function wizard shows this perfectly. Below note that when Excel looks at the B3 cell it sees a “5”. The VALUE function converts it into the number 5.
Now the original IF function will work.
Use Quotes to turn a number into ‘Text’
Alternatively we can change our IF function to rather do the logical test against “5” rather than 5. As shown below, putting the quotes around the 5 allows Excel to compare like for like and give you the answer you expect.
Warning re Excel and numbers
The above concept is important. Just because you think a cell seems to contain a number, does not mean Excel sees it as a number. There are lots of reasons Excel confuses a number including
- using certain functions (LEFT, RIGHT, MID)
- data imports being formatted as text
- regional settings confusing Excel (expecting a . for decimal but it receives a ,)
Always do a few test checks on your formula to make sure they are working.
Related
Turn TRUE FALSE into numbers in Excel