You have decided that pass and fail are not enough. This will give us a chance to use nested IF statements. Let’s take this a little further and give the students letter grades instead of simply passing or failing them. Imagine having to determine a pass or fail in a list of a hundred students just by looking at the Test Score column. That was a simple example, but it shows how valuable the IF function can be in Excel. The following function in the next cell down will be this: IF(B3 > 50, "Pass", "Fail") If you click on them, you will notice the only thing that changes is the cell value in the function. This basically says if the value in the B2 cell is greater than 50, then print “Pass.” If not, print “Fail.” To add the function to the rest of the cells in the column, just highlight and drag the cell down the column, and it will create functions for each cell. This is the function that will print “Pass” or “Fail” in the column: IF(B2 > 50, "Pass", "Fail") We also added a Pass/Fail column for the IF function we will be using.Īfter you have your data in the spreadsheet, select the top cell in the Pass/Fail column and click on the Fx button to add a function to the cell. Below, you can see what our data looks like in an Excel spreadsheet. Let’s be lenient with the students and say that any student getting over 50% of the questions right passes. Here is an sample of the type of data you will have: Name You have the names of the students and their test scores in a spreadsheet. Using the IF Statement in Excelįor our following examples, imagine you’re a teacher who needs to assign a grade to each student based on their test scores.
#EXCEL IF THEN FORMULA EXAMPLE HOW TO#
How to Use Multiple IF Statements in Excelīefore we get into using multiple IF statements, let’s get started with a simple example of using only one IF statement and build from there.
![excel if then formula example excel if then formula example](https://cdn.extendoffice.com/images/stories/excel-functions/fdist-function/doc-f.inv-function-2.png)
We’ll explain the reason behind that later in the article, but for now, let’s look at some examples of how nested IF statements can help you perf data analytics. Note, though, that just because it’s possible to nest a large amount of IF statements, doesn’t mean it’s a good idea. With the current version of Excel, you can nest up to 64 different IF functions - which is basically like chaining a bunch of ELSEIF conditions in a programming language. We can look at this as pseudocode again to figure out what is happening. Here is what a nested IF statement would look like: IF(condition1, value_if_true1, IF(condition2, value_if_true2, value_if_false2))
![excel if then formula example excel if then formula example](https://blog.hubspot.com/hs-fs/hubfs/sum-formula-excel.jpg)
#EXCEL IF THEN FORMULA EXAMPLE CODE#
This structure means that you could create an IF statement, and then if that statement evaluates to true, the code can then use another IF statement and so on. The third parameter in the Excel IF statement is equivalent to what an ELSE statement would return in many programming languages, but you can also use another IF statement as the third parameter.
![excel if then formula example excel if then formula example](https://i2.wp.com/www.techguruplus.com/wp-content/uploads/2017/01/If-else-formula-in-excel-11.png)
To get a sense of how this works, we can convert it to pseudocode like the example below: IF (condition) THEN value_if_false: The value that is returned if condition evaluates to FALSE.value_if_true: The value that is returned if condition evaluates to TRUE.condition: The value that you want to test.Here is the syntax of the IF statement in Excel: IF(condition, value_if_true, value_if_false) Let’s look at the syntax of the IF statement.
![excel if then formula example excel if then formula example](https://www.exceldemy.com/wp-content/uploads/2022/07/Excel-Leave-Cell-Blank-If-No-Data-3-1536x1324.png)
If the statement returns false, then another value is returned. If the statement returns true, then one value is returned. As the image above suggests, we use the IF statement to evaluate a condition.