This function’s potential for complexity is both a curse and a blessing. It allows you to create sophisticated formulas, and at the same time, it makes it easier for you to get lost in the spiral and end up with errors and a broken formula.
Understanding the IF Function
The most important step in clearing your IF function of errors is to understand the function and its syntax. IF is one of the most popular functions in Excel. The IF function runs a logical test, and then returns one value for when the logical test’s result is true, and another for when the test’s result is false. So then, the IF function is made up of three pieces:
logical_test value_if_true value_if_false
When you put these pieces together, you get the IF function:
Where logical_test is your condition, value_if_true is the value the function returns if the condition is met, and value_if_false is the value returned if the condition is not met.
IF Function Example
As an example, consider the spreadsheet above. We have the scores of two different students on a test, and we want to write a formula that tells us if these two students have the same score. The formula would be as below:
This formula calls on the IF function and runs the logical test of B2=B3. If the test result is positive, then the function will say that the two students have the same score. Else, if the test result is negative, then the function will say that the two students didn’t get the same score.
Rather than typing in the names of the students, in this example, we’ve referred to the cells that contain their names. This way the formula can still function properly even when the names change.
Nested IF Function Example
The spiral of nested IF functions is very easy to get lost in, but you can master it by understanding its structure. In this sample list, we have the names and scores of some random students in a test. We also want to establish a tier system. Scores higher than 90 get S+, 80 to 90 get S, 70 to 80 get A, and then a letter goes up with every 10 scores.
We can do this with a single formula, a nested IF formula to be exact. The formula would be as below:
The initial logical test in this formula is to check if the score is higher than 90. If it is, the function returns the value S+. So far so good, right?
But what starts the chain of IF functions, is that the value_if_false is an IF function itself. So, if B2 wasn’t higher than 90, the formula will then move on to the next IF function, to check and see if B2 is higher than 80. If this test returns true, then B2 is certainly between 80 and 90 and so the formula returns the value S.
On the other hand, if the second test returns false as well, then the formula moves on to the next IF function, where it tests B2 to see if it’s higher than 70. The formula will keep on testing B2 until it gets a positive result from one of the logical tests, or it reaches the final IF function.
Fixing the IF Function
In some cases, the error you’re getting is because you’ve messed up the IF function itself. This means that there’s either a mistake in the function syntax or the cells that it’s referring to.
The first thing you should do to make sure your function is healthy is to check out the commas and the parentheses. Remember the IF function’s syntax, and know that the function moves on to the next parameter with every comma. Also, be sure to close your parentheses right after you’ve defined value_if_false.
Other than the IF function itself, things can go wrong with the nested functions. You can nest any function inside an IF function, in either the value_if_true or _value_if_false parameters. The key here is to know the functions that you’re using so that you don’t mess up the syntax or surprise the function with invalid input.
When there’s something wrong with your formula, Excel will inform you with an abbreviation of the error. By knowing what each means, you’ll have less trouble fixing them. Here are four of the most common errors associated with the IF function.
Keep in mind that errors are relative. For instance, a formula could work flawlessly on a specific pair of numbers, but not work with another pair of numbers. This all depends on your formula, its range, and its domain. Here’s an example.
In this example, we have the number of men and women in a park on different days. We also have a column to indicate whether the women-to-men ratio was bigger than 1 on that day. This can be achieved with the IF function.
Here, you need only use the autofill handle to check the ratio every other day. Note that the formula works fine in all rows, except one. The formula has hit a #DIV/0! error, because it was trying to divide a number by zero.
Fixing Errors and Setting Conditions
IF is a popular function that conducts a logical test, and then returns two values depending on the results of that test. The IF function allows you to build many complex formulas in Excel, but it’s also possible to lose sight of what exactly is happening in the formula and get bombarded with errors.
The key to using the IF function without any errors is to first understand it and its syntax. If you’re nesting other functions inside an IF function, then you should make yourself familiar with those functions as well.
