Meraz Al Nahian
The article will show you how to use IFS and AND function together in Excel. The IFS function is an alternative to using the nested IF functions. The advantage of using the IFS function is that we don’t need to use the function repeatedly in the formula. The AND function will do the logical test here. Please stay tuned to this article to see the application of the combined IFS and AND functions.
Syntax of IFS and AND Functions
The syntax for the IFS function is given below.
=IFS(logical_test1, value_if_true1, [logical_test2, value_if_true2],...)The IFS function can test several arguments at a time whether it is true or not. If the argument meets the criteria, the function returns the ‘value_if_true’ value. Otherwise, it returns the Value not Available Error (#N/A). To get rid of this problem, we state the logical_test that does not meet any arguments of the system as TRUE and then put the ‘value_if_true’ value for that logical_test.
Let’s have a simple example to illustrate this issue. Suppose, you want Apple as an output in cell B5 if you type A in B4. For any other character, it will return nothing. So, the first logical_test will be if the cell value of B4 matches with A. We can express this logical_test as B4=“A”. The next thing will be the output (Apple) we want if the cell value of B4 matches the condition. We write this as “Apple” after the logical_test. The next logical_test will be if the value in B4 does not match with the letter A. Following the description above, we can express this as TRUE and put a space (“ ”) in the value_if_true argument.
The formula will be look like this
=IFS(B4="A","Apple",TRUE,"")
The syntax for the AND function is given below.
=AND(logical1, [logical2])The function checks whether all the arguments are TRUE, and returns TRUE if all arguments are TRUE. Otherwise, it returns FALSE.
How to Use IFS and AND Functions Together in Excel: 3 Examples
I’ve used two types of datasets in this article to show how to use the IFS and AND functions together in Excel. Please go through the following sections to understand the process.
1. Applying Excel IFS and AND Functions Together to Determine CGPA
Suppose you are a teacher and you want to determine the Grade Point of the students based on their Obtained Marks. The following procedure will guide you on this matter.
Steps:
- First, make a column to store the Grade Points and type the following formula in cell D5 and press the ENTER It will return the corresponding Grade Point for the obtained marks of Lula.
=IFS(AND(C5>=80,C5<=100)=TRUE,4,AND(C5<80,C5>75)=TRUE,3.75,AND(C5>70,C5<=75)=TRUE,3.5,AND(C5>65,C5<=70)=TRUE,3.25,AND(C5>60,C5<=65)=TRUE,3,AND(C5>55,C5<=60)=TRUE,2.75,AND(C5>50,C5<=55)=TRUE,2.5,AND(C5>45,C5<=50)=TRUE,2.25,AND(C5>40,C5<=45)=TRUE,2,TRUE,0)
Formula Breakdown
Although the formula looks horrible, you can find it easy if you go through the arguments one by one. The arguments are given below. The formula returns the CGPA of the matched argument based on the Mark in C5.
- AND(C5>=80,C5<=100)=TRUE, 4 —-> returns the Grade Point 4.00 if the mark is in the range [80, 100].
- AND(C5<80,C5>75)=TRUE, 3.75 —-> returns the Grade Point 3.75 if the mark is in the range (75, 80).
- AND(C5>70,C5<=75)=TRUE, 3.5 —-> returns the Grade Point 3.5 if the mark is in the range (70, 75].
- AND(C5>65,C5<=70)=TRUE, 3.25 —-> returns the Grade Point 3.25 if the mark is in the range (65, 70].
- AND(C5>60,C5<=65)=TRUE, 3 —-> returns the Grade Point 3.00 if the mark is in the range (60, 65].
- AND(C5>55,C5<=60)=TRUE, 2.75 —-> returns the Grade Point 2.75 if the mark is in the range (55, 60].
- AND(C5>50,C5<=55)=TRUE, 2.5 —-> returns the Grade Point 2.50 if the mark is in the range (50, 55].
- AND(C5>45,C5<=50)=TRUE, 2.25 —-> returns the Grade Point 2.25 if the mark is in the range (45, 50].
- AND(C5>40,C5<=45)=TRUE, 2 —-> returns the Grade Point 2.00 if the mark is in the range (40, 45].
- TRUE, 0 —-> returns the Grade Point 0.00 if the mark is below 40.
- After that, use the Fill Handle to AutoFill the lower cells.
Thus you can determine the CGPAs of the students using the IFS and AND functions together.
Read More: How to Use IF with AND Function in Excel
2. Conditional Formatting Using Excel IFS and AND Functions Together
We can also do Conditional Formatting using IFS and AND functions. Say we want the CGPAs 4.00 and 3.75 to be filled with Green background color. Let’s go through the discussion below to see how we can achieve this. We will use the dataset that we got in Method 1.
Steps:
- First, select the range D5:D14 and then go to Home >> Conditional Formatting >> New Rule.
- Then, select the Use a formula to determine which cells to format option in the New Formatting Rule
- Next, type the following formula in the ‘Format values where this formula is true’ section and click on Format.
=IFS(AND(D5>3.75,D5<=4)=TRUE,TRUE,AND(D5>3.5,D5<=3.75)=TRUE,TRUE)
The formula fills the cells with a color that satisfies the condition, which is the cells containing the CGPAs 4.00 and 3.75.
- Thereafter, select the fill color from the Fill tab of the Format Cells Here we chose Green.
- In addition, you may format the Font too if you want.
- Later, click OK.
- The New Formatting Rule window will again pop up with a preview of how the formatted cells will look like.
- Just click OK.
- After this operation, you will see the Grade Points 4.00 and 75 filled with Green color.
Thus you can perform Conditional Formatting using the IFS and AND functions together in Excel.
Read More: How to Use Conditional Formatting with AND Function in Excel
3. Using Excel IFS and AND Functions Together to Consider Bonus for Employee
Suppose, you want to motivate your employees by giving them Bonuses to get their best effort. So you made a criteria: If someone works more than 47 hours a week, you will certainly give him a bonus. If someone works between 44 to 47 hours, you will decide later whether you give him a bonus or not. The other employees won’t receive any bonus. Let’s go through the description below to see how we can do this.
Steps:
- First, make a column to store the Bonus Eligibility of your employees and type the following formula in cell D5 and press ENTER. The formula will show you the Bonus Eligibility of Lula.
=IFS(AND(C5>47,C5<=53)=TRUE,"Yes",AND(C5<=47,C5>44)=TRUE,"Under Consideration",TRUE,"No")
The formula will return the Bonus Eligibility for Lula. As he worked not more than 44 hours, he won’t get a bonus. And so, his Bonus Eligibility is “No”.
- After that, use the Fill Handle to AutoFill the lower cells.
Thus you can use the IFS and AND functions for official purposes too.
Read More: How to Return TRUE or FALSE Using Excel AND Function
Practice Section
Here, I’m giving you the dataset of this article so that you can practice these methods on your own.
Download Practice Workbook
Conclusion
In the end, you will learn the basic idea of how to use IFS and AND function together in Excel after reading this article. If you have any better suggestions, questions, or feedback regarding this article, please share them in the comment box. This will help me enrich my upcoming articles.
