• Skip to primary navigation
  • Skip to main content
  • Skip to footer
WallStreetMojo

Wallstreet Mojo

Wallstreet Mojo

MENUMENU
  • Resources
        • Excel

          • Excel Functions
          • Excel Tools
          • Excel Tips
        • Excel
        • Financial Functions Excel

          • NPV in Excel
          • IRR in excel
          • PV in Excel
        • Financial-Functions-Excel
        • Lookup Functions Excel

          • VLOOKUP
          • HLOOKUP
          • Index Function
        • Lookup-Functions-in-Excel
        • Excel Charts

          • Pareto Chart in Excel
          • Gannt Chart in Excel
          • Waterfall Chart in Excel
        • Excel-Charts
        • VBA

          • VBA Left Function
          • VBA Paste Special
          • VBA Worksheet Function
        • VBA
        • Others

          • Resources (A to Z)
          • Financial Modeling
          • Equity Research
          • Private Equity
          • Corporate Finance
          • Financial Statement Analysis
  • Free Courses
  • All Courses
        • Certification Courses

          Excel Course certificate
        • Excel VBA All in One Bundle

          Excel-VBA-Certification-Course
        • Excel Data Analysis Course

          Excel-Data-Analysis-Course
        • VBA Macros Course

          VBA-Training-Course
        • Others

          • Basic Excel Training
          • Advanced Excel Course
          • Tableau Certification Course
          • Excel for Finance Course

          • Excel for Marketers Course
          • Excel for HR Managers
          • Excel for Power Users
          • View All
  • Excel VBA All in One Bundle
  • Login

AND Function in Excel

Home » Excel » Logical Functions in Excel » AND Function in Excel

By Roshan Waingankar Leave a Comment

AND Function in Excel

AND Excel Function (Table of Contents)

  • AND Function in Excel
  • And Formula in Excel
  • How to Use AND Function?

AND Function in Excel

AND function in Excel is categorized as a logical function; it returns two values only that are TRUE and FALSE. This AND in excel tests the condition that is specified and returns TRUE, if conditions are met as TRUE, else it returns FALSE. This function is often used with other Excel functions, AND in Excel can significantly broaden the abilities of the worksheet.

And Formula in Excel

AND Function Formula

This AND Formula could also be written as

AND( condition1, [condition2],…)

Recommended Courses

  • Online Certification Training in Financial Analyst
  • Course on Excel
  • VBA Macros Training Bundle

Explanation of AND Function in Excel

logical 1 is the first condition or the logical value to evaluate

logical 2 is the optional argument, is the second condition or logical value to evaluate

There can be more logical expressions (conditions) to test depending on the situations and requirement.

AND function in Excel returns TRUE if all conditions are TRUE. It returns FALSE if any of the condition is FALSE.

AND Function formula explanation

If all the logical expressions evaluate to TRUE (first case), the AND function in excel will return TRUE and if any of the logical expression evaluates to FALSE (second, third and fourth case), the AND in excel will return FALSE.

If the logical expression argument evaluates to numbers, instead of Boolean logical value TRUE/FALSE, the value zero is treated as FALSE and all non-zero value as TRUE.

AND Function formula explanation 1

And function in excel is often used with functions like IF, OR and other functions.

 

How to Use AND Function in Excel

AND Function in Excel is very simple and easy to use. Let’s understands the working of AND Function in excel with examples.

You can download this AND Function Excel Template here – AND Function Excel Template

AND in Excel Example #1

There are 10 players and each have to clear all 3 levels to be a winner, that is if the player clear level 1, level 2 and level 3, he/she will be a winner else if fails to clears any of the level he/she will fail and will not be declared as a winner.

Popular Course in this category
Cyber Monday Sale
All in One Excel VBA Bundle (35 Courses with Projects) 35+ Courses | 120+ Hours | Full Lifetime Access | Certificate of Completion
4.9 (1,353 ratings)
Course Price

View Course

Related Courses
VBA Macros CourseAdvanced Excel CourseTableau Certification Course

AND Function Example 1

We will use the AND function on excel to check if any of the levels is not clear he/she is not a winner then. So, applying the AND formula in E column. 

