ERROR.TEXT

Converts an error value into a corresponding text string.

Syntax:

ERROR.TEXT(error_value)

error_value is required. It is the error value or a cell reference containing an error value.

Example:

If A1 contains “#NUM!”:

ERROR.TEXT(A1)

returns “#NUM!”

Error_value:

Result:

#NUM!

Application:

Calculating Employee Bonuses

Let's imagine you have a spreadsheet to calculate employee bonuses. The bonus is calculated as a percentage of their sales. However, there are some data entry errors that can occur, such as a sales value being "Not Applicable" or an employee ID being incorrect.

Table:

	Employee ID	Employee Name	Sales ($)	Bonus %
	A	B	C	D
1	101	Alice	150000	5%
2	102	Bob	200000	5%
3	103	Charlie	180000	5%
4	104	David	#N/A	5%
5	105	Eve	120000	#REF!

Now, you want to calculate the bonus for each employee in a new column. The formula to calculate the bonus would be:

C2*D2

Let's analyze the ERROR.TEXT function's role here:

If you simply use the formula C2*D2, the result for David and Eve would be an error (#N/A and #REF!).

To make the spreadsheet more user-friendly, you can use the IFERROR function combined with ERROR.TEXT to provide a more specific message about the error.

Formula Breakdown:

In cell E2 (Bonus column), you would use the following formula:

IFERROR(C2*D2, "Calculation Error: " & ERROR.TEXT(C2) & " " & ERROR.TEXT(D2))

How the Formula Works:

IFERROR(value, value_if_error): This is the main function. It tries to calculate the value. If it results in an error, it returns the value_if_error.
- value: C2*D2. This is the bonus calculation.
- value_if_error: "Calculation Error: " & ERROR.TEXT(C2) & " " & ERROR.TEXT(D2). This is the custom error message.
ERROR.TEXT(C2): This part of the formula checks the value in cell C2.
- For Alice, Bob, and Charlie, C2 contains a valid number, so ERROR.TEXT will return a blank value.
- For David, C2 contains the #N/A error.
ERROR.TEXT(D2): This part checks the value in cell D2.
- For Alice, Bob, and Charlie, D2 contains a valid number, so ERROR.TEXT will return a blank value.
- For Eve, D2 contains the #REF! error.

Resulting Table with the Formula:

	Employee ID	Employee Name	Sales ($)	Bonus %	Bonus
	A	B	C	D	E
1	101	Alice	150000	5%	5551800000
2	102	Bob	200000	5%	7402400000
3	103	Charlie	180000	5%	6662160000
4	104	David	#N/A	5%	Calculation Error: #N/A
5	105	Eve	120000	#REF!	Calculation Error: #REF!