How to Use IF and IFERROR Combined in Excel (3 Examples)

Example 1 – Calculate the Sales Bonus

We have two columns containing Sales Target and Sales Achieved for some products. We’ll check in Column E if the achieved sales have met the sales target and return a statement.

Steps:

Insert the following formula in cell E5.

=IFERROR(IF(D5>=C5,$C$15*(D5-C5)/C5,"Not Applicable"),"Sales Target is not Correctly inserted")

Press Enter.

Drag the Fill Handle icon to fill the other cells with the formulas.

How Does the Formula Work?

IF(D5>=C5,$C$15*(D5-C5)/C5,”Not Applicable”) checks whether the value of cell D5 is greater or equal to that of C5. If it is greater, then it returns the value of cell $C$15*(D5-C5)/C5 else it will return the “Not Applicable” text.

The IFERROR function checks the first argument, whether it holds any errors or not. If it does, then the IFERROR function returns “Sales Target is not Correctly inserted” text. If not, it returns the value of the cell.

Example 2 – Determine Achieved Sales Through IF and IFERROR

We’ll hardcode sales thresholds and return a percentage of the sales as income.

Steps:

Use the following formula in cell D5.

=IFERROR(IF(C5<32500,C5*16%,IF(C5<73500,C5*26%,C5*29%)),0)

Press Enter.

Drag the Fill Handle icon to fill the other cells with the formulas.

How Does the Formula Work?

IF(C5<32500,C5*16%,…) checks whether the value of cell C5 is smaller that of 32500. If it is smaller, then it returns the value of C5*16% else it moves on to the next portion of the formula

IF(C5<73500,C5*26%,C5*29%) checks whether the value of cell C5 is smaller that of 73500. If it is smaller, then it returns the value of C5*26% else it returns the value of C5*29%.

The IFERROR function checks the first argument, whether it holds any errors or not. If it does, then the IFERROR function returns 0. If not, it returns the value of the cell.

Example 3 – Track Project Progress

We have a list of projects and want to track progress for each person.

Steps:

Click the Developer tab on your ribbon.
Select Visual Basic from the Code group.

Go to the Insert tab on the VBA editor.
Click on Module from the drop-down.

A new module will be created.
Select the module if it isn’t already selected.
Insert the following code in it.

Sub track()
Range("E5").Select
    ActiveCell.FormulaR1C1 = _
        "=IFERROR(IF(RC[-1]>=RC[-2],(RC[-1]-RC[-2])/RC[-2],""Completed""),""Incorrect values"")"
    Range("E5").Select
    Selection.AutoFill Destination:=Range("E5:E12")
    Range("E5:E12").Select
End Sub