IF statement is used to test whether a certain condition is met. Sometimes you can have more than one condition to test, thus creating an If statement within another if statement (nested if statement). This enables you to come up with a statement with many possible outcomes.
The excel If () function is used when evaluating a condition with two outcomes. e.g. (evaluate if sales are greater than 1000 and give different values for each outcome). The function is commonly used to evaluate whether a condition is true or false.
It returns one outcome if the logical; test is TRUE and another outcome if the condition is FALSE.
The syntax for two outcomes
IF(logical_test, [value_if_True], [value_if_False])
There are situations where you have to work with more than two outcomes. In such a situation, you're required to create Multiple IF statements or Nested If Statements.
How to use multiple if statements with more than two outcomes
This multiple if statement replaces one of the True/False calculations with another if function. You create an if function within another if function.
Syntax
=IF (CONDITION X, OUTPUT B, IF (CONDITION Y, OUTPUT C, OUTPUT D)))
Example: Commission for sales based on monthly sales
Open your excel sheet and enter the following details
From the above table, create an if statement with the following conditions
| Sales | Commission |
| Less than $400 | 7% |
| Sales between $400 and $800 | 12.5% |
| Sales above $800 | 16% |
Use nested if to calculate the commission for Shelly.
Perform a logical test on sales: Are the Sales figure less than $400? If TRUE, then calculate commission. If FALSE, then is the sales less than $800? If TRUE, calculate the commission; if False, then calculate the commission.
Enter the following formula in cell C2
=IF(B2<400,B2*7%,IF(B2<800, B2*12.5%,B2*16%))
Press enter key to calculate the commission for Shelly.
Use the Autofill feature to copy the formula to the rest of the sales team.
In the above example, the If statement checks if cell B2 is greater than less than $400. If that is true, it calculates the commission with 7% and stops calculating. If cell B2 had more than $400 and sales of less than $800, the commission would be calculated at 12.5%; otherwise, if the sales figure is greater than $800 commission would be calculated at a rate of 16%.
IF function with multiple conditions
Nested IF functions follow specific conditions to give respective outcomes. The function defines the condition, which results in values or results as commanded by the formula function. Two or more conditions fed in the IF function give a similar number of desired outcomes. In the illustration below, we look at how Nested IF can be used in choosing qualified candidates for a program.
Suppose you want to allocate projects to students. The conditions for qualification are;
- A GPA of 2.5 or more must be achieved.
- The learner must have completed at least 2 elective courses.
- The number of credits received by the student should be 115 or above.
The first condition must be fulfilled by the student the rest. Look at the following table of students who applied for the project.
Now go ahead and write the following formula in cell E2
=IF(AND(B2>=2.5,OR(C2>=110,D2>=1)), "Yes","No")
Press Enter to get the result in the cell
Use the Fill Handle (+) to complete the values in the column.
Multiple IF and AND statements in Excel
Let us work with the following illustration: if a patient walks in with flu symptoms, they are first sent to an outside tent to be tested for Covid-19. They are sent straight to the doctor for consultation if no flu symptoms. The patient is then sent to the vaccination booth after visiting the pharmacy.
We’ll use the IF and AND functions fused in a complex formula to determine the direction every respective patient shall take from when they enter the hospital.
Type the following formula in cell D5
= IF(AND(B5="Yes",C5="Positive"), "Patient sent to Ward One, then respiratory department", IF(AND(B5="Yes",C5="Negative"),"Patient sent to Ward One, then Vaccination Booth", IF(AND(B5="No",C5="Positive"),"Patient sent to Consultation, then Pharmacy", "Bypass Ward One and go directly to Consultation")))
Press Enter to get the direction for that cell.
1. Use Fill Handle (+) to complete the table.
Here’s what you should note;
- You can work with up to 64 IF conditions in excel on Office 2007 – 2016.
- You must use the appropriate order of IF conditions to get the required outcomes. If not, you will encounter an error.
- The standard practice is using AND or OR functions together with the IF function if it has to be used severally in the formula.
Calculating student grades using the IF function in Excel
Say you have finished tabulating students’ scores in figures and want to append equivalent grades as is the standard practice. Usually, these grades come as a range of figures which are given as scores. You can easily use multiple IF functions to update these students’ grades. Consider the illustration below.
Write the following formula in cell C2, then press Enter
=IF(B2<50,"F",
IF(B2<60,"D",
IF(B2<65,"D+",
IF(B2<70,"C",
IF(B2<75,"C+",
IF(B2<80,"B",
IF(B2<85,"B+",
IF(B2<90,"A","A+"))))))))
Drag the Fill Handle (+) downwards to fill the table