AND Function Example 1-1Output:AND Function Example 1-2

Player 2 and player 10 have cleared all the levels, hence all logical conditions are TRUE, therefore, the AND Excel functions gave the output TRUE and for rest if any level out of all three was not cleared the logical expression evaluated as FALSE and the AND in excel resulted in FALSE output.

AND Function Example 1-3

AND in Excel Example #2

Using AND Excel function with IF function

There is a list of students with their percentages and we want to find out the grades, where the grade criteria are shown below in the table:

AND Function Example 2
If the percentage is greater than 90%, then grade will be A+, when the percentage is greater than or equal to 90% and less than 80% then grade will be A, and similarly, other Grades based on percentage and a student is Fail is percentage is less than 40%. 

AND Function Example 2-1

Formula that we will use is

=IF(B2>90,”A+”,IF(AND(B2<=90,B2>80),”A”,IF(AND(B2<=80,B2>75),”B+”,IF(AND(B2<=75,B2>70),”B”,IF(AND(B2<=70,B2>60),”C+”,IF(AND(B2<=60,B2>50),”C”,IF(AND(B2<=50,B2>40),”D”,”FAIL”)))))))

We have used Nested IF’s with multiple AND excel functions to compute the Grade of the student.

IF formula tests the condition and gives the output, for the condition value if TRUE else it returns the value if FALSE.

=if(logical_test, [value_if_TRUE],[value_if_FALSE])

First logical test, in this case, is B2>90, if this is TRUE then Grade is ‘A+’, if this condition is not TRUE if checks for the statement after comma and it again entered another if condition, in which we have to test 2 conditions that is if percentage is greater than 80 and percentage less than or equal to 90 so, logical statements are

B2<=90, B2>80

Since we have to test two conditions together we have used AND Excel function. So, if B2<=90 and B2>80, is TRUE the Grade will be ‘A’, if this condition is not TRUE IF checks for the statement after the comma and it again enters another IF condition and will continue to check each statement till the last bracket closes. In the last IF statement it checks, percentage <=50 and percentage >40, hence last IF statement evaluates Grade ‘D’ else evaluate ‘FAIL’.

AND Function Example 2-2

AND Function Example 2-3

AND in Excel Example #3

Using AND excel function with IF function

There is a list of employees and the sale amount in Column B, on the basis of their sale amount the incentives are allotted to each employee. Sale incentive criteria are

AND Function Example 3

We need to calculate the incentive of each employee based on his performance for cracking the sale base on the criteria mentioned above in the table

AND Function Example 3-1

Roman made a sale of $3000, he will receive an incentive amount of $400 and David and Tom were not able to even crack $1000 sale the bare minimum sale criteria for an incentive, hence they will not get any incentive.

Formula that we will use is

=IF(AND(B2>=3000),400,IF(AND(B2>=2000,B2<3000),200,IF(AND(B2>=1500,B2<2000),150,IF(AND(B2>=1000,B2<1500),100,0))))

We have again used Nested IF conditions with multiple AND excel function testing the all the logical condition together in the formula above to compute the sale incentive of the employee based the sale amount that he/she made.

AND Function Example 3-2

AND Function Example 3-3

David and Tom were not able to meet the incentive criteria hence their incentive amount is $0.

Nesting of AND Function in Excel

Nesting means using the AND in excel as an argument in the function itself. Nesting functions in Excel refer to using one function inside another function. Excel allows you to nest up to 64 levels of functions.

AND in Excel Example #4

There is a list of candidates who appeared for posting in Army for which there are certain selection conditions.

Eligibility Criteria: Age has to be greater than or equal to 18 but less than 35 years with a height greater than 167 cms, eyesight has to be normal and has successfully completed the long run task

AND Function Example 4

Here, we will be using the Nested AND in Excel to check the eligibility criteria.

Formula that we will use is

=AND(B2=”Normal”,C2>167,D2=”Successful”,AND(E2>=18,E2<35))

As, you can see we have, used multiple logical conditions, to check if they evaluate to be TRUE and for age, we have again used AND function in Excel to check if age is greater than or equal to 18 and less than 35 years.

We have used AND function in excel inside AND function to check the age criteria.

AND Function Example 4-1Output:AND Function Example 4-2

Three candidates passed all the selection criteria, Ralph, Alex and Scott. Hence, their eligibility computes out as TRUE using the AND function in Excel and for rest of the candidates FALSE. 

AND Function Example 4-3

Limitation of AND Function in Excel

The AND function in Excel can test multiple conditions not exceeding more than 255 conditions. In Excel version 2003, the AND in excel could test up to 30 arguments, but in Excel version 2007 and later it can handle up to 255 arguments.

The AND function in excel returns #Value! Error if logical conditions are passed as a text or string.

AND Function Example 5

Recommended Articles

This has been a guide to AND Function in Excel. Here we discuss the AND Formula in Excel and how to use AND Function in Excel along with practical examples and downloadable excel templates. You may also look at these useful functions in excel –

  • VBA Boolean Data Type
  • How to Use IF AND Formula in Excel?
  • How to use Logical Function in Excel?
  • Nested IF
  • Fill Handle in Excel
  • Excel Record Macros
  • IF Excel Function
  • OR Excel Function
4 Shares
Share
Tweet
Share
All in One Excel VBA Bundle (35 Courses with Projects)
  • 35+ Courses
  • 120+ Hours
  • Full Lifetime Access
  • Certificate of Completion
LEARN MORE >>

Filed Under: Excel, Logical Functions in Excel

Reader Interactions
Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Footer
COMPANY
About
Reviews
Blog
Contact
Privacy
Terms of Service
FREE COURSES
Free Finance Online Course
Free Accounting Online Course
Free Online Excel Course
Free VBA Course
Free Investment Banking Course
Free Financial Modeling Course
Free Ratio Analysis Course

CERTIFICATION COURSES
All Courses
Financial Analyst All in One Course
Investment Banking Course
Financial Modeling Course
Private Equity Course
Business Valuation Course
Equity Research Course
CFA Level 1 Course
CFA Level 2 Course
Venture Capital Course
Microsoft Excel Course
VBA Macros Course
Accounting Course
Advanced Excel Course
Fixed Income Course
RESOURCES
Investment Banking
Financial Modeling
Equity Research
Private Equity
Excel
Books
Certifications
Accounting
Asset Management
Risk Management

Copyright © 2019. CFA Institute Does Not Endorse, Promote, Or Warrant The Accuracy Or Quality Of WallStreetMojo. CFA® And Chartered Financial Analyst® Are Registered Trademarks Owned By CFA Institute.
Return to top

Free Excel Course

Excel functions, Formula, Charts, Formatting creating excel dashboard & others

By continuing above step, you agree to our Terms of Use and Privacy Policy.

Free Excel Course

Excel functions, Formula, Charts, Formatting creating excel dashboard & others

By continuing above step, you agree to our Terms of Use and Privacy Policy.
WallStreetMojo

Free Investment Banking Course

IB Excel Templates, Accounting, Valuation, Financial Modeling, Video Tutorials

By continuing above step, you agree to our Terms of Use and Privacy Policy.

* Please provide your correct email id. Login details for this Free course will be emailed to you

WallStreetMojo

Free Excel Course

Excel functions, Formula, Charts, Formatting creating excel dashboard & others

By continuing above step, you agree to our Terms of Use and Privacy Policy.

* Please provide your correct email id. Login details for this Free course will be emailed to you

WallStreetMojo

Free Excel Course

Excel functions, Formula, Charts, Formatting creating excel dashboard & others

By continuing above step, you agree to our Terms of Use and Privacy Policy.

* Please provide your correct email id. Login details for this Free course will be emailed to you

WallStreetMojo

Free Excel Course

Excel functions, Formula, Charts, Formatting creating excel dashboard & others

By continuing above step, you agree to our Terms of Use and Privacy Policy.

* Please provide your correct email id. Login details for this Free course will be emailed to you

WallStreetMojo

Download AND Function Excel Template

By continuing above step, you agree to our Terms of Use and Privacy Policy.

CYBER WEEK OFFER - All in One Excel VBA Bundle (35 Courses with Projects) View More