Showing errors to the end-user is quite ugly, so we should be careful in protecting our formulas so that you don’t display to the user the nasty
#Value!. There are a lot of
IS functions, but I want to focus on the “ISERROR” function since it’s the most complete and used. The function will return true or false depending on there’s an error in the argument or not.
It follows a simple pattern.
- Value to check
Let’s check the suspicious function CHOOSE and pick something outside the array:
=CHOOSE(3,“banana”, “orange", “apple”) The result will be #Value!
Super ugly. But what if we use the “ISERROR” function to show something different?
==ISERROR(CHOOSE(3,"banana","orange","apple")) The result will be true
Then a simple “IF” will help us showing something more helpful.
=IF(ISERROR(CHOOSE(4,"banana","orange","apple")),"Not so good value","Good value") This will return "Not so good value" since we're trying to pick something ourside the bounds.
This function will only return true or false. You need to parse the value yourself to show something different to the user.
- You should always consider using this function if you have nesting of functions and dealing with values in columns (since you can’t control the values inside the columns).
- Always display a decent message to the user. It doesn’t make sense to replace the default
#Value!error message with “Error,” for example. The user will not have more information to go on. Provide details so that either you can use it to fix the issue, or the user to correct the data.
- You can encompass this formula in pretty much everything, but don’t go overboard. It doesn’t make sense to check,
Back to the SharePoint’s list formula reference.