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.

Usage

It follows a simple pattern.

  1. 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.

Limitations

This function will only return true or false. You need to parse the value yourself to show something different to the user.

Recommendations:

  1. 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).
  2. 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.
  3. You can encompass this formula in pretty much everything, but don’t go overboard. It doesn’t make sense to check, ISERROR("Manuel")for example.

Sources:

Microsoft’s IS Function Reference

Introduction to SharePoint formulas and functions

Back to the SharePoint’s list formula reference.

Photo by James Lee on Unsplash

Manuel Gomes

I'm a Project Manager with experience in large projects and companies. I've worked in the past for companies like Bayer, Sybase (now SAP) and I'm currently working for Pestana Hotel Group.

View all posts by Manuel Gomes →

Leave a Reply

%d bloggers like this: