How to Use IF IsError Together?

Author Zaheer    Category Guides     Tags ,

In Microsoft Excel, IF function is the most efficient and versatile function. This is also available in other worksheet or spreadsheet programs. This is one of the most used and helpful functions. It aims to test and see if a condition is false or true. If it is true, this function aims to do a single thing. If it is in false condition, it will do something more. Comparison operators are often used to the logic test for its comparison between two values.

Performing calculations:

This IF function performs different calculations that depend on what function is used, returning its true value or not. This is the second option upon using the IF function of Excel having to carry out the various calculations depending on the tested condition of false or true.

In Microsoft Excel, IsError function is used in checking for the error values. The syntax for this function is “IsError(value)”. The value argument is the important because this is the value that the user wanted to test. IsError function can be applied to Microsoft Excel XP, Excel 2003, Excel 2000 and Excel 2007.

IsError function is oftentimes used when there are errors that occur in your spreadsheet containing numerous formulas that is not calculated properly. This function, combined with the IF function are used in defaulting the value of the cell when errors occurred. Through this, your formula can evaluate properly even without the involvement of the user.

Names of First and Last Extraction:

The extraction or the splitting of the first names and last name components is the most frequent task that is encountered by many people when working with the data lists in Excel. For instance, the user have to put the full names such as “Pearson, Michael D” in column A thus it needs to put the last name in column B, the first name is in column D and in column E is the middle initial.

All the procedures and functions assuming your data is in the format of “Last name, first and middle name”.

Worksheet Function:

These are the following number of worksheet functions in splitting full names into the last name and first name components.

Returning the full name’s last name in column A2, you can use this formula, “LEN(A2), Find(“,”,A2,1) -1),“left(A2, IF(IsError(find(“,”,A2,1))”

Returning the full name to first in column A2, this formula is applicable “=TRIM(IF(Is Error(FIND(“,”,A2,1).

Returning the full name to middle name, try this formula, “=TRIM(right(A2,LEN(A2) – IF(IsError(FIND(“ “, A2,FIND(“ “,A2,1) + 2))).

VBA Procedures:

VBA functions are used to split full names into the first name and last name components. This instruction accepts the full name and returns the four elements of an array – the last name or surname, the first name, middle initial, and suffix. Therefore, through this function, you need to enter these names through an array formula.

In complex spreadsheet of Microsoft Excel, the user wanted to hide any errors obtained in the functions. It doesn’t only aims to make your spreadsheet looks nice but it aims to hide errors such as #REF, #VALUE and #N/A. Through this, your spreadsheet will look more professional. However, numbers of errors will blow up on other function; therefore, it will produce and give more errors. Thus, your spreadsheet or worksheet will become not viable once there is some dependent function in your spreadsheet.

Nevertheless, never use IF IsError in suppressing the errors when there are really no errors, thus you have to use these functions only when there is an error that has occurred. If the function is based on the VLOOKUP value of existence but there is no guaranteed existence of VLOOKUP function value, therefore, it is not necessary to use the functions of IF IsError. When your spreadsheet is in aVLOOKUP value, it is not suitable to suppress and trace the error. The IF(IsError) functions covers generally the potential errors such as #NULL, #VALUE, #REF, #NUM!, #NAME?, #N/A or #DIV/O!.

IfError is introduced along with the Microsoft Excel 2007 and in Microsoft Excel 2003, IsError function is used. Microsoft Excel 207 IfError is an extreme user-friendly and this function is the performance improvement over the Microsoft Excel 2003 IsError. Even though, they have several differences among them.

However, IfError is in replacement but IsError is inevitable because of the following:

  • The creation of the Microsoft Excel models similar to Excel2007 and Excel 2003.
  • The requirement of “IF(ISERROR(arg1),arg2,arg3)” as a substitute for “IF)ISERROR(arg1),arg2,arg3)”.

Post comment