When learning and using Excel formulas and functions, it is important to have knowledge of those that are most important and most used, as we already explained in how to create a function in Excel..
One of the most used functions, where we can find some difficulty initially when creating them, are conditional formulas. But as we are going to show you below, we can convert a conditional formula into a simple task.
Also remember that, if at any time you have questions, or have forgotten how to do them, you can see this explanation again . The important thing is that today we learn what a conditional formula is, that we know how to apply it with the syntax and examples that we are going to teach you; And that we know what types of conditional formulas we can apply, establishing one or more conditions to our formula or expression..
We will start as always at the beginning, explaining what a conditional formula is.
1 What is a Conditional Formula in Excel
A conditional formula in Excel is an expression that is used to establish a condition and be able to evaluate in a cell or group of cells whether this condition is met or not met ..
The result of a conditional formula returns one result or another depending on the condition or conditions that we establish in the expression, depending on whether or not the condition or conditions are met, and depending on the responses that we are going to establish.
Conditional formulas are part of the functions called logical functions, and are very important and used in an analysis context to be able to obtain different results with the same formula depending on the established condition.
Formulas or conditional expressions in Excel may seem more complicated at first: these formulas, as we are going to explain, have a different syntax than other simpler functions, and they also tend to be longer than usual. However, if we know how to use them, it will be easy for us to do.
Why is a conditional formula different and why are they longer expressions?
Within the conditional formulas, we will usually use the “IF” function or the “IF.SET” function, as we will see in the examples. Within the “IF” function, it is common to use other functions such as “AND” and functions like “OR” to be able to establish more criteria in the formula.
The syntax of a simple formula that uses a simple function in Excel, such as the average function, as we have already seen, is made up of the name of a function, in the example of the function “AVERAGE” and a range of data in parentheses. As we see in the example image below, this would look like a formula that uses a simple function like the average function.
However, not all formulas are so simple, so short, nor do they all have the same structure or form.
In order to perform operations or calculations, many times you need to include more parameters or information in the expression to obtain the expected result. This is the case of conditional functions, where in addition to including the name of a function and the data range, we must add to the expression which requirement or requirements we want to be used to evaluate the data. Furthermore, in the expression or formula we must also include the response that we want to obtain when that requirement is met, and the response that we hope to obtain when that requirement is not met.
Conditional functions are usually longer formulas, in short, because in the formula, we must include a series of criteria or conditions, and because several results or variants can be given that we must also consider in the expression.
Below, in the image, you can see what the syntax of a simple conditional formula is like, where we establish a condition in the formula, and then we have to add the two possible scenarios that arise:
- If the condition we have set is met
- The established condition is not met
This would be the syntax of a simple conditional formula, in which we have established a single condition.
2 What is a Formula or Conditional expression like in Excel?
In Excel, conditional formulas are commonly used with the “IF” command , as we have seen in the image. In the “IF” expression or formula we must include the condition (or conditions) and then specify what value the formula should return if the condition is true (if the condition is met) and what value it should return if the condition is false (if the condition is true). condition is not met).
In a conditional formula, we can establish more than one criterion or condition. And we will do it by using the “IF” function with other functions like “AND” or how the “OR” function.
Example: How to create a simple conditional formula
Using a simple example, we are going to use a conditional formula to find out whether several students have passed the course or not based on the exam grade. In the image below, we can see the list of the students, with the grade that each one has obtained in the exam.
We can create, as we will see below in the examples, a formula with this logic:
If a student has gotten a grade of 5 or more than a 5 on the exam, he or she has passed. If, on the other hand, the exam grade is less than 5, the result would be a failure.
Important
As we have already mentioned on other occasions,
the use of fixed values in the formula is not recommended ; For this reason, as you have seen in the example, we reference the cell with the cutoff score in the formula, instead of putting a fixed value.
Additionally, as you have seen in the image, in the formula we use the dollar sign $ to set the cutoff score, and that dragging the formula downwards does not move that cell in the formula.
When we apply this formula, as we can see in the image below, the result is what we expected, and we have obtained as a result of the formula, “YES” AND “NO”. “YES” would be those who passed the course, “NO” would be the students who have not met the condition, which if we remember was getting a 5 or more in the annual course grade.
In the example we have seen how to make a conditional formula with a condition. However, in a conditional formula we can establish as many conditions and requirements as necessary using the aforementioned “AND” or “OR” functions, each one with a different purpose.
We are going to see how the “AND” function and the “OR” function work, and we are going to see examples to learn when we should use each one.
“AND” function, “OR” function in Excel conditional formulas
Within a conditional expression or formula made with “IF” we can in turn include the “AND” Function or the “OR” Function to be able to establish, as we have said, more than one condition in Excel. Let's see how it works and what differences there are between the “AND” function and the “OR” function.
- “AND” Function: With the “AND” function, we can establish a series of criteria or conditions that all have to be met. What best characterizes the “AND” function within a conditional formula is that we are establishing several criteria that we want to be met. In the example, we would use the “AND” function to reflect in the formula that a student, in order to pass, must have passed both parts of the course.
- With Function “OR” , we are saying that we want one of the criteria to be met.
3 How to make a Conditional Formula with two conditions in Excel (“IF” Function with 2 or more conditions)
As we have told you, in a conditional formula, we may want to establish more criteria. Thus we can establish two or more criteria in the conditional formula using the “AND” function or the “OR” function , depending on the criteria established for the condition:
- Do we want several conditions to be met yes or yes? We will use the “AND” function
- Do we want only one of the conditions we establish to be met? We will then use the “OR” function.
Let's see better with an example the use of several conditions in the same formula.
Example 2: How to make a formula with several conditionals in Excel
Continuing with the same example, where a teacher is evaluating the annual results of his students to know if they have passed the course or not. The students' results are reflected in two exams that they had to take during the course.
In this case, for a student to pass, they must now have passed the 2 semester exams of the course, and therefore in the formula we must collect all this information.
- If he/she has obtained a 5, or more than a 5 in the 2 exams, the student has passed.
- If a student has failed at least one of the exams, that is, they have scored less than a 5 in some of the exams, this student has failed the course.
What would a conditional formula with two or more criteria or conditions look like?' To the syntax that we have created before, we have to add a new function that is combined with the conditional “IF” function that we have already talked about and explained: the “AND” function.
The “AND” function responds to language logic. In our formula, we want to put condition 1, which is that the student has passed the first semester, AND condition 2, which is that the student has also passed the second semester. That is, you have to have passed both exams yes or yes to have a pass. (And if this condition of having passed both exams is not met, then it would be a failure).
Thus, the formula that we built would have the following form, which you can see in the image, with the explanation of each part of the formula.
When applying the formula to all students, this is the result we obtain. Now, unlike the example we saw before, only students who have passed both exams appear with the “YES” for passing.
With this method, using the “AND” function you can add as many criteria as you want to add to the formula, following the same structure of the formula that we just presented to you in the example.
Now we know how to make a conditional formula, with several conditions. In the examples we have seen, we have constructed expressions with 1 or more conditions, and we have established two possible scenarios in response. If the requirements are met, in the example, that both exams have been passed, we obtain the first of our answers: YES, it is approved.
In the case of not having passed, the formula includes “No” as an answer, in case a student has failed one of the two exams, or has failed both. This means that we have two possible scenarios.
- The condition is met.
- It is not fulfilled.
As far as conditional formulas are concerned, there is also a way to establish more than two responses or scenarios, as we are going to explain to you below, and you will see how easy it is in the example.
4 How to use Conditional Formulas in Excel with multiple answers
Continuing with the learning of conditional formulas, we are going to see below a way to create expressions that contain more than two answers, using the resources that we have been learning:
- Using the “IF” function to construct the conditional formula
- Using the “AND” and “OR” functions within the conditional formula.
This formula will be longer than the formulas we have been looking at, since it must include, as we already know, the condition or conditions that we want to use, as well as the possible scenarios that may occur depending on the result we hope to obtain.
As always, let's see it with an example. We will see that we are going to build a long formula, but that we can nevertheless build it well and easily if we know how to do it.
Example 3: How to make formulas in Excel with multiple answers
We therefore continue with the same example, but now we want to create a different formula, because we want to create 3 evaluation categories (3 possible answers) according to the users' grades in the two exams.
In the example, we are therefore going to create a formula that includes 3 possible answers, because we want to create a new evaluation category to include students who have passed 1 of the 2 exams.
So the 3 types of evaluation that we want to apply are the following:
- Those who have passed both exams are “PASSED” students. They are students who have scored a 5 or more on each exam.
- Those who have failed both exams: are “FAIL” students (students who have obtained a grade less than 5 in each exam).
- The new category of users will be those who have passed only one of the two exams. These will be students that we are going to categorize as “RECOVERY” (They are students who, in one of the two exams taken, have obtained a 5 or more in one of the exams, and less than a 5 in the other exam).
Before building the formula, let's see what the syntax of the expression we are going to create would be, according to the example we are working on.
Taking into account the 3 evaluation categories, the syntax of the formula must be constructed taking into account that there are 3 different types of students:
- For the first scenario, we will use the “Yes” Function, and we will have to use, as we have already seen, the “AND” function (To be able to express in scenario 1, that, to have a Pass, you must have passed exam 1 and exam 2).
- For scenario 2, we are going to use the “IF” function again, and in this case we will use the “OR” function. Because this is the case of the students we have called “RECOVERY”, who are those who have either passed one exam, or have passed the other. If scenario 1 and 2 are not met, then this student will be a “FAIL” student.
Next, we have built the expression for the example, which would look like you can see in the image below. It is an expression that helps to understand it better if, as we have seen in the syntax, we build it little by little and using the same logic.
When we apply the formula and drag to the rest of the cells to obtain the grade of all the students, we can see how the formula works well, and we obtain the 3 types of evaluation mentioned: PASS, FAIL, RECOVERY.
In this way we better understand why a conditional formula can be more complicated at first, but we can nevertheless make it easy if we have the appropriate training. This is today's goal, turning something that is initially difficult, like a conditional formula, into a simple task.
We must keep in mind that, as we have seen, several cases can occur in a conditional formula, and we have explained all of them:
- A simple conditional formula
- A conditional formula with 2 or more criteria
- A conditional formula with more than two criteria, and also with multiple answers
When we are learning to use a practical tool such as Excel, it is best to use and test the knowledge acquired. In this way, you will put into practice what you have learned, while memorizing how to use Excel in practice in its multiple functions and uses.
Conditional formulas will help you take a leap in the way you evaluate data, and will increase your resources when it comes to having to use formulas and functions in Excel for data analysis.