Shahriar Abrar Rafid
You can construct a conditional formula to conduct a computation when a cell contains a particular value. If the condition is true, a conditional formula provides one value; if the condition is false, a different value is returned. There are numerous ways to create a conditional formula in Excel. In this article, we’ll demonstrate 5 different and easy methods to create a conditional formula in Excel. So, let’s go through the article to explore them one by one.
Download Practice Workbook
You may download the following Excel workbook for better understanding and practice yourself.
5 Methods to Create a Conditional Formula in Excel
For ease of understanding, we are going to use a Score List of Math’s of some students of a particular institution. This dataset contains the ID, Student Name, and their corresponding Marks in columns B, C, and D respectively.
Now, we’ll show you 5 easy approaches to create a conditional formula in Excel. So, let’s look at them one by one.
Here, we have used Microsoft Excel 365 version, you may use any other version according to your convenience.
1. Using IF Function
In our first method, we’ll use the IF function to create a conditional formula. Here, we’ll check whether a student passed or failed the exam. So, let’s get into it.
- At the very beginning, select cell E5 and enter the formula below.
=IF(D5>55,"Passed","Failed")
Here, we applied a logical statement. If the student gets a number above 55 then the formula will return Passed in cell E5. Otherwise, it will show Failed in that cell. In this case, Jonas got 54 which is less than 55. So, she gets Failed.
- Then, press the ENTER key.
- At this time, bring the cursor to the right-bottom corner of cell E5 and it’ll look like a plus (+) sign. Actually, it’s the Fill Handle tool.
- Now, double-click on it.
It copies the formula to the lower cells and gives us the output of these cells automatically.
Read More: How to Create a Formula in Excel (5 Ways)
2. Inserting AND, IF, and OR Functions
In the second method, we’ll use the AND, IF, and OR functions in one formula. Here, we’ll imply multiple conditions to pass the exam. For this method, we’ll add marks for another subject: Geography.
If a student gets above 50 in both subjects, he will pass the exam. Or, he has to obtain marks above 40 in Maths and marks above 60 in Geography to pass the exam. Otherwise, he’ll get a “failed” result. Now, we’ll do it in Excel for these 10 students. So, let’s see it in action.
📌 Steps:
- First of all, go to cell F5 and insert the formula below.
=IF(OR(AND(D5>50,E5>50),AND(D5>40,E5>60)),"Passed","Failed")
- AND(D5>40,E5>60): This part means the marks in Maths should be above 40 and the marks in Geography should be above 60.
- AND(D5>50,E5>50): This part indicates that the number of both subjects has to be above 50.
- OR(AND(D5>50,E5>50),AND(D5>40,E5>60)): Here, the OR function checks whether any of the two arguments are TRUE.
- IF(OR(AND(D5>50,E5>50),AND(D5>40,E5>60)),”Passed”,”Failed”): If the OR function returns TRUE, then the IF function gives the output as “Passed”, otherwise it gives “Failed”.
- Secondly, press ENTER.
In this example, Jonas got 54 and 44 in Maths and Geography respectively. So, he gets “Failed” in the exam as per the formula.
Read More: How to Create a Custom Formula in Excel (A Step-by-Step Guideline)
3. Applying IF and SUM Functions
Now, we’ll add the SUM function with the IF function to show something new. Allow me to demonstrate the process below.
📌 Steps:
- At first, select cell F5 and write down the following formula.
=IF(SUM(D5:E5)>110,"Excellent",IF(SUM(D5:E5)>100,"Good","Not Satisfactory"))
Here, if the sum of numbers in cells D5 and E5 is greater than 110, then it gives the output “Excellent”. If it’s greater than 100, then the output is “Good”. On the other hand, the output is “Not Satisfactory”.
- After that, hit ENTER.
Jonas gets a total of 98 which is less than 100. So, his result is “Not Satisfactory”.
Read More: How to Apply Formula to Entire Column Without Dragging in Excel
Similar Readings
- How to Create a Formula in Excel to Change Date by 1 Year (3 Methods)
- Create a Formula in Excel for Multiple Sheets (4 Methods)
- How to Create a Formula to Calculate Percentage in Excel
- How to Create a Formula in Excel That Will Place the Word Yes (7 Ways)
4. Implementing IF, MAX, and MIN Functions
In this example, we’ll find out who has the highest and lowest mark in the class. So, without further delay, let’s dive in!
📌 Steps:
- Firstly, we’ve to find out the total number of each student.
- So, move to cell F5 and paste the following formula.
=SUM(D5:E5)
- Then, hit the ENTER key.
- After that, proceed to cell G5 and apply the formula below.
=IF(F5=MAX($F$5:$F$14),"Highest Marks",IF(F5=MIN($F$5:$F$14),"Lowest Marks",""))
In this formula, the MAX function returns the largest value in the F5:F14 range. If the total in cell F5 is equal to the largest value, then it will give the output Highest Marks in cell G5. Then, the MIN function returns the lowest value from the same range. And, if F5 is equal to the smallest value, then it will return Lowest Marks in that cell.
- Following this, tap ENTER.
- Therefore, double-click on the Fill Handle tool to get results for the remaining cells.
Here, we can see that Harry gets the Highest Mark which is 133 and Nick gets the Lowest Mark which is 85.
Read More: How to Apply Formula to Entire Column Using Excel VBA
5. Utilizing COUNTIF Function
This time, we’ll show the conditional formula for counting anything in Excel using the COUNTIF function. In the following dataset, we get the respective Gender of each student.
We want to count the total number of female students. Excited? Let’s see the process in detail.
📌 Steps:
To do this, create a new output range in the B16:D16 range.
- Then, go to cell D16 and put down the following formula.
=COUNTIF(D5:D14,"Female")
- Later, tap the ENTER key.
The results show that there are a total of 4 female students in the dataset. You can verify it by counting manually, as the dataset is small enough.
Read More: How to Create a Formula in Excel without Using a Function (6 Approaches)
Creating Conditional Formatting Formula in Excel
We can use the conditional formatting formula with the IF function in Excel for an easy calculative and attractive worksheet. It’s simple and easy; just follow along.
📌 Steps:
- Initially, we get the Results in Column E using Method 1.
- Then, go to the Home tab.
- After that, click on the Conditional Formatting drop-down on the Styles group of commands.
- Next, select New Rule from the dropdown list.
Immediately, the New Formatting Rule dialog box appears before us.
- Here, select Use a formula to determine which cells to format under the Select a Rule Type section.
- Following this, write down the following formula in the Format values where this formula is true box.
=E5=“Passed”
- Later, click on the Format button.
Consequently, the Format Cells wizard pops up.
- Then, go to the Fill tab.
- Moreover, choose Light Green as the Background Color.
- As usual, click OK.
Again, it returns us to the New Formatting Rule dialog box.
- Then, click OK.
Eventually, it highlights the cells containing the text Passed.
- Currently, repeat the above steps for the cells containing Failed. But the difference is the use of Red as the background color in this case.
Read More: How to Create a Complex Formula in Excel (with Easy Steps)
Applying Conditional Formatting Formula Based on Another Cell in Excel
While dealing with a large database, you may want to format some specific cells based on other cells or values to identify them quickly. In such a case, you can use conditional formatting to create a formatting formula. Here, we want to apply conditional formatting in the B5:E14 range based on cell C16. The whole row will get formatted and have the “Passed” status.
To do this effectively using this method, you may follow the below steps.
📌 Steps:
- Primarily, get the New Formatting Rule dialog box like before.
- Again, select Use a formula to determine which cells to format.
- In the Format values where this formula is true box, write down the following formula.
=$E5=$C$16
- Then, click Format.
- After that, choose the same color as before.
- Also, click OK.
- Lastly, click OK in the New Formatting Rule dialog box.
Presently, we can see the whole row with the preferred fill color, which contains the text Passed.
Read More: How to Create a Formula in Excel for Multiple Cells (9 Methods)
Practice Section
For doing practice by yourself, we have provided a Practice section like the one below on each sheet on the right side. Please do it by yourself.
Conclusion
This article explains how to use a continuous probability distribution in Excel in a simple and concise manner. Don’t forget to download the Practice file. Thank you for reading this article. We hope this was helpful. Please let us know in the comment section if you have any queries or suggestions. Please visit our website, Exceldemy, a one-stop Excel solution provider, to explore more.
