Brought to you by
accountingcpd

Award-winning CPD courses.

Save content
Have you found this content useful? Use the button above to save it to your profile.

Excel Errors and how to solve them quickly

18th Oct 2018
Brought to you by
accountingcpd

Award-winning CPD courses.

Save content
Have you found this content useful? Use the button above to save it to your profile.

excel

At one point or another, we’ve all had to spend valuable time clicking between various Excel sheets to find a hidden error. These can be hard to spot and errors such as #DIV/0! can cause your entire worksheet to stop working. On the bright side, there are a number of ways you can check for and manage errors using Excel.

One way to do this is by using certain functions that tell Excel to return an alternative value in place of an error. You can then use the alternative value within other formulas. These include:

  • The ISNA function – returns TRUE when the error type is #N/A
  • The ISERR function – returns TRUE for all error types except #N/A
  • The ISERROR function – returns TRUE for all error types
  • The ERROR.TYPE function – returns a number from 1-8 that corresponds to an error-type, as below
  1. #NULL!
  2. #DIV/0!
  3. #VALUE!
  4. #REF!
  5. #NAME?
  6. #NUM!
  7. #N/A
  8. #GETTING_DATA

Want to assign your own value to an error? Excel makes this easy too.

IFNA allows you to specify a value to be returned if a formula detects an #N/A error.

Use:

=IFNA(value, value_if_na)

value is the data range you are checking for an #N/A error, and "value_if_na" is the value you wish to return instead of the error.

For example, to change the value returned on a #N/A error to 5, use:

=IFNA(value, 5)

 

IFERROR lets you define a value to be returned if a formula detects any error type.

Use:

=IFERROR(value, value_if_error)

Where value is the data range you are checking for an error, and "value_if_error" is the value you wish to return instead of the error.

If you would like to change the error to a number, such as 0, to make sure your worksheet continues working in the case of any error arising use:

=IFERROR(value, 0)

 

Want to develop your Excel skills further? Check out excel@acpd, the only excel resource designed specifically for accountants.

accountingcpd.net provide award winning online CPD for accountants to help you succeed and develop in your career.

 

Tags: