{"id":2190,"date":"2015-03-23T12:29:01","date_gmt":"2015-03-23T01:29:01","guid":{"rendered":"http:\/\/excelatfinance.com\/online\/?page_id=2190"},"modified":"2019-01-06T12:28:35","modified_gmt":"2019-01-06T01:28:35","slug":"2190-2","status":"publish","type":"page","link":"https:\/\/excelatfinance.com\/online\/2190-2\/","title":{"rendered":"Logical functions"},"content":{"rendered":"<style type=\"text\/css\">\n.tftablex {color:#333333; margin-left:36px; width: 810px !important;border-width: 1px;border-color: #729ea5;border-collapse: collapse;}\n.tftablex th {background-color:#acc8cc;border-width: 1px;padding: 8px;border-style: solid;border-color: #729ea5;text-align:left;}\n.tftablex tr {background-color:#d4e3e5;}\n.tftablex td {border-width: 1px;padding: 8px;border-style: solid;border-color: #729ea5;}\n.tftablex tr:hover {background-color:#ffffff;}\n<\/style>\n<p><?php include \"..\/includes\/access.php\"; ?><\/p>\n<h2>Logical<\/h2>\n<p>All logical functions evaluate the existence of a Boolean value TRUE or FALSE value, and comprise of a logical_test based on a comparison operator.<\/p>\n<h2>Comparison operators<\/h2>\n<table class=\"tftablex\" border=\"1\">\n<tr>\n<th>Excel comparison operators<\/th>\n<th>Description<\/th>\n<\/tr>\n<tr>\n<td width=\"30%\"><strong>=<\/strong> equals (eq)<\/td>\n<td width=\"70%\">Returns TRUE if Value1 is equal to Value2. eg. Value1=Value2<\/td>\n<\/tr>\n<tr>\n<td width=\"30%\"><strong>&lt;<\/strong> less than (lt)<\/td>\n<td width=\"70%\">Returns TRUE if Value1 is less than Value2. eg. Value1&lt;Value2<\/td>\n<\/tr>\n<tr>\n<td width=\"30%\"><strong>&gt;<\/strong> greater than (gt)<\/td>\n<td width=\"70%\">Returns TRUE if Value1 is greater than Value2. eg. Value1&gt;Value2<\/td>\n<\/tr>\n<tr>\n<td width=\"30%\"><strong>&lt;=<\/strong> less than or equals (lte)<\/td>\n<td width=\"70%\">Returns TRUE if Value1 is less than or equal to Value2. eg. Value1&lt;=Value2<\/td>\n<\/tr>\n<tr>\n<td width=\"30%\"><strong>&gt;=<\/strong> greater than or equals (gte)<\/td>\n<td width=\"70%\">Returns TRUE if Value1 is greater than or equal to Value2. eg. Value1&gt;=Value2<\/td>\n<\/tr>\n<tr>\n<td width=\"30%\"><strong>&lt;&gt;<\/strong> not equal (ne)<\/td>\n<td width=\"70%\">Returns TRUE if Value1 is not equal to Value2. eg. Value1&lt;&gt;Value2<\/td>\n<\/tr>\n<\/table>\n<p><\/p>\n<h2>Logical functions<\/h2>\n<p>Logical functions are grouped under the Logical category in the Excel function library, and the Insert Function dialog box.<\/p>\n<table class=\"tftablex\" border=\"1\">\n<tr>\n<th>Excel logical  functions <\/th>\n<th>Description<\/th>\n<\/tr>\n<tr>\n<td width=\"30%\"><strong>IF<\/strong><\/td>\n<td width=\"70%\">Returns one value if the logical test is TRUE, and another value if the logical test is FALSE<\/td>\n<\/tr>\n<tr>\n<td width=\"30%\"><strong>AND<\/strong><\/td>\n<td width=\"70%\">Returns TRUE if all arguments return TRUE<\/td>\n<\/tr>\n<tr>\n<td width=\"30%\"><strong>OR<\/strong><\/td>\n<td width=\"70%\">Returns TRUE if any argument returns TRUE<\/td>\n<\/tr>\n<tr>\n<td width=\"30%\"><strong>XOR<\/strong><br \/>[eXclusive OR; New in Excel 2013]<\/td>\n<td width=\"70%\">Returns the logical Exclusive Or of all arguments<\/td>\n<\/tr>\n<tr>\n<td width=\"30%\"><strong>TRUE<\/strong> <\/td>\n<td width=\"70%\">Returns the logical value TRUE<\/td>\n<\/tr>\n<tr>\n<td width=\"30%\"><strong>FALSE<\/strong> <\/td>\n<td width=\"70%\">Returns the logical value FALSE<\/td>\n<\/tr>\n<tr>\n<td width=\"30%\"><strong>NOT<\/strong> <\/td>\n<td width=\"70%\">Returns the reverse of its logical argument<\/td>\n<\/tr>\n<p><?php \/* \n\n<tr>\n<td width=\"30%\"><strong>IFERROR<\/strong><\/td>\n<td width=\"70%\">Returns the value you specify if a formula evaluates to an error; otherwise,<br \/>\n\treturns the result of the formula<\/td>\n<\/tr>\n<tr>\n<td width=\"30%\"><strong>IFNA<\/strong><\/td>\n<td width=\"70%\">Returns the logical TRUE if value refers to a #N\/A error, else it returns FALSE<\/td>\n<\/tr>\n<p> *\/ ?><\/p>\n<\/table>\n<p><\/p>\n<h2>Examples<\/h2>\n<h3>IF function<\/h3>\n<ul style=\"list-style:none\">\n<li>IF function<\/li>\n<li>SYNTAX: <code class=\"xl\"> IF(logical_test, [value_if_true] [,value_if_false])<\/code><\/li>\n<\/ul>\n<p>Example: Suppose that the vector named \\(Vector\\) contains the series \\(\\{1,2,&#8230;9,10\\}\\) with count of 10 and average value of 5.5. We will now use a series of logical tests to determine if the values 3, and 7 are less than the average, 5.5.<\/p>\n<ul style=\"list-style:none\">\n<li><strong>Logical<\/strong><\/li>\n<li><code class=\"xl\"> =3&lt;AVERAGE(Vector)<\/code> returns TRUE<\/li>\n<li><code class=\"xl\"> =7&lt;AVERAGE(Vector)<\/code> returns FALSE<\/li>\n<\/ul>\n<p>The IF function has optional arguments, but is most commonly used with three arguments.<\/p>\n<ul style=\"list-style:none\">\n<li><strong>IF (one argument)<\/strong><\/li>\n<li><code class=\"xl\"> =IF(3&lt;AVERAGE(Vector),)<\/code> returns 0<\/li>\n<li><code class=\"xl\"> =IF(7&lt;AVERAGE(Vector),)<\/code> returns FALSE<\/li>\n<li>IF with one argument provides NO advantages compared to the simple logical operator statement above.<\/li>\n<li>In addition, IF with one argument requires a trailing comma, this is rather unusual; and if the logical_test is TRUE, the function returns 0 (ZERO), rather than the logical TRUE<\/li>\n<\/ul>\n<ul style=\"list-style:none\">\n<li><strong>IF (two arguments)<\/strong><\/li>\n<li><code class=\"xl\"> =IF(3&lt;AVERAGE(Vector),\"Low\")<\/code> returns the text string Low<\/li>\n<li><code class=\"xl\"> =IF(7&lt;AVERAGE(Vector),\"Low\")<\/code> returns FALSE because the third argument has been omitted<\/li>\n<\/ul>\n<ul style=\"list-style:none\">\n<li><strong>IF (three arguments)<\/strong><\/li>\n<li><code class=\"xl\"> =IF(3&lt;AVERAGE(Vector),\"Low\",\"High\")<\/code> returns the text string Low<\/li>\n<li><code class=\"xl\"> =IF(7&lt;AVERAGE(Vector),\"Low\",\"High\")<\/code> returns the text string High<\/li>\n<\/ul>\n<h3>Nested IF functions<\/h3>\n<p>A example &#8211; a university handbook describes the marks and grades relationship as shown in figure 1.<\/p>\n<figure><a href=\"https:\/\/i0.wp.com\/excelatfinance.com\/online\/wp-content\/uploads\/2015\/03\/xlf-marks-grades-range.png\"><img data-attachment-id=\"2204\" data-permalink=\"https:\/\/excelatfinance.com\/online\/2190-2\/xlf-marks-grades-range\/\" data-orig-file=\"https:\/\/i0.wp.com\/excelatfinance.com\/online\/wp-content\/uploads\/2015\/03\/xlf-marks-grades-range.png?fit=168%2C180&amp;ssl=1\" data-orig-size=\"168,180\" data-comments-opened=\"0\" data-image-meta=\"{&quot;aperture&quot;:&quot;0&quot;,&quot;credit&quot;:&quot;&quot;,&quot;camera&quot;:&quot;&quot;,&quot;caption&quot;:&quot;&quot;,&quot;created_timestamp&quot;:&quot;0&quot;,&quot;copyright&quot;:&quot;&quot;,&quot;focal_length&quot;:&quot;0&quot;,&quot;iso&quot;:&quot;0&quot;,&quot;shutter_speed&quot;:&quot;0&quot;,&quot;title&quot;:&quot;&quot;,&quot;orientation&quot;:&quot;0&quot;}\" data-image-title=\"xlf-marks-grades-range\" data-image-description=\"\" data-image-caption=\"\" data-medium-file=\"https:\/\/i0.wp.com\/excelatfinance.com\/online\/wp-content\/uploads\/2015\/03\/xlf-marks-grades-range.png?fit=168%2C180&amp;ssl=1\" data-large-file=\"https:\/\/i0.wp.com\/excelatfinance.com\/online\/wp-content\/uploads\/2015\/03\/xlf-marks-grades-range.png?fit=168%2C180&amp;ssl=1\" decoding=\"async\" loading=\"lazy\" src=\"https:\/\/i0.wp.com\/excelatfinance.com\/online\/wp-content\/uploads\/2015\/03\/xlf-marks-grades-range.png?resize=151%2C162\" alt=\"xlf-marks-grades-range\" width=\"151 .2\" height=\"162 .0\" class=\"alignnone size-full wp-image-2204\" srcset=\"https:\/\/i0.wp.com\/excelatfinance.com\/online\/wp-content\/uploads\/2015\/03\/xlf-marks-grades-range.png?w=168&amp;ssl=1 168w, https:\/\/i0.wp.com\/excelatfinance.com\/online\/wp-content\/uploads\/2015\/03\/xlf-marks-grades-range.png?resize=140%2C150&amp;ssl=1 140w\" sizes=\"(max-width: 168px) 100vw, 168px\" data-recalc-dims=\"1\" \/><\/a><figcaption style=\"margin-bottom: 20px; width: 450px; font-size: 90%; line-height: normal;\"><strong>Fig 1: Marks grades<\/strong> from a university handbook. Students are awarded a final grade (column 2) based on the marks achieved (column 1)<\/figcaption><\/figure>\n<p>Mapping marks to grades, from figure 1, using the worksheet IF function.<\/p>\n<ul>\n<li>In this section, the IF structure is written is ascending order &#8211; to mimic the structure of a VLOOKUP function nearest match <\/li>\n<li>Formula:<code class=\"xl\"> =IF(Marks<50,\"N\",IF(Marks<65,\"P\",IF(Marks<70,\"H3\",IF(Marks<75,\"H2B\",IF(Marks<80,\"H2A\",\"H1\")))))<\/code>, as an array formula, with a test vector named Marks. The array formula for the Grades is a 13 x 1 target, based on the dimensions of the Marks array<\/li>\n<li>Each nested IF structure returns the first TRUE test then exists<\/li>\n<li style=\"list-style:none\">&nbsp;<\/li>\n<li style=\"list-style:none\">\n<figure><a href=\"https:\/\/i0.wp.com\/excelatfinance.com\/online\/wp-content\/uploads\/2015\/03\/xlf-marks-grades-test-array.png\"><img data-attachment-id=\"2208\" data-permalink=\"https:\/\/excelatfinance.com\/online\/2190-2\/xlf-marks-grades-test-array\/\" data-orig-file=\"https:\/\/i0.wp.com\/excelatfinance.com\/online\/wp-content\/uploads\/2015\/03\/xlf-marks-grades-test-array.png?fit=627%2C459&amp;ssl=1\" data-orig-size=\"627,459\" data-comments-opened=\"0\" data-image-meta=\"{&quot;aperture&quot;:&quot;0&quot;,&quot;credit&quot;:&quot;&quot;,&quot;camera&quot;:&quot;&quot;,&quot;caption&quot;:&quot;&quot;,&quot;created_timestamp&quot;:&quot;0&quot;,&quot;copyright&quot;:&quot;&quot;,&quot;focal_length&quot;:&quot;0&quot;,&quot;iso&quot;:&quot;0&quot;,&quot;shutter_speed&quot;:&quot;0&quot;,&quot;title&quot;:&quot;&quot;,&quot;orientation&quot;:&quot;0&quot;}\" data-image-title=\"xlf-marks-grades-test-array\" data-image-description=\"\" data-image-caption=\"\" data-medium-file=\"https:\/\/i0.wp.com\/excelatfinance.com\/online\/wp-content\/uploads\/2015\/03\/xlf-marks-grades-test-array.png?fit=300%2C220&amp;ssl=1\" data-large-file=\"https:\/\/i0.wp.com\/excelatfinance.com\/online\/wp-content\/uploads\/2015\/03\/xlf-marks-grades-test-array.png?fit=625%2C458&amp;ssl=1\" decoding=\"async\" loading=\"lazy\" src=\"https:\/\/i0.wp.com\/excelatfinance.com\/online\/wp-content\/uploads\/2015\/03\/xlf-marks-grades-test-array.png?resize=564%2C413\" alt=\"xlf-marks-grades-test-array\" width=\"564 .3\" height=\"413 .1\" class=\"alignnone size-full wp-image-2208\" srcset=\"https:\/\/i0.wp.com\/excelatfinance.com\/online\/wp-content\/uploads\/2015\/03\/xlf-marks-grades-test-array.png?w=627&amp;ssl=1 627w, https:\/\/i0.wp.com\/excelatfinance.com\/online\/wp-content\/uploads\/2015\/03\/xlf-marks-grades-test-array.png?resize=150%2C110&amp;ssl=1 150w, https:\/\/i0.wp.com\/excelatfinance.com\/online\/wp-content\/uploads\/2015\/03\/xlf-marks-grades-test-array.png?resize=300%2C220&amp;ssl=1 300w, https:\/\/i0.wp.com\/excelatfinance.com\/online\/wp-content\/uploads\/2015\/03\/xlf-marks-grades-test-array.png?resize=624%2C457&amp;ssl=1 624w\" sizes=\"(max-width: 625px) 100vw, 625px\" data-recalc-dims=\"1\" \/><\/a><figcaption style=\"margin-bottom: 20px; font-size: 90%; line-height: normal;\"><strong>Fig 2: Marks grades<\/strong> from a university handbook. Students are awarded a grade based on the mark achieved <\/figcaption><\/figure>\n<\/li>\n<li>Excel permits 64 levels of nesting. The marks grades example contains 5 IF functions - with 4 levels of nesting<\/li>\n<li>In general, if the statements include more than 3 or 4 nesting levels, it is easier to use a Lookup and Reference function, or use a VBA procedure <\/li>\n<\/ul>\n<h3>AND function w\/ the TRUE function and FALSE function<\/h3>\n<ul style=\"list-style:none\">\n<li>TRUE function<\/li>\n<li>SYNTAX: <code class=\"xl\"> TRUE()<\/code><\/li>\n<\/ul>\n<ul style=\"list-style:none\">\n<li>FALSE function<\/li>\n<li>SYNTAX: <code class=\"xl\"> FALSE()<\/code><\/li>\n<\/ul>\n<ul style=\"list-style:none\">\n<li>AND function - <strong>ALL<\/strong> TRUE<\/li>\n<li>SYNTAX: <code class=\"xl\"> AND(logical1, [logical2], ...)<\/code><\/li>\n<\/ul>\n<ul style=\"list-style:none\">\n<li><code class=\"xl\"> =AND(TRUE(),TRUE(),TRUE()<\/code> returns a logical TRUE <\/li>\n<li><code class=\"xl\"> =AND(TRUE(),TRUE(),FALSE())<\/code> returns a logical FALSE<\/li>\n<li><code class=\"xl\"> =AND(FALSE(),FALSE(),FALSE())<\/code> returns a logical FALSE<\/li>\n<\/ul>\n<h3>OR function<\/h3>\n<ul style=\"list-style:none\">\n<li>OR function - <strong>ANY ONE <\/strong> TRUE<\/li>\n<li>SYNTAX: <code class=\"xl\"> OR(logical1, [logical2], ...)<\/code><\/li>\n<\/ul>\n<ol>\n<li style=\"list-style:none\"><code class=\"xl\"> =OR(TRUE(),TRUE(),TRUE())<\/code> returns a logical TRUE <\/li>\n<li style=\"list-style:none\"><code class=\"xl\"> =OR(TRUE(),TRUE(),FALSE())<\/code> returns a logical TRUE<\/li>\n<li style=\"list-style:none\"><code class=\"xl\"> =OR(FALSE(),FALSE(),FALSE())<\/code> returns a logical FALSE<\/li>\n<\/ol>\n<ol>\n<li><code class=\"xl\"> =OR(TRUE(),TRUE())<\/code> returns a logical TRUE<\/li>\n<li><code class=\"xl\"> =OR(TRUE(),FALSE())<\/code> returns a logical TRUE<\/li>\n<li><code class=\"xl\"> =OR(FALSE(),FALSE())<\/code> returns a logical FALSE<\/li>\n<\/ol>\n<p>Logically, an OR function should return TRUE when one OR the other of two functions is TRUE. In Excel, this functionality was introduced with the XOR (Exclusive Or) function.  This is shown by comparing points 1 to 3 (above) using OR, to points 4 to 6 (below) using XOR.<\/p>\n<h3>XOR function<\/h3>\n<ul style=\"list-style:none\">\n<li>XOR function - <strong>only ONE <\/strong> TRUE<\/li>\n<li>SYNTAX: <code class=\"xl\"> OR(logical1, [logical2], ...)<\/code><\/li>\n<\/ul>\n<ol start=\"4\">\n<li><code class=\"xl\"> =XOR(TRUE(),TRUE())<\/code> returns a logical FALSE<\/li>\n<li><code class=\"xl\"> =XOR(TRUE(),FALSE())<\/code> returns a logical TRUE<\/li>\n<li><code class=\"xl\"> =XOR(FALSE(),FALSE())<\/code> returns a logical FALSE<\/li>\n<\/ol>\n<h3>Comparison AND, OR, and XOR <\/h3>\n<figure><iframe loading=\"lazy\" width=\"800\" height=\"346\" frameborder=\"0\" scrolling=\"no\" src=\"https:\/\/onedrive.live.com\/embed?resid=30828B13843F1859%211283&#038;authkey=%21AMQ1p6ELR0S1ryw&#038;em=2&#038;wdAllowInteractivity=False&#038;ActiveCell='and%20or%20xor'!B2&#038;wdDownloadButton=True&#038;wdInConfigurator=True\"><\/iframe><figcaption style=\"top-bottom: 20px; font-size: 90%; line-height: normal;\"><strong>Fig 3: xlf Online:<\/strong> comparison of logical functions AND, OR and XOR<br \/>\n<code class=\"xl\">AND<\/code> returns TRUE if all are TRUE<br \/>\n<code class=\"xl\">OR<\/code> returns TRUE if at least one is TRUE<br \/>\n<code class=\"xl\">XOR<\/code> returns TRUE if either one is TRUE (pair-wise)<br \/>\nNote: the formula in cell L9 is: <code class=\"xl\">=IF(ISFORMULA(H9),ADDRESS(ROW(H9),COLUMN(H9),4)&\": \"&FORMULATEXT(H9),ADDRESS(ROW(H9),COLUMN(H9),4)&\": \"&IF(NOT(ISBLANK(H9)),OneLeft,\"\"))<\/code><\/figcaption><\/figure>\n<h3>NOT function<\/h3>\n<p>The NOT function is useful when it is easier to write a particular logical test, then reverse the answer. An example is an advanced filter criteria for data filtering.<\/p>\n<ul style=\"list-style:none\">\n<li>NOT function<\/li>\n<li>SYNTAX: <code class=\"xl\"> NOT(logical)<\/code><\/li>\n<\/ul>\n<ol>\n<li style=\"list-style:none\"><code class=\"xl\"> =NOT(TRUE())<\/code> returns a logical FALSE<\/li>\n<li style=\"list-style:none\"><code class=\"xl\"> =NOT(\"up\"=\"down\")<\/code> returns a logical TRUE<\/li>\n<\/ol>\n<p><?php include '..\/includes\/next-prev.php'; ?><\/p>\n","protected":false},"excerpt":{"rendered":"<p>Logical All logical functions evaluate the existence of a Boolean value TRUE or FALSE value, and comprise of a logical_test based on a comparison operator. Comparison operators Excel comparison operators Description = equals (eq) Returns TRUE if Value1 is equal to Value2. eg. Value1=Value2 &lt; less than (lt) Returns TRUE if Value1 is less than [&hellip;]<\/p>\n","protected":false},"author":1,"featured_media":0,"parent":0,"menu_order":0,"comment_status":"closed","ping_status":"closed","template":"","meta":{"jetpack_post_was_ever_published":false},"jetpack_shortlink":"https:\/\/wp.me\/P59BC0-zk","jetpack_sharing_enabled":true,"_links":{"self":[{"href":"https:\/\/excelatfinance.com\/online\/wp-json\/wp\/v2\/pages\/2190"}],"collection":[{"href":"https:\/\/excelatfinance.com\/online\/wp-json\/wp\/v2\/pages"}],"about":[{"href":"https:\/\/excelatfinance.com\/online\/wp-json\/wp\/v2\/types\/page"}],"author":[{"embeddable":true,"href":"https:\/\/excelatfinance.com\/online\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/excelatfinance.com\/online\/wp-json\/wp\/v2\/comments?post=2190"}],"version-history":[{"count":1,"href":"https:\/\/excelatfinance.com\/online\/wp-json\/wp\/v2\/pages\/2190\/revisions"}],"predecessor-version":[{"id":4717,"href":"https:\/\/excelatfinance.com\/online\/wp-json\/wp\/v2\/pages\/2190\/revisions\/4717"}],"wp:attachment":[{"href":"https:\/\/excelatfinance.com\/online\/wp-json\/wp\/v2\/media?parent=2190"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}