{"id":3851,"date":"2018-12-06T13:53:01","date_gmt":"2018-12-06T18:53:01","guid":{"rendered":"http:\/\/lisa.rushworth.us\/?p=3851"},"modified":"2018-12-06T13:53:19","modified_gmt":"2018-12-06T18:53:19","slug":"did-you-know-excel-performs-logical-tests","status":"publish","type":"post","link":"https:\/\/www.rushworth.us\/lisa\/?p=3851","title":{"rendered":"Did you know \u2026 Excel performs logical tests?"},"content":{"rendered":"\n<p>I have been writing a lot of \u201cdid you know\u201d articles as part of our Microsoft Teams reintroduction, and I have an Excel file that lists topics for which we want articles. To avoid posting the same article twice (or worse, <em>writing<\/em> the same article twice!), I mark off when the article is written and posted. With a long list, though, it is difficult to identify which articles still need to be posted (yeah, I know I&#8217;m not apt to have posted an article that hasn&#8217;t been written so I could have just used a filter on the &#8216;Posted&#8217; column &#8230; but there are plenty of cases where a simple filter does not suffice). Sometime you can build an advanced filter that isolates the specific data you need, but there&#8217;s an easier way &#8212; the Excel IF function. <br><\/p>\n\n\n\n<p>In my spreadsheet, I \nadded a column, named \u201cStatus\u201d. The \u201cIF\u201d function displays different \ntext when the test evaluates to TRUE and FALSE: <strong>if(B2=\u201dx\u201d,\u201dWritten\u201d,\u201dNot written\u201d) <\/strong>displays \u201cWritten\u201d in all of the rows where column B has an x, and \u201cNot written\u201d in the remaining columns. <br><\/p>\n\n\n\n<p>In\n conjunction with IF, I can use the Boolean AND function to display \n\u201cFinished\u201d in any row where both columns B and C contain an X: &nbsp;&nbsp;&nbsp;&nbsp; =IF(AND(B2=\u201dx\u201d,C2=\u201dx\u201d),\u201dFinished\u201d,\u201dIn Progress\u201d)<\/p>\n\n\n\n<figure class=\"wp-block-image\"><img loading=\"lazy\" decoding=\"async\" width=\"1024\" height=\"526\" src=\"http:\/\/lisa.rushworth.us\/wp-content\/uploads\/2018\/12\/Excel-BooleanFunction01-1024x526.png\" alt=\"\" class=\"wp-image-3852\" srcset=\"https:\/\/www.rushworth.us\/lisa\/wp-content\/uploads\/2018\/12\/Excel-BooleanFunction01-1024x526.png 1024w, https:\/\/www.rushworth.us\/lisa\/wp-content\/uploads\/2018\/12\/Excel-BooleanFunction01-300x154.png 300w, https:\/\/www.rushworth.us\/lisa\/wp-content\/uploads\/2018\/12\/Excel-BooleanFunction01-768x395.png 768w, https:\/\/www.rushworth.us\/lisa\/wp-content\/uploads\/2018\/12\/Excel-BooleanFunction01.png 1078w\" sizes=\"auto, (max-width: 1024px) 100vw, 1024px\" \/><\/figure>\n\n\n\n<p>The <em>content<\/em> of the Status cells can be used as a filter. On the \u201cData\u201d tab, select \u201cFilter\u201d.<\/p>\n\n\n\n<figure class=\"wp-block-image\"><img loading=\"lazy\" decoding=\"async\" width=\"1024\" height=\"449\" src=\"http:\/\/lisa.rushworth.us\/wp-content\/uploads\/2018\/12\/Excel-BooleanFunction02-1024x449.png\" alt=\"\" class=\"wp-image-3853\" srcset=\"https:\/\/www.rushworth.us\/lisa\/wp-content\/uploads\/2018\/12\/Excel-BooleanFunction02-1024x449.png 1024w, https:\/\/www.rushworth.us\/lisa\/wp-content\/uploads\/2018\/12\/Excel-BooleanFunction02-300x132.png 300w, https:\/\/www.rushworth.us\/lisa\/wp-content\/uploads\/2018\/12\/Excel-BooleanFunction02-768x337.png 768w, https:\/\/www.rushworth.us\/lisa\/wp-content\/uploads\/2018\/12\/Excel-BooleanFunction02.png 1147w\" sizes=\"auto, (max-width: 1024px) 100vw, 1024px\" \/><\/figure>\n\n\n\n<p>Click the drop-down menu on the \u201cStatus\u201d column, deselect whatever values you do not wish to display, then click \u201cOK\u201d.<\/p>\n\n\n\n<figure class=\"wp-block-image\"><img loading=\"lazy\" decoding=\"async\" width=\"1024\" height=\"731\" src=\"http:\/\/lisa.rushworth.us\/wp-content\/uploads\/2018\/12\/Excel-BooleanFunction03-1024x731.png\" alt=\"\" class=\"wp-image-3854\" srcset=\"https:\/\/www.rushworth.us\/lisa\/wp-content\/uploads\/2018\/12\/Excel-BooleanFunction03-1024x731.png 1024w, https:\/\/www.rushworth.us\/lisa\/wp-content\/uploads\/2018\/12\/Excel-BooleanFunction03-300x214.png 300w, https:\/\/www.rushworth.us\/lisa\/wp-content\/uploads\/2018\/12\/Excel-BooleanFunction03-768x548.png 768w, https:\/\/www.rushworth.us\/lisa\/wp-content\/uploads\/2018\/12\/Excel-BooleanFunction03.png 1042w\" sizes=\"auto, (max-width: 1024px) 100vw, 1024px\" \/><\/figure>\n\n\n\n<p>Voila! Now I see <em>only<\/em> the articles where the status is not &#8220;Finished&#8221;. <br><\/p>\n\n\n\n<figure class=\"wp-block-image\"><img loading=\"lazy\" decoding=\"async\" width=\"1024\" height=\"538\" src=\"http:\/\/lisa.rushworth.us\/wp-content\/uploads\/2018\/12\/Excel-BooleanFunction04-1024x538.png\" alt=\"\" class=\"wp-image-3855\" srcset=\"https:\/\/www.rushworth.us\/lisa\/wp-content\/uploads\/2018\/12\/Excel-BooleanFunction04-1024x538.png 1024w, https:\/\/www.rushworth.us\/lisa\/wp-content\/uploads\/2018\/12\/Excel-BooleanFunction04-300x158.png 300w, https:\/\/www.rushworth.us\/lisa\/wp-content\/uploads\/2018\/12\/Excel-BooleanFunction04-768x404.png 768w, https:\/\/www.rushworth.us\/lisa\/wp-content\/uploads\/2018\/12\/Excel-BooleanFunction04.png 1056w\" sizes=\"auto, (max-width: 1024px) 100vw, 1024px\" \/><\/figure>\n","protected":false},"excerpt":{"rendered":"<p>I have been writing a lot of \u201cdid you know\u201d articles as part of our Microsoft Teams reintroduction, and I have an Excel file that lists topics for which we want articles. To avoid posting the same article twice (or worse, writing the same article twice!), I mark off when the article is written and &hellip;<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[677],"tags":[708,265],"class_list":["post-3851","post","type-post","status-publish","format-standard","hentry","category-office-365","tag-excel","tag-office-365"],"_links":{"self":[{"href":"https:\/\/www.rushworth.us\/lisa\/index.php?rest_route=\/wp\/v2\/posts\/3851","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.rushworth.us\/lisa\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.rushworth.us\/lisa\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.rushworth.us\/lisa\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.rushworth.us\/lisa\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=3851"}],"version-history":[{"count":1,"href":"https:\/\/www.rushworth.us\/lisa\/index.php?rest_route=\/wp\/v2\/posts\/3851\/revisions"}],"predecessor-version":[{"id":3856,"href":"https:\/\/www.rushworth.us\/lisa\/index.php?rest_route=\/wp\/v2\/posts\/3851\/revisions\/3856"}],"wp:attachment":[{"href":"https:\/\/www.rushworth.us\/lisa\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=3851"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.rushworth.us\/lisa\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=3851"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.rushworth.us\/lisa\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=3851"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}