IFERROR

Returns a value if error is found

Syntax:

IFERROR(value, value_if_error)

IFERROR tests a value and checks if an error occurred. It returns value if an error did not occur, otherwise returns value_if_error.

Example:

If A2 contains 0 and A1 contains 1:

IFERROR(A1/A2, "Error: divisible by zero")

returns Error: divisible by zero

If A2 contains 1 and A1 contains 1:

IFERROR(A1/A2, "Error: divisible by zero")

returns 1

	A	B
1	1	Error: divisible by zero
2	0

Application:

Here's an application of IFERROR:

Scenario: Calculating Sales Commission with Potential for Missing Data

Imagine you have a sales tracking sheet. You want to calculate the commission for each salesperson, but sometimes the "Sales Target" might be left blank or contain text instead of a number, leading to errors when you try to divide.

Without IFERROR (The Problem):

Let's say your data looks like this:

	Salesperson	Actual Sales ($)	Sales Target ($)	Commission Rate (%)	Commission ($)
	A	B	C	D	E
1	Alice	$10,000.00	12000	10%	0.083333333
2	Bob	$8,000.00	0	10%	#DIV/0!
3	Charlie	$15,000.00	18000	10%	0.083333333
4	David	$7,500.00		10%	#DIV/0!
5	Eve	$11,000.00	N/A	10%	#VALUE!

Explanation of Errors:

Bob's Commission: B2/C2*D2 would result in a #DIV/0! error because C2 (Sales Target) is 0, and you cannot divide by zero.
David's Commission: B4/C4*D4 would result in a #DIV/0! error because C4 (Sales Target) is blank, which is treated as 0 in division.
Eve's Commission: B5/C5*D5 would result in a #VALUE! error because C5 (Sales Target) contains text ("N/A") and cannot be used in a mathematical calculation.

With IFERROR (The Solution):

Now, let's use IFERROR to make this table much cleaner and more informative. We'll say that if there's an error in calculating the commission, we want to display "Missing Data" instead of an error message.

Here's the updated table with the IFERROR function in the "Commission ($)" column:

	Salesperson	Actual Sales ($)	Sales Target ($)	Commission Rate (%)	Commission ($)
	A	B	C	D	E
1	Alice	$10,000.00	12000	10%	0.083333333
2	Bob	$8,000.00	0	10%	Missing Data
3	Charlie	$15,000.00	18000	10%	0.083333333
4	David	$7,500.00		10%	Missing Data
5	Eve	$11,000.00	N/A	10%	Missing Data

How the IFERROR function works:

The syntax is IFERROR(value, value_if_error):

value: This is the formula or expression that you want to check for errors. In our example, it's B1/C1*D1 (or the corresponding cell for each row).
value_if_error: This is what you want to display if the value argument results in any error (like #DIV/0!, #N/A, #NAME?, #NULL!, #NUM!, #REF!, or #VALUE!). In our example, we chose "Missing Data". You could also use "" for a blank cell, 0 for a zero, or any other text or number.