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

Get FREE Advanced Excel Exercises with Solutions!

If you are looking for some special tricks to know how to use IF and IFERROR combined in Excel, you’ve come to the right place. We’ll show 3 suitable examples to use IF and IFERROR combined in Excel. This article will discuss every step of the examples. Let’s follow the complete guide to learn all of this.


How to Use IF and IFERROR Combined in Excel: 3 Suitable Examples

The following section demonstrates the use of IF and IFERROR functions in Excel using three practical and tricky examples. The IF function checks whether a condition is met, and returns one value if TRUE, and another one if FALSE. The IFERROR function in excel can generate a custom value as a result if there is any error from the formula. If there is no error it will return the regular result. You should learn and apply these to improve your thinking capability and Excel knowledge. We use the Microsoft Office 365 version here, but you can utilize any other version according to your preference.


1. Calculate Sales Bonus

In our dataset, there are two columns containing Sales Target and Sales Achieved for some products. By using the IF and IFERROR  functions, we’ll check and show statements in Column E if the achieved sales have met the sales target or not. Follow the below steps.

Dataset to Use IF and IFERROR Combined in Excel

📌 Steps:

  • First, write down the following formula in cell E5.

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

  • Next, press Enter.
  • Therefore, you will get the following output.

calculate sales bonus to Use IF and IFERROR Combined in Excel

  • Next, drag the Fill Handle icon to fill the other cells with the formulas.
  • Consequently, you will get the following sales bonus.

🔎 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.

Read More: Excel ISERROR vs IFERROR Functions


2. Determine Achieved Sales Through IF and IFERROR Combined

In this section, we will show you how to calculate achieved sales in Excel. Here, we will use nested IF and IFEROOR functions to do the task. Let’s walk through the following steps to complete the task.

 Use IF and IFERROR Combined in Excel

📌 Steps:

  • First, write down the following formula in cell D5.

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

  • Next, press Enter.
  • Therefore, you will get the following output.

determine achieved sales to Use IF and IFERROR Combined in Excel

  • Next, drag the Fill Handle icon to fill the other cells with the formulas.
  • Consequently, you will get the following achieved sales.

🔎 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.

Read More: How to Use Conditional Formatting with IFERROR in Excel


3. Track Project Progress

If you want to track project progress using the VBA code in Excel, you need to use the help of VBA. This is Microsoft’s event-driven programming language called Visual Basic for Applications (VBA). To use this feature you first need to have the Developer tab showing on your ribbon. Click here to see how you can show the Developer tab on your ribbon. You can then use this detailed Excel VBA code to track project progress. Here, we use IF and IFEROOR functions in conjunction to track project progress.

track project progress to Use IF and IFERROR Combined in Excel

📌 Steps:

  • VBA has its own separate window to work with. You have to insert the code in this window too.
  • To open the VBA window, click the Developer tab on your ribbon.
  • Then select Visual Basic from the Code group.

  • VBA modules hold the code in the Visual Basic Editor. It has a.bcf file extension. We can create or edit one easily through the VBA editor window.
  • To insert a module for the code, go to the Insert tab on the VBA editor.
  • Then click on Module from the drop-down.

insert module to Use IF and IFERROR Combined in Excel

  • As a result, a new module will be created.
  • Now select the module if it isn’t already selected. Then write down 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""),""Incorrected value"")"
    Range("E5").Select
    Selection.AutoFill Destination:=Range("E5:E12")
    Range("E5:E12").Select
End Sub
  • Next, save the code.
  • Afterward, close the Visual Basic window. After that press Alt+F8.
  • When the Macro dialogue box opens, select the following macro in the Macro name. Click on Run.

  • Therefore, this will track the project’s progress as shown below.

 Use IF and IFERROR Combined in Excel

Read More: How to SUM with IFERROR in Excel


Download Practice Workbook

Download this practice workbook to exercise while you are reading this article. It contains all the datasets in different spreadsheets for a clear understanding. Try it yourself while you go through the step-by-step process.


Conclusion

That’s the end of today’s session. I strongly believe that from now on you may know how to use IF and IFERROR functions combined in Excel. If you have any queries or recommendations, please share them in the comments section below. Keep learning new methods and keep growing!


Related Articles


<< Go Back to Excel IFERROR Function | Excel Functions | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Saquib Ahmad Shuvo
Saquib Ahmad Shuvo

Saquib Ahmad Bhuiyan, holding a BSc degree in Naval Architecture & Marine Engineering from Bangladesh University of Engineering and Technology, skillfully integrates engineering expertise with a passion for Excel. He provides solutions to complex issues as an Excel & VBA Content Developer for ExcelDemy Forum. He demonstrates an enthusiastic mindset and adeptness in managing critical situations with finesse, showcasing his commitment to excellence. Apart from creating Excel tutorials, he is interested in Data Analysis with MS Excel, Rhinoceros,... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo