Simple and Nested if statement

Introduction to IF Statements in Excel

  • Purpose of the Video: To learn how to build IF statements in Excel, including simple and nested IF statements.

  • Importance of IF Function: It is one of the most useful functions in Excel, returning a value based on whether a specified condition is true or false.

Syntax of IF Statements

  • Basic Syntax: =IF(logical_test, value_if_true, value_if_false)

    • Parameters:

    • logical_test: The criteria to evaluate (e.g., a comparison statement).

    • valueiftrue: The output if the logical test evaluates to true.

    • valueiffalse: The output if the logical test evaluates to false.

Example of a Simple IF Statement

  • Scenario: A user inputs their age.

    • Input: User enters 16, where the voting age in the United States is set at 18 years.

    • Formula: =IF(E4 < 18, "No, you are not old enough to vote", "Yes")

    • Interpretation:

      • If the user enters 16, E4 < 18 evaluates to true. Therefore, the output is "No, you are not old enough to vote."

      • If the user enters 40, E4 < 18 evaluates to false. Therefore, the output would be "Yes."

Quotation Marks in Outputs

  • Note: Quotation marks are required around outputs when they are text. If the output is a number, quotes are not needed.

Excel Practice Exercise 1

  • Open Excel Practice Sheet: Go to the first tab titled 'IF'.

  • Scenario: User inputs their age and the younger sibling's age to determine voting eligibility.

    • Example Input: User enters age 25 and younger sibling age 17.

  • Question: Can the user vote for president?

    • Logical Test Used: =IF(C3 >= 18, "Yes", "No, you cannot vote for president")

    • Correction Note: The formula should use >= (greater than or equal to) instead of >, ensuring inclusivity for age 18.

Considerations for Eligibility

  • Alternative Approach: To calculate the years eligible to vote, a simple subtraction (25 - 18) is ineffective for those not eligible. An IF statement can provide logical outputs.

    • Suggested Formula: =IF(C3 >= 18, C3 - 18, 0)

    • If the user is eligible to vote, the formula will output years eligible; otherwise, it outputs 0.

      • Example: If the user is 16 years old, the output will be logically stated as 0.

Introduction to Nested IF Statements

  • Definition: A nested IF statement involves placing an IF function within another IF function.

  • Scenario: Determining if both the user and younger sibling are eligible to vote using nested IF statements.

    • Formula Structure: =IF(C3 >= 18, IF(C4 >= 18, "Yes", "No"), "No")

    • Breakdown:

      • The outer IF checks the user's age.

      • The nested IF checks the sibling's age if the first condition is true.

      • Outputs "Yes" only if both are eligible; otherwise, outputs "No".

Testing the Nested IF Statement

  • Test Scenarios:

    • User inputs age 14; output = "No".

    • User inputs age 25 and sibling 16; output = "No".

    • User inputs both eligible ages; output = "Yes".

Final Exercise for Students

  • Task Instructions:

    • Question 1: Input expected company revenue and actual revenue, expected EBITDA and actual EBITDA.

    • Output: Did the company exceed revenue expectations? (simple IF statement).

    • Additional Output: Did the company exceed both revenue and EBITDA expectations? (nested IF statement).

  • Encouragement: Press pause to try the questions independently and practice building IF statements.