The sample dataset records person’s name and gender.
Solution 1 – Remove Leading Space
A leading space is the most common reason why a formula doesn’t work properly. In the sample dataset, the IF function returns Yes for Males and No for Females. But the second output is returning the wrong result.
There’s nothing wrong with the formula.
There is a leading space in Cell C6.
Solution:
- Delete the space or use the TRIM function before the cell reference as in the image below.
Method 2 – Change Wrong Delimiter
Another common issue is using the wrong delimiter or character.
Solution:
- Delete the incorrect delimiters and use commas between the arguments.
Read More: How to Use Excel IF Function with Range of Values
Solution 3 – Change Cell Format
Entering numbers in Text format creates an error in many formulas.
There are no mistakes in the formula.
The numbers are entered in a Text format instead of a Number format.
Solution:
- To convert the numbers select the cells and click on the error icon, then select the Convert to Number option.
The formula will now return the correct result.
Solution 4 – Remove Unusual Characters from Argument
The sample dataset returns TRUE for married persons and FALSE for unmarried persons. While inserting text in a formula double quotation marks (“”) are frequently used but some values don’t need it.
The reason for this error is the double quotation marks in the logical part, C5=”TRUE”. Excel considers the value TRUE as the number 1 and FALSE as the number 0.
Solution:
- Delete the double quotation marks from TRUE or FALSE in the formula.
Read More: How to Make Yes 1 and No 0 in Excel
Download Practice Workbook
You can download the free Excel workbook from here and practice independently.
Related Articles
<< Go Back to Excel IF Function | Excel Functions | Learn Excel
Get FREE Advanced Excel Exercises with Solutions!