Excel AND Function
Excel AND Function is used to test whether all the conditions in a test are TRUE. The AND function returns TRUE if all its arguments evaluated are TRUE, and returns FALSE if one or more arguments evaluated is FALSE. If both scores in Math and Statistics are more than 80 {=AND(C6>80, D6>80) in our example below}, the AND function will return TRUE; otherwise, if any one of the scores is less than 80, AND function will return FALSE.
In this section:
- Syntax of AND Function
- Example 1: Test if value is greater than and less a value
- Example 2: Nested AND
- Example 3: AND with OR
- Example 4: AND with IF
- Example 5: IF(AND) to calculate letter grades
1. Syntax of AND Function:
AND(logical1, [logical2], …)
where:
Logical1: The first condition to be tested to either TRUE or FALSE. It is a REQUIRED argument.
Logical2: Additional condition that users want to test that can evaluated either TRUE or FALSE.
2. Example 1: Test if value is greater than and less a value:
To test whether a value is greater than and less than a value, the formula is =AND(C6>80, C6<96), which returns TRUE if C6>80 and C6<96; otherwise FALSE.
3. For multiple AND Conditions, use nested AND:
For multiple AND conditions, we can use nested AND. For example, we want to award students with scholarship based on the following conditions:
- Math scores must be more than 80,
- Score in Accounting must be more than 75,
- Must do some community service, and
- Student’s age must be greater than or equal to 21.
The formula to return TRUE if all the conditions are met is =AND(D6>80, E6>75, AND(F6=”Yes”, G6>=21)), which return TRUE if all the conditions are met.
4. AND Function with OR:
We can combine And function with OR. For example, we want to select students based on the following conditions:
- Score in Math must be more than 80,
- Score in Accounting must be more than 75, and
- Students should be engaged in community service OR their age must be 21 or less.
The formula to return TRUE if all the conditions are met is =AND(D6>80, D6>75, OR(F6=”Yes”, G6<21)).
5. Embed AND inside IF function:
The AND function can be embedded with IF. In our example, if a student scores more than or equal to 80 in both Math and Statistics, she will receive award. The formula (in F5) is =IF(AND(D5>=80, E5>=80), “Award”, “No Award”), which returns results in column F.
6. IF(AND) to calculate letter grades
Users can calculate letter grades with the combination of IF and AND. The formula is
More Readings:
Thanks on your marvelous posting! I certainly enjoyed reading it, you will be a great author.I will make sure to bookmark your blog and definitely will come back later in life. I want to encourage you to ultimately continue your great work, have a nice day!
I am continuously invstigating online for posts that can facilitate me. Thx!
I am incessantly thought about this, thanks for putting up.
I really like your writing style, superb info , thanks for posting : D.
I found your weblog website on google and verify a number of of your early posts. Continue to maintain up the superb operate. I simply additional up your RSS feed to my MSN Information Reader. Looking for forward to reading extra from you later on!…
I went over this web site and I conceive you have a lot of wonderful info, saved to my bookmarks (:.
Great tremendous issues here. I?¦m very glad to see your article. Thank you a lot and i’m having a look forward to touch you. Will you please drop me a e-mail?
Thank you for your article.Thanks Again.
Thanks for another informative site. The place else may I am getting that type of info written in such an ideal method? I have a challenge that I’m just now working on, and I have been at the look out for such info.
I’ve been absent for some time, but now I remember why I used to love this blog. Thanks , I will try and check back more frequently. How frequently you update your website?
Regards for this post, I am a big fan of this site would like to keep updated.
Pretty great post. I just stumbled upon your blog and wished to mention that I have truly loved browsing your weblog posts. In any case I will be subscribing for your feed and I am hoping you write again soon!