HomeApplicationOffice information

IFERROR function in Excel, formulas, and usage

The IFERROR function in Excel is a basic Excel function, a logical function that returns the desired value when its condition produces an error. While we calculate data in Excel, it is inevitable to get errors when doing it. Excel errors can occur in any function like Vlookup, . with many different error codes like the #VALUE date error in Excel.

The IFERROR function will help us detect errors in the data sheet and handle them. The value returned will depend on the user specified when the function is wrong. The following article will guide you how to use the IFERROR function in Excel.

  1. How to use the SUMIF function in Excel?
  2. 3 ways to calculate totals in Excel
  3. How to use the Hlookup function in Excel?
  4. How to fix Errors were detected while saving file in Excel 2010

1. Excel IFERROR function formula

The IFERROR function has the formula is = IFERROR (value, value_if_error) . Inside:

  1. Value: This is the argument to check. Can be calculations, formulas, Excel functions, are required values.
  2. Value_if_error: This is the return value that specifies if the formula fails.
  3. The Value_if_error value you can declare is a space (''), equal to 0, an example message line 'Error result', .
  4. Value_if_error value is returned when the formula returns values ​​with error types such as: # N / A, #VALUE !, #REF !, # DIV / 0 !, #NUM !, #NAME? or #NULL !.

2. How to use the IFERROR function in Excel

Download the IFERROR function exercise in Excel with the answer below.

  1. Download the Excel IFERROR function exercise

Example 1: Calculation of average score of the error

We have the following subjects transcript and are required to average an average of 3 subjects through the calculation, without using the AVERAGE function.

IFERROR function in Excel, formulas, and usage Picture 1

First we enter the average calculation as normal and then press Enter to produce the result. Then drag the first result cell down to the remaining cells.

IFERROR function in Excel, formulas, and usage Picture 2

It can be seen that the data cells below have an error, so the error results as shown in the picture. If we leave it like that, the data sheet is pretty bad. Instead of such an error, you can use the IFERROR function to replace it with another value.

IFERROR function in Excel, formulas, and usage Picture 3

We enter the formula as = IFERROR ((C2 + D2 + E2) / F2, "Wrong calculation") and press Enter.

IFERROR function in Excel, formulas, and usage Picture 4

When the data in each cell is correct, display the result. You scroll down to the remaining cells to check. If a cell has an error, it will produce the following calculation error.

IFERROR function in Excel, formulas, and usage Picture 5

Example 2: Refining% of items sold

We have the following table summarizing the quantities of stock sold and stock taken. We will calculate% in Excel by dividing the stock sold to the inventory to find the% value we are looking for.

IFERROR function in Excel, formulas, and usage Picture 6

Enter = C2 / D2 and press Enter and produce the result.

IFERROR function in Excel, formulas, and usage Picture 7

In the table there are some values ​​in the sold cell and the warehouse total error so when calculating% has displayed an error as shown below. We can also replace this error with another value.

IFERROR function in Excel, formulas, and usage Picture 8

In the first cell enter the formula = IFERROR (C2 / D2, 'Wrong error entered') and press Enter.

IFERROR function in Excel, formulas, and usage Picture 9

With the correct input data, the results are still displayed. As for the wrong input box will replace with the message as below.

IFERROR function in Excel, formulas, and usage Picture 10

So the Excel IFERROR function is very easy to use. We just need to enter the function name and then the formula and the replacement value for the error is okay. Users can enter alternate values ​​as they like.

I wish you successful implementation!

4.3 ★ | 3 Vote

You should read it

May be interested

  • How to create an Excel formula in Notepad ++How to create an Excel formula in Notepad ++
    when entering excel formulas in notepad ++, there will be additional features highlighting, highlighting, and suggestion. the following article will guide you how to set up an excel input formula in notepad ++.
  • Difference between function and formula in ExcelDifference between function and formula in Excel
    one of the core features of excel is the ability to perform calculations using functions and formulas. although these terms are often used interchangeably, they have distinct meanings.
  • Excel date function - Usage and examplesExcel date function - Usage and examples
    the date counting function in excel is one of the most effective functions of this software. because excel usually works in the field of statistics and calculations, it is extremely necessary to record dates. to help you understand this function, let's come to the following article of tipsmake.
  • How to use MID functions to get strings in ExcelHow to use MID functions to get strings in Excel
    mid function in excel is a function that takes the middle character string corresponding to the value that the user requires to perform.
  • OR function in Excel, how to use the OR function, and examplesOR function in Excel, how to use the OR function, and examples
    the following article details the meaning and usage of the or - logic function in excel. description: the or function is a function that returns the logical value in excel.
  • PMT function in Excel - Usage and examplesPMT function in Excel - Usage and examples
    the pmt function is one of the built-in financial functions of the excel software used to calculate the payment for a loan based on regular payments and a constant interest rate. the pmt function is not only useful for businesses, but also very practical for users if you want to calculate a loan.
  • How to color formula cells in Excel automaticallyHow to color formula cells in Excel automatically
    coloring formula cells in excel helps us quickly identify cells that use formulas in long data tables.
  • How to use the Search function in ExcelHow to use the Search function in Excel
    the search function in excel is used to search for the position and order of a text string, so you can find the position of the word or the data you need.
  • Match function in Excel - Usage and illustrative examplesMatch function in Excel - Usage and illustrative examples
    the match function in excel finds a specified item in a specified range and, returns the relative position of that value in this range. function syntax and usage syntax: = match (lookup_value, lookup_array, [match_type]). in which: - match: is the function name. - lookup_value: is the value to search, be it text or numeric.
  • How to delete quick recipe on ExcelHow to delete quick recipe on Excel
    in addition to locking excel formulas, we can delete formulas and retain results, or delete all cells with formulas and results.

Office information