Last week I wrote about IF. There are three other formula that start with IF, I will cover two of those here, IFERROR and IFNA.
IFERROR
IFERROR is a very helpful function. You can use it to sit in front of another function and deal with errors before they become a problem.
For example, if you are doing a division calculation and some of the numbers may be zero, without IFERROR you will get a #DIV/0! error. To display something more useful, you can use the IFERROR.
=IFERROR(value, value_if_error). You can see that you need to supply two inputs. The value that is being calculated (which is often a formula or function) and the value you want to display if the first input gives an error.
For example, =IFERROR(5/0, “Invalid input”) or more usually =IFERROR(C1/D1, “Invalid input”)
Here we can see the average being worked out. For the player with zero times out, without IFERROR, the average shows as an error. With the IFERROR we can replace the error with whatever we want.
IFNA
IFNA is almost a subset of the IFERROR function. It works in the same way, but only works on the #N/A error. #N/A errors are most associated with lookup functions not finding what they are looking for, so to have an error trap just for that is useful.
With the IFNA function wrapped around the VLOOKUP function, instead of getting #N/A we can specify what should happen if the player name is not on the player list. If you use XLOOKUP, you can specify what to do with a missing value inside the XLOOKUP function, so this lessens the need for the IFNA function.
Now you can use IFERROR and IFNA to make your spreadsheets easier to use!