Microsoft Office (Session 05)

Shape Image One

Advanced Excel Logic

Advanced Logic: IF

The IF formula is a powerful tool in Excel that allows you to make decisions based on conditions. Here’s a breakdown with a picture:

Structure:

=IF(Logical_Test, Value_if_True, Value_if_False)

  • Logical_Test: This is a comparison or expression that evaluates to TRUE or FALSE. (e.g., A1>B1)
  • Value_if_True: The result displayed if the Logical_Test is TRUE.
  • Value_if_False: The result displayed if the Logical_Test is FALSE.
Example:





Styled Table

A B C
Score Pass Mark (70) Result
85 =IF(A2>B1, “Pass”, “Fail”)
60 =IF(A3>B1, “Pass”, “Fail”)


Breakdown:
  • Logical_Test (A2>B1): Checks if the score in cell A2 is greater than the pass mark in B1.
  • Value_if_True (“Pass”): If TRUE, “Pass” is displayed in cell C2.
  • Value_if_False (“Fail”): If FALSE, “Fail” is displayed in cell C2.

    Additional Notes:
    • You can use cell references, values, or even other formulas within the IF statement.
    • You can nest IF statements for more complex conditions (e.g., IF nested within another IF).
      Tips:
      • Use the Formula Help (Fx button) for detailed information on the IF function.
      • Search online for “[Excel IF formula] + example” to find tutorials with pictures for advanced IF scenarios.

        Video Link: #StingTasteBhiTezBhi (youtube.com)

        https://www.youtube.com/watch?v=Zs9NuYw-F7k

        Advanced Logic: IFS

        • IFS is a newer and more efficient way to handle multiple conditions compared to nested IF statements.
        • It evaluates a series of conditions and returns the value corresponding to the first TRUE condition.
        Structure:

        =IFS(Logical_Test1, Value_if_True1, Logical_Test2, Value_if_True2, …)

        • You can add up to 127 pairs of conditions and results (logical_test, value_if_true).
        Benefits:
        • Easier to read and understand than nested IFs, especially with many conditions.
        • Reduces the risk of errors caused by complex nesting.
        Example:

        Imagine you have a sales target in cell B1 and a commission rate structure:

        • Exceed target by 10% or more: 10% commission (cell C1)
        • Meet target exactly: 5% commission (cell C2)
        • Below target: 0% commission (cell C3)

        Using IFS:

        =IFS(A1>(B1*1.1), A1*C1, A1=B1, A1*C2, TRUE, 0)  **Formula in cell D2**

        • Logical_Test1: Checks if sales (A1) exceed target by 10% (B1*1.1).
        • Value_if_True1: If TRUE, calculates commission (A1*C1) based on the higher rate (C1).
        • Logical_Test2: Checks if sales (A1) meet the target (B1) exactly.
        • Value_if_True2: If TRUE, calculates commission (A1*C2) based on the standard rate (C2).
        • Final catch-all: Since IFS requires pairs, the last TRUE argument ensures a 0 commission for any other scenario.

        Tips:
        • Use the Formula Help (Fx button) for detailed information on the IFS function.
        • Search online for “[Excel IFS formula] + example” to find tutorials with more complex scenarios.

        Remember, IFS promotes clear and efficient handling of conditional logic in your Excel spreadsheets.

        Video Link: (456) How to use the IFS function in Microsoft Excel – YouTube

        https://www.youtube.com/watch?v=wqSHQawTACU

        Optimized by Optimole
        WhatsApp whatsapp
        Messenger messenger
        Instagram instagram
        Call Us phone
        chat