{"id":325,"date":"2011-10-05T10:32:07","date_gmt":"2011-10-05T10:32:07","guid":{"rendered":"http:\/\/www.microsofttraining.net\/b\/exceltraining\/?p=325"},"modified":"2024-05-12T01:22:54","modified_gmt":"2024-05-12T01:22:54","slug":"the-and-function-in-excel","status":"publish","type":"post","link":"https:\/\/www.stl-training.co.uk\/b\/the-and-function-in-excel\/","title":{"rendered":"The AND Function in Excel"},"content":{"rendered":"<p>This article describes the formula syntax and usage of the <strong>AND<\/strong> function (function: A prewritten formula that takes a value or values, performs an operation, and returns a value or values. Use functions to simplify and shorten formulas on a worksheet, especially those that perform lengthy or complex calculations.) in Microsoft Office Excel.<\/p>\n<h2>Description<\/h2>\n<p>Returns TRUE if all its arguments evaluate to TRUE; returns FALSE if one or more arguments evaluate to FALSE.<\/p>\n<p>One common use for the <strong>AND<\/strong> function is to expand the usefulness of other functions that perform logical tests. For example, the <strong>IF<\/strong> function performs a logical test and then returns one value if the test evaluates to TRUE and another value if the test evaluates to FALSE. By using the <strong>AND<\/strong> function as the<strong><em> logical_test<\/em><\/strong> argument of the <strong>IF<\/strong> function, you can test many different conditions instead of just one.<\/p>\n<h2>Syntax<\/h2>\n<pre>AND(logical1, [logical2], ...)<\/pre>\n<p>The <strong>AND<\/strong> function syntax has the following arguments (argument: A value that provides information to an action, an event, a method, a property, a function, or a procedure.):<\/p>\n<ul type=\"disc\">\n<li><strong>logical1<\/strong>\u00a0\u00a0\u00a0\u00a0Required. The first condition that you want to test that can evaluate to either TRUE or FALSE.<\/li>\n<li><strong>logical2, &#8230;<\/strong>\u00a0\u00a0\u00a0\u00a0Optional. Additional conditions that you want to test that can evaluate to either TRUE or FALSE, up to a maximum of 255 conditions.<\/li>\n<\/ul>\n<h2>Remarks<\/h2>\n<ul type=\"disc\">\n<li>The arguments must evaluate to logical values, such as TRUE or FALSE, or the arguments must be arrays (array: Used to build single formulas that produce multiple results or that operate on a group of arguments that are arranged in rows and columns. An array range shares a common formula; an array constant is a group of constants used as an argument.) or references that contain logical values.<\/li>\n<li>If an array or reference argument contains text or empty cells, those values are ignored.<\/li>\n<li>If the specified range contains no logical values, the <strong>AND<\/strong> function returns the #VALUE! error value.<\/li>\n<\/ul>\n<h2>Example 1<\/h2>\n<p>The example may be easier to understand if you copy it to a blank worksheet.<\/p>\n<p>Select the example in this article. If you are copying the example in Excel Web App, copy and paste one cell at a time.Important Do not select the row or column headers.<\/p>\n<div>\n<p>Selecting an example from Help<\/p>\n<ol type=\"1\">\n<li>Press CTRL+C.<\/li>\n<li>Create a blank workbook or worksheet.<\/li>\n<li>In the worksheet, select cell A1, and press CTRL+V. If you are working in Excel Web App, repeat copying and pasting for each cell in the example.<\/li>\n<\/ol>\n<p>Important For the example to work properly, you must paste it into cell A1 of the worksheet.<\/p>\n<ol type=\"1\">\n<li>To switch between viewing the results and viewing the formulas that return the results, press CTRL+` (grave accent), or on the Formulas tab, in the Formula Auditing group, click the Show Formulas button.<\/li>\n<\/ol>\n<p><a id=\"_GoBack\" name=\"_GoBack\"><\/a>After you copy the example to a blank worksheet, you can adapt it to suit your needs.<\/p>\n<hr align=\"center\" \/>\n<\/div>\n<table>\n<tbody>\n<tr>\n<td>\n<div>\n<table id=\"NestedTable1\">\n<tbody>\n<tr>\n<td>\u00a0<\/td>\n<\/tr>\n<tr>\n<th><strong>1<\/strong><\/th>\n<\/tr>\n<tr>\n<th><strong>2<\/strong><\/th>\n<\/tr>\n<tr>\n<th><strong>3<\/strong><\/th>\n<\/tr>\n<tr>\n<th><strong>4<\/strong><\/th>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n<\/td>\n<td>\n<div>\n<table id=\"NestedTable2\">\n<tbody>\n<tr>\n<th><strong>A<\/strong><\/th>\n<th><strong>B<\/strong><\/th>\n<th><strong>C<\/strong><\/th>\n<\/tr>\n<tr>\n<td><strong>Formula<\/strong><\/td>\n<td><strong>Description<\/strong><\/td>\n<td><strong>Result<\/strong><\/td>\n<\/tr>\n<tr>\n<td>=AND(TRUE, TRUE)<\/td>\n<td>All arguments are TRUE<\/td>\n<td>TRUE<\/td>\n<\/tr>\n<tr>\n<td>=AND(TRUE, FALSE)<\/td>\n<td>One argument is FALSE<\/td>\n<td>FALSE<\/td>\n<\/tr>\n<tr>\n<td>=AND(2+2=4, 2+3=5)<\/td>\n<td>All arguments evaluate to TRUE<\/td>\n<td>TRUE<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<h2>Example 2<\/h2>\n<p>The example may be easier to understand if you copy it to a blank worksheet.<\/p>\n<div>\n<ol type=\"1\">\n<li>Select the example in this article. If you are copying the example in Excel Web App, copy and paste one cell at a time.Important Do not select the row or column headers.<\/li>\n<\/ol>\n<p>Selecting an example from Help<\/p>\n<ol type=\"1\">\n<li>Press CTRL+C.<\/li>\n<li>Create a blank workbook or worksheet.<\/li>\n<li>In the worksheet, select cell A1, and press CTRL+V. If you are working in Excel Web App, repeat copying and pasting for each cell in the example.<\/li>\n<\/ol>\n<p>Important For the example to work properly, you must paste it into cell A1 of the worksheet.<\/p>\n<ol type=\"1\">\n<li>To switch between viewing the results and viewing the formulas that return the results, press CTRL+` (grave accent), or on the Formulas tab, in the Formula Auditing group, click the Show Formulas button.<\/li>\n<\/ol>\n<p><a id=\"_GoBack\" name=\"_GoBack\"><\/a>After you copy the example to a blank worksheet, you can adapt it to suit your needs.<\/p>\n<hr align=\"center\" \/>\n<\/div>\n<table>\n<tbody>\n<tr>\n<td>\n<div>\n<table id=\"NestedTable1\">\n<tbody>\n<tr>\n<td>\u00a0<\/td>\n<\/tr>\n<tr>\n<th><strong>1<\/strong><\/th>\n<\/tr>\n<tr>\n<th><strong>2<\/strong><\/th>\n<\/tr>\n<tr>\n<th><strong>3<\/strong><\/th>\n<\/tr>\n<tr>\n<th><strong>4<\/strong><\/th>\n<\/tr>\n<tr>\n<th><strong><br \/>\n<\/strong><strong><br \/>\n5<\/strong><\/th>\n<\/tr>\n<tr>\n<th><strong><br \/>\n<\/strong><strong><br \/>\n<\/strong><strong><br \/>\n<\/strong><strong><br \/>\n6<\/strong><\/th>\n<\/tr>\n<tr>\n<th><strong><br \/>\n<\/strong><strong><br \/>\n<\/strong><strong><br \/>\n7<\/strong><\/th>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n<\/td>\n<td>\n<div>\n<table id=\"NestedTable2\">\n<tbody>\n<tr>\n<th><strong>A<\/strong><\/th>\n<th><strong>B<\/strong><\/th>\n<th><strong>C<\/strong><\/th>\n<\/tr>\n<tr>\n<td><strong>Data<\/strong><\/td>\n<td>\u00a0<\/td>\n<td>\u00a0<\/td>\n<\/tr>\n<tr>\n<td>50<\/td>\n<td>\u00a0<\/td>\n<td>\u00a0<\/td>\n<\/tr>\n<tr>\n<td>104<\/td>\n<td>\u00a0<\/td>\n<td>\u00a0<\/td>\n<\/tr>\n<tr>\n<td><strong>Formula<\/strong><\/td>\n<td><strong>Description<\/strong><\/td>\n<td><strong>Result<\/strong><\/td>\n<\/tr>\n<tr>\n<td>=AND(1&lt;A2, A2&lt;100)<\/td>\n<td>Displays TRUE if the number in cell A2 is between 1 and 100. Otherwise, it displays FALSE.<\/td>\n<td>TRUE<\/td>\n<\/tr>\n<tr>\n<td>=IF(AND(1&lt;A3, A3&lt;100), A3, &#8220;The value is out of range.&#8221;)<\/td>\n<td>Displays the number in cell A3, if it is between 1 and 100. Otherwise, it displays the message &#8220;The value is out of range.&#8221;<\/td>\n<td>The value is out of range.<\/td>\n<\/tr>\n<tr>\n<td>=IF(AND(1&lt;A2, A2&lt;100), A2, &#8220;The value is out of range.&#8221;)<\/td>\n<td>Displays the number in cell A2, if it is between 1 and 100. Otherwise, it displays a message.<\/td>\n<td>50<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n","protected":false},"excerpt":{"rendered":"<p>This article describes the formula syntax and usage of the AND function (function: A prewritten formula that takes a value or values, performs an operation, and returns a value or values. Use functions to simplify and shorten formulas on a worksheet, especially those that perform lengthy or complex calculations.) in Microsoft Office Excel. Description Returns [&hellip;]<\/p>\n","protected":false},"author":5,"featured_media":0,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_jetpack_memberships_contains_paid_content":false,"footnotes":""},"categories":[4],"tags":[433,531],"class_list":["post-325","post","type-post","status-publish","format-standard","hentry","category-excel-training","tag-pr","tag-the-and-function-in-excel"],"jetpack_featured_media_url":"","jetpack_sharing_enabled":true,"_links":{"self":[{"href":"https:\/\/www.stl-training.co.uk\/b\/wp-json\/wp\/v2\/posts\/325","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.stl-training.co.uk\/b\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.stl-training.co.uk\/b\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.stl-training.co.uk\/b\/wp-json\/wp\/v2\/users\/5"}],"replies":[{"embeddable":true,"href":"https:\/\/www.stl-training.co.uk\/b\/wp-json\/wp\/v2\/comments?post=325"}],"version-history":[{"count":2,"href":"https:\/\/www.stl-training.co.uk\/b\/wp-json\/wp\/v2\/posts\/325\/revisions"}],"predecessor-version":[{"id":7540,"href":"https:\/\/www.stl-training.co.uk\/b\/wp-json\/wp\/v2\/posts\/325\/revisions\/7540"}],"wp:attachment":[{"href":"https:\/\/www.stl-training.co.uk\/b\/wp-json\/wp\/v2\/media?parent=325"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.stl-training.co.uk\/b\/wp-json\/wp\/v2\/categories?post=325"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.stl-training.co.uk\/b\/wp-json\/wp\/v2\/tags?post=325"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}