Master Advanced Excel & VBA

Learn Excel formulas, data analysis, dashboards, VBA programming, and business automation through structured practical lessons.

COURSE SYLLABUS
1. Excel Basics
Introduction to Advanced Excel Workbook, Worksheets, Rows & Columns Data Types & Cell Formatting Cell References & Keyboard Shortcuts
2. Advanced Excel Formulas
Advanced Formulas Introduction IF, AND, OR & IFERROR Excel Tables XLOOKUP INDEX & MATCH SUMIFS & COUNTIFS
3. Pivot Analytics
Pivot Analytics Introduction Pivot Tables Calculated Fields & Items Timelines & Slicers Text Functions & Data Cleaning
4. Excel Dashboards
Interactive Dashboards Introduction Data Validation Conditional Formatting Combo Charts Sparklines
5. Excel Data Tools
Data Integration Introduction Importing Text Files Power Query Worksheet Protection Formula Auditing
6. VBA Fundamentals
VBA Fundamentals Introduction Macro Recorder VBE Workspace VBA Objects & Properties Variables & Data Types MsgBox & InputBox If...Then...Else Select Case Loops & Dynamic Ranges
7. VBA Automation
VBA Automation Introduction Subroutines & Arguments User-Defined Functions Workbook Events Worksheet Events Form Controls & Buttons
8. VBA UserForms
UserForms Introduction UserForm Design Toolbox Controls Input Validation CRUD Operations with UserForms
9. VBA Optimization & Security
Optimization Introduction Error Handling Debugging Tools Performance Optimization VBA Project Security
10. Corporate Capstone Project
Capstone Project Introduction Data Consolidation Data Cleaning & Analysis Automated PDF Reports Dashboard Automation
11. Excel Practice & Assignments
Practice Introduction Excel Formula Exercises Pivot Table Exercises Dashboard Exercises VBA Programming Exercises Final Assignments

IF, AND, OR and IFERROR

Learn how to make Excel formulas evaluate conditions, combine multiple tests, and handle formula errors. Practice logical functions using student marks and training institute reports.

Learning objectives: Use IF for conditional results, AND when all conditions must be true, OR when at least one condition must be true, and IFERROR to return an alternative result when a formula produces an error.

1. IF Function

The IF function checks a condition and returns one value if the condition is TRUE and another value if it is FALSE. It is useful for pass/fail results, eligibility checks, attendance status, and sales targets.

Syntax
=IF(logical_test, value_if_true, value_if_false)

Example: Student Pass or Fail

Suppose Samadhan's marks are stored in cell B2. The pass threshold for this example is 40 marks.

Excel Formula
=IF(B2>=40,"Pass","Fail")
Read student marks (B2) Marks ≥ 40? Check condition TRUE FALSE Pass Fail

Diagram: IF evaluates a condition and chooses one of two results.

If B2 contains 85, Excel returns Pass. If B2 contains 32, Excel returns Fail.

2. AND Function

The AND function returns TRUE only when every supplied condition is TRUE. If even one condition is FALSE, AND returns FALSE.

Syntax
=AND(condition1, condition2, ...)

Example: Course Eligibility

Samiksha is eligible for a sample training certificate when her marks are at least 40 and her attendance is at least 75%. Assume marks are in B2 and attendance is in C2.

Excel Formula
=AND(B2>=40,C2>=75%)

The formula returns TRUE only if both conditions are met. To display a message, combine AND with IF:

Excel Formula
=IF(AND(B2>=40,C2>=75%),"Eligible","Not Eligible")
Example result: Marks = 85 and attendance = 90% returns Eligible. Marks = 85 and attendance = 60% returns Not Eligible.

3. OR Function

The OR function returns TRUE when at least one supplied condition is TRUE. It returns FALSE only when all supplied conditions are FALSE.

Syntax
=OR(condition1, condition2, ...)

Example: Special Recognition

Omkar qualifies for a sample recognition list if he scores at least 90 marks in Excel or at least 90 marks in VBA. Assume the marks are stored in B2 and C2.

Excel Formula
=IF(OR(B2>=90,C2>=90),"Recognize","Regular")

If B2 is 68 and C2 is 95, the result is Recognize because the second condition is TRUE. If both marks are below 90, the result is Regular.

4. IFERROR Function

IFERROR returns an alternative value when a formula produces an error. It helps make reports easier to read when calculations involve missing data, invalid references, or division by zero.

Syntax
=IFERROR(value, value_if_error)

Example: Safe Division

Suppose D2 contains total marks and E2 contains the maximum marks. Dividing by zero can produce a #DIV/0! error.

Excel Formula
=IFERROR(D2/E2,"Check maximum marks")

If the calculation succeeds, Excel returns the division result. If it produces an error, Excel displays Check maximum marks.

Important: IFERROR handles errors; it does not fix the underlying problem. Use it thoughtfully so that important data issues are not hidden.

5. Comparison of IF, AND, OR and IFERROR

Function Purpose Example
IF Returns one result when true and another when false. =IF(B2>=40,"Pass","Fail")
AND Checks whether all conditions are true. =AND(B2>=40,C2>=75%)
OR Checks whether at least one condition is true. =OR(B2>=90,C2>=90)
IFERROR Returns an alternative value if a formula errors. =IFERROR(D2/E2,"Check data")

6. Step-by-Step Practical Exercise

Neha is preparing a training report with marks and attendance. She wants Excel to identify eligible students automatically.

Student Marks Attendance Eligibility
Samadhan 85 90% Eligible
Samiksha 92 80% Eligible
Pradnya 76 70% Not Eligible
Omkar 32 85% Not Eligible
Neha 88 95% Eligible
  1. Enter student names in column A.
  2. Enter marks in column B and attendance percentages in column C.
  3. In D2, enter the eligibility formula shown below.
  4. Press Enter and copy the formula down the column.
  5. Change sample values to test different outcomes.
Excel Formula for D2
=IF(AND(B2>=40,C2>=75%),"Eligible","Not Eligible")

The example uses a minimum of 40 marks and 75% attendance. Adjust these thresholds according to the actual course rules.

7. Common Mistakes to Avoid

  • Forgetting quotation marks around text results such as "Pass".
  • Using AND when the requirement is that only one condition must be true.
  • Using OR when every condition must be satisfied.
  • Comparing percentages that have been entered as whole numbers incorrectly.
  • Using IFERROR to hide problems without investigating their cause.
  • Copying formulas without checking whether cell references adjust correctly.

Important Points to Remember

  • IF chooses a result based on a condition.
  • AND returns TRUE when all conditions are TRUE.
  • OR returns TRUE when at least one condition is TRUE.
  • IFERROR provides an alternative result when a formula errors.
  • Test formulas using both successful and unsuccessful cases.

Summary

In this lesson, you learned how IF, AND, OR and IFERROR work. You practiced checking student marks, combining marks and attendance conditions, and displaying alternative messages when a formula produces an error.

These functions are useful for automating decisions and preparing reliable reports. In the next topic, we will learn about Excel Tables.