Excel Logical Functions for Finance: IF, AND, OR, IFS, and SWITCH Explained

Financial analysis often requires evaluating multiple conditions, such as bonus eligibility, loan approval, credit ratings, or tax bracket assignment. Handling these tasks manually can be time-consuming and increase the chances of errors, especially when working with large datasets.

Excel Logical Functions automate these decisions and make financial models more efficient and reliable. The concepts covered here include IF, AND, OR, NOT, IFS and SWITCH with practical finance-based examples for CA, MBA Finance, IB, FM, and B.Com learners.

Introduction to Logical Functions in Excel

Logical Functions in Excel help check whether a condition is true or false and return the required result. They are widely used in financial models to automate calculations and make decisions based on predefined rules. Functions such as IF, AND, OR, NOT, IFS, and SWITCH reduce manual work and improve accuracy. They are useful for tasks like bonus calculations, loan eligibility, credit ratings, tax bracket assignment, and other finance-related decisions.

Logical Functions in Excel Overview

Logical Functions in Excel help evaluate conditions and return results based on predefined rules. They are widely used in financial modelling to automate calculations, improve decision-making, and reduce manual effort. The concepts covered here include functions for testing conditions, combining multiple criteria, assigning categories, and handling errors in financial data.

How Can the IF Function Help Assess Employee Bonus Eligibility?

The IF function is one of the most commonly used logical functions in Excel. It checks whether a condition is true or false and returns the appropriate result. Consider an employee bonus assessment where employees receive a bonus only if their sales are greater than the assigned target. The IF function helps identify eligible employees and calculate their bonus automatically.

Bonus Eligibility Rules

• Employees qualify for a bonus if Sales > Target.

• Qualified employees receive a bonus equal to 10% of Sales.

• Employees who do not qualify receive a bonus of 0.

Steps to Check Employee Bonus Eligibility 

Follow these steps to check employee bonus eligibility using the IF function. First, compare each employee's sales with the assigned target. Then use the result to calculate the bonus amount automatically.

Step 1: Check Employee Bonus Eligibility

Begin by entering the IF function. The logical test compares an employee's Sales with the Target. If the sales value is greater than the target, the employee qualifies for the bonus. 

Formula

=IF(Sales>Target,"Yes","No")

If the condition is true, Excel returns "Yes". If the condition is false, it returns "No". Remember to write text values such as "Yes" and "No" inside inverted commas.

Step 2: Calculate the Bonus Amount

After identifying eligible employees, use another IF function to calculate the bonus amount. If the result in G2 is "Yes", Excel calculates 10% of Sales. Otherwise, it returns 0.

Formula

=IF(G2="Yes",0.10*Sales,0)

This formula automatically calculates the bonus amount. Eligible employees receive 10% of Sales, while employees who do not qualify receive 0. The IF function is useful whenever a calculation depends on a single condition.

Final Output

The table below shows the final output after applying both IF formulas to the employee data.

The output shows which employees qualify for the bonus and the bonus amount calculated automatically using the IF function.

How Can Nested IF Functions Help Assign Performance Ratings?

The Nested IF function is useful when there are more than two possible outcomes. Instead of checking only one condition, it evaluates multiple conditions in a specific order and returns the appropriate result. Consider a performance rating exercise where employees are assigned different performance labels based on their rating scores.

Performance Rating Rules

• Employees with a rating of 4 or above receive "Excellent".

• Employees with a rating of 3 or above but below 4 receive "Good".

• Employees with a rating below 3 receive "Needs Improvement".

Steps to Assign Employee Performance Ratings

Follow these steps to assign performance ratings using the Nested IF function. Start by checking the highest rating condition first, then evaluate the remaining conditions in descending order until the correct performance label is assigned.

Step 1: Check the Highest Rating Condition

Begin by checking whether the employee's rating is 4 or above. If this condition is true, Excel returns "Excellent". Otherwise, move to the next condition.

Step 2: Add the Second Condition

If the first condition is false, check whether the rating is 3 or above. If this condition is true, Excel returns "Good". If neither condition is true, Excel returns "Needs Improvement".

Step 3: Write the Nested IF Formula

Combine all the conditions into a single Nested IF formula.

Formula

=IF(Rating>=4,"Excellent",IF(Rating>=3,"Good","Needs Improvement"))

Always check the highest condition first and close a bracket for each IF function used.

Final Output

The table below shows the final performance ratings after applying the Nested IF formula.

The Nested IF function is useful when a formula needs to return more than two possible results based on multiple conditions.

How Can AND, OR and NOT Functions Help Screen Loan Eligibility?

AND, OR, and NOT functions are used when a decision depends on one or more conditions. These functions help finance professionals evaluate loan applications based on factors such as credit score, income, and existing debt. Depending on the business rule, Excel can approve or reject an application automatically.

How Does the AND Function Check Loan Eligibility?

The AND function returns TRUE only when all conditions are TRUE. If even one condition is FALSE, the result becomes FALSE. This is useful when every eligibility rule must be satisfied before approving a loan.

Loan Eligibility Rules (AND)

• Credit Score ≥ 700

• Income ≥ $50,000

• Existing Debt < $20,000

• Loan is approved only if all three conditions are met.

Steps to Check Loan Eligibility Using the AND Function

Follow these steps to check whether an applicant qualifies for a loan using the AND function. First, verify all eligibility conditions. Then combine the result with the IF function to display the final decision.

Step 1: Check All Eligibility Conditions

Use the AND function to test whether every condition is satisfied.

Formula

=AND(CreditScore>=700,Income>=50000,Debt<20000)

If all conditions are TRUE, Excel returns TRUE. If any one condition is FALSE, Excel returns FALSE.

Step 2: Display the Final Decision

Wrap the AND function inside an IF function to show the loan decision.

Formula

=IF(AND(CreditScore>=700,Income>=50000,Debt<20000),"Approved","Rejected")

Applicants meeting all conditions are marked Approved. Everyone else is marked Rejected.

Final Output

The table below shows the final result after applying the AND and IF formulas.

How Does the OR Function Check Loan Eligibility?

The OR function returns TRUE if at least one condition is TRUE. It is useful when meeting any one of the eligibility conditions is enough to approve the application.

Loan Eligibility Rules (OR)

• Credit Score > 750 OR

• Income ≥ $80,000

If either condition is satisfied, the applicant is eligible.

Steps to Check Loan Eligibility Using the OR Function

Follow these steps to evaluate loan eligibility using the OR function. Excel checks both conditions and returns TRUE if at least one condition is satisfied.

Step 1: Check the Eligibility Conditions

Use the OR function to test the required conditions.

Formula

=OR(CreditScore>750,Income>=80000)

If either condition is TRUE, Excel returns TRUE. If both conditions are FALSE, Excel returns FALSE.

Step 2: Display the Final Decision

Use the OR function inside the IF function to show the approval status.

Formula

=IF(OR(CreditScore>750,Income>=80000),"Approved","Rejected")

Applicants satisfying at least one condition are marked Approved. Others are marked Rejected.

Final Output

The table below shows the final result after applying the OR and IF formulas.

How Does the NOT Function Reverse a Condition?

The NOT function changes the result of a logical test. If a condition is TRUE, NOT returns FALSE. If the condition is FALSE, NOT returns TRUE. It is useful when you want to reverse a condition before making a decision.

Loan Eligibility Rules (NOT)

• Credit Score < 600 is considered High Risk.

• Applicants who are NOT High Risk can be approved.

Steps to Identify Applicants Using the NOT Function

Follow these steps to reverse the high-risk condition using the NOT function and display the final loan decision.

Step 1: Reverse the High-Risk Condition

Use the NOT function to check whether the applicant is not in the high-risk category.

Formula

=NOT(CreditScore<600)

If the credit score is 600 or above, Excel returns TRUE. Otherwise, it returns FALSE.

Step 2: Display the Final Decision

Combine the NOT function with the IF function.

Formula

=IF(NOT(CreditScore<600),"Approved","Rejected")

Applicants who are not high risk are marked Approved, while high-risk applicants are marked Rejected.

Final Output

The table below shows the final result after applying the NOT and IF formulas.

How Do IFS and SWITCH Functions Simplify Multiple Conditions in Excel?

The IFS and SWITCH functions provide cleaner and more readable alternatives to long Nested IF formulas. While the IFS function evaluates multiple logical conditions in sequence, the SWITCH function matches a value against predefined options. These functions simplify formulas, improve readability, and make them easier to maintain.

IFS Function — Assigning Credit Ratings (AAA to B or Below)

The IFS function evaluates multiple conditions one after another and returns the value for the first TRUE condition. It is useful when you need to assign different results based on multiple ranges without creating lengthy Nested IF formulas.

Credit Rating Rules

The formula is:

=IFS(C2>=800,"AAA",C2>=750,"AA",C2>=700,"A",C2>=600,"BB",TRUE,"B or Below")

Excel evaluates each condition from left to right and returns the result for the first TRUE condition. The final TRUE acts as the default condition, returning "B or Below" whenever none of the previous conditions are satisfied. This makes the formula much easier to read and maintain than a deeply Nested IF formula.

SWITCH Function — Assigning Tax Bracket Labels

The SWITCH function compares a single value against multiple predefined values and returns the corresponding result. It is useful when working with fixed categories, such as tax bracket codes, grades, status codes, or other predefined labels.

The formula is:

=SWITCH(C20,1,0%,2,5%,3,10%,4,15%,5,20%)

The SWITCH function checks the Bracket Code and returns the corresponding tax rate for the first matching value. Since it matches exact values instead of logical conditions, it provides a cleaner and more concise alternative to writing multiple IF statements for fixed categories.

Final Output

The tables below show the final output after applying the IFS function to assign credit ratings based on credit scores and the SWITCH function to assign tax rates using bracket codes.

Tax Bracket Rules