{"id":5181,"date":"2020-06-22T15:23:44","date_gmt":"2020-06-22T13:23:44","guid":{"rendered":"https:\/\/www.myexcelonline.com\/?p=5181"},"modified":"2024-03-22T19:04:27","modified_gmt":"2024-03-22T18:04:27","slug":"between-formula-in-excel","status":"publish","type":"post","link":"https:\/\/www.myexcelonline.com\/meo-staging\/blog\/between-formula-in-excel\/","title":{"rendered":"BETWEEN Formula in Excel"},"content":{"rendered":"<p><a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2020\/06\/BETWEEN-FORMULA-IN-EXCEL.png\" target=\"_blank\"><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-large wp-image-33502\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2020\/06\/BETWEEN-FORMULA-IN-EXCEL-1024x576.png\" alt=\"BETWEEN FORMULA IN EXCEL\" width=\"1024\" height=\"576\" srcset=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2020\/06\/BETWEEN-FORMULA-IN-EXCEL-1024x576.png 1024w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2020\/06\/BETWEEN-FORMULA-IN-EXCEL-300x169.png 300w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2020\/06\/BETWEEN-FORMULA-IN-EXCEL-768x432.png 768w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2020\/06\/BETWEEN-FORMULA-IN-EXCEL.png 1280w\" sizes=\"(max-width: 1024px) 100vw, 1024px\" title=\"\"><\/a><\/p>\n<p>There is no explicit <strong>Between formula<\/strong> in <a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/blog\/excel-interview-questions\/\" target=\"_blank\" rel=\"noopener\">Excel<\/a>, however, we can come up with creative ways to create this functionality. Our goal is to evaluate if a given value is between a range, for example, is 6 between 1 and 10?<\/p>\n<p>You can download this Excel Workbook and follow along:<br \/>\n<strong><a class=\"IRPP_minimalist icon-download\" href=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/11\/Between.xlsx\" target=\"_blank\"><span class=\"ctaText px-3 text-capitalize\"><b>download excel workbook<\/b><\/span><span class=\"postTitle px-3\">Between.xlsx<\/span><\/a><\/strong><\/p>\n<div id=\"ez-toc-container\" class=\"ez-toc-v2_0_57_1 counter-flat ez-toc-counter ez-toc-transparent ez-toc-container-direction\">\n<div class=\"ez-toc-title-container\">\n<p class=\"ez-toc-title \" >Table of Contents<\/p>\n<span class=\"ez-toc-title-toggle\"><\/span><\/div>\n<nav><ul class='ez-toc-list ez-toc-list-level-1 ' ><li><a href=\"#\" class=\"scroll-to-download\">Free Practice Workbook<\/a><\/li><li class='ez-toc-page-1'><a class=\"ez-toc-link ez-toc-heading-1\" href=\"https:\/\/www.myexcelonline.com\/meo-staging\/blog\/between-formula-in-excel\/#Between_formula_in_Excel_for_Numbers\" title=\"Between formula in Excel for Numbers\">Between formula in Excel for Numbers<\/a><\/li><li class='ez-toc-page-1'><a class=\"ez-toc-link ez-toc-heading-2\" href=\"https:\/\/www.myexcelonline.com\/meo-staging\/blog\/between-formula-in-excel\/#Between_formula_in_Excel_for_Dates\" title=\"Between formula in Excel for Dates\">Between formula in Excel for Dates<\/a><\/li><li class='ez-toc-page-1'><a class=\"ez-toc-link ez-toc-heading-3\" href=\"https:\/\/www.myexcelonline.com\/meo-staging\/blog\/between-formula-in-excel\/#Conclusion\" title=\"Conclusion\">Conclusion<\/a><\/li><\/ul><\/nav><\/div>\n<h2><span class=\"ez-toc-section\" id=\"Between_formula_in_Excel_for_Numbers\"><\/span><a id=\"between-formula-in-excel-for-numbers\"><\/a>Between formula in Excel for Numbers<span class=\"ez-toc-section-end\"><\/span><\/h2>\n<p><span style=\"color: #ff0000;\"><strong>OPTION 1:<\/strong><\/span>Using a combination of <strong>MIN, MAX &amp; AND <\/strong>function<\/p>\n<p>In the example below, you have the start of the range in Column A, end of the range in Column B and the value to be evaluated in Column C.<\/p>\n<p>You need to check whether the number entered in Column C is in between the numbers in Column A &amp; Column B using a creatively formulated BETWEEN formula in Excel.<\/p>\n<p><a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/12\/Screenshot-2020-06-23-at-4.08.49-AM.png\" target=\"_blank\"><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-18175\" title=\"BETWEEN Formula in Excel\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/12\/Screenshot-2020-06-23-at-4.08.49-AM.png\" alt=\"BETWEEN Formula in Excel\" width=\"952\" height=\"155\" srcset=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/12\/Screenshot-2020-06-23-at-4.08.49-AM.png 952w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/12\/Screenshot-2020-06-23-at-4.08.49-AM-300x49.png 300w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/12\/Screenshot-2020-06-23-at-4.08.49-AM-768x125.png 768w\" sizes=\"(max-width: 952px) 100vw, 952px\" \/><\/a><\/p>\n<p>The function that can be used to determine if the value in cell D7 is in-between values in cell A7 &amp; B7 is<\/p>\n<h3 style=\"text-align: center;\"><span style=\"color: #0000ff;\">=IF(AND(C7&gt;=MIN(A7,B7),C7&lt;=MAX(A7,B7)), &#8220;Yes&#8221;, &#8220;No&#8221;)<\/span><\/h3>\n<p>Let&#8217;s break this formula into parts to understand it better:<\/p>\n<ul>\n<li><strong>C7 &gt;= MIN(A7, B7)<\/strong> &#8211; \u00a0This expression checks whether the value in cell C7 is greater than (or equal to) the smaller of the two numbers in cell A7 and B7.<\/li>\n<li><strong>C7 &lt;= MAX(A7, B7)<\/strong> &#8211; \u00a0This expression checks whether the value in cell C7 is smaller than (or equal to) the larger of the two numbers in cell A7 and B7.<\/li>\n<li><strong>AND( C7 &gt;= MIN(A7, B7), C7 &lt;= MAX(A7, B7))<\/strong> &#8211; AND function simply checks whether the above two conditions are met or not i.e. whether the value in cell C7 is greater than (or equal to) the smaller number and less than (or equal to) the larger number.<\/li>\n<\/ul>\n<p><a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/12\/Screenshot-2020-06-23-at-4.08.34-AM.png\" target=\"_blank\"><img loading=\"lazy\" decoding=\"async\" class=\"alignnone wp-image-18174 size-full\" title=\"BETWEEN Formula in Excel\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/12\/Screenshot-2020-06-23-at-4.08.34-AM.png\" alt=\"BETWEEN Formula in Excel\" width=\"951\" height=\"154\" srcset=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/12\/Screenshot-2020-06-23-at-4.08.34-AM.png 951w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/12\/Screenshot-2020-06-23-at-4.08.34-AM-300x49.png 300w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/12\/Screenshot-2020-06-23-at-4.08.34-AM-768x124.png 768w\" sizes=\"(max-width: 951px) 100vw, 951px\" \/><\/a><\/p>\n<p><span style=\"color: #ff0000;\"><strong>OPTION 2:<\/strong><\/span>Using a <strong>MEDIAN\u00a0<\/strong>function<\/p>\n<p>You can use a simpler version of this complicated function by creatively using the <strong><a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/blog\/median-formula-in-excel\/\" target=\"_blank\" rel=\"noopener\">Median formula<\/a><\/strong>:<\/p>\n<p><a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/11\/Between-01.png\" target=\"_blank\"><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-5183\" title=\"BETWEEN Formula in Excel\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/11\/Between-01.png\" alt=\"BETWEEN Formula in Excel\" width=\"787\" height=\"142\" srcset=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/11\/Between-01.png 787w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/11\/Between-01-300x54.png 300w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/11\/Between-01-768x139.png 768w\" sizes=\"(max-width: 787px) 100vw, 787px\" \/><\/a><\/p>\n<h3 style=\"text-align: center;\"><span style=\"color: #0000ff;\">=IF(C7=MEDIAN(A7:C7), &#8220;Yes&#8221;, &#8220;No&#8221;)<\/span><\/h3>\n<p>In our first example above, the range is 20-60, upon checking the value 50, it is in between this range.<\/p>\n<p>The median formula will return the value in the middle of these 3 values when arranged in increasing order: 20, 50, 60. The median value is 50.<\/p>\n<p>Since it matches the value we are evaluating, then the answer we get is a <strong>Yes,\u00a0<\/strong>this value (50) is in between the range.<\/p>\n<p>Now that you have learned how to use Excel if between two numbers, let&#8217;s move forward to dates and text.<\/p>\n<h2><span class=\"ez-toc-section\" id=\"Between_formula_in_Excel_for_Dates\"><\/span><a id=\"between-formula-in-excel-for-dates\"><\/a>Between formula in Excel for Dates<span class=\"ez-toc-section-end\"><\/span><\/h2>\n<p>Irrespective of how you <a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/blog\/ctrl-1-format-cells\/\" target=\"_blank\" rel=\"noopener\">format a cell<\/a> to display a date, Excel always stores it as a number. The number stored for each date actually represents the number of days since 0-Jan-1990.<\/p>\n<p>1st Jan 1990 is stored as 1 (representing 1 day since 0-Jan-1990) and 23rd June 2020 is stored as 44,005 (representing 44005 days since 0-Jan-1990).<\/p>\n<p>So, to check whether a date is in between two mentioned dates<strong>,\u00a0<\/strong>we have the same application as the <strong>median formula.<\/strong><\/p>\n<p>Below is an example of how to use the median function to check dates.<\/p>\n<h2><a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/11\/Between-02.png\" target=\"_blank\"><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-5184\" title=\"BETWEEN Formula in Excel\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/11\/Between-02.png\" alt=\"BETWEEN Formula in Excel\" width=\"790\" height=\"113\" srcset=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/11\/Between-02.png 790w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/11\/Between-02-300x43.png 300w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/11\/Between-02-768x110.png 768w\" sizes=\"(max-width: 790px) 100vw, 790px\" \/><\/a><\/h2>\n<h3 style=\"text-align: center;\"><span style=\"color: #0000ff;\">=IF(C10=MEDIAN(A10:C10), &#8220;Yes&#8221;, &#8220;No&#8221;)<\/span><\/h3>\n<p>In our first example above, the range is May 1 &#8211; July 1, upon checking the date June 1, it is in between this range.<\/p>\n<p>The median formula will return the value in the middle of these 3 dates when arranged in increasing order: May 1, June 1, July 1. The median value is June 1.<\/p>\n<p>Since it matches the value we are evaluating, then the answer we get is a <strong>Yes,\u00a0<\/strong>this value (June 1) is in between the range.<\/p>\n<h3><a id=\"between-formula-in-excel-for-text\"><\/a>Between formula in Excel for Text<\/h3>\n<p>For text, we are checking if the value is alphabetically in the middle. We will be using the <strong>and formula:<\/strong><\/p>\n<p><a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/11\/Between-03.png\" target=\"_blank\"><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-5185\" title=\"BETWEEN Formula in Excel\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/11\/Between-03.png\" alt=\"BETWEEN Formula in Excel\" width=\"788\" height=\"104\" srcset=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/11\/Between-03.png 788w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/11\/Between-03-300x40.png 300w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/11\/Between-03-768x101.png 768w\" sizes=\"(max-width: 788px) 100vw, 788px\" \/><\/a><\/p>\n<h3 style=\"text-align: center;\"><span style=\"color: #0000ff;\">=IF(AND(C12&gt;=A12, C12&lt;=B12, &#8220;Yes&#8221;, &#8220;No&#8221;)<\/span><\/h3>\n<p>Interestingly enough, you can compare texts using the\u00a0<strong>&gt;= and &lt;= operators. \u00a0<\/strong>Excel is able to compare them which goes alphabetically first or last.<\/p>\n<p>In our first example above, the range is Cat\u00a0&#8211; Dog, upon checking the text Cow, it is in between this range. As when arranged alphabetically, it would be: Cat, Cow, Dog.<\/p>\n<p>The And formula checks if <strong>Cow &gt;= Cat, <\/strong>and\u00a0<strong>Cow &lt;= Dog<\/strong>. You will see that both of these are true, as\u00a0<strong>Cow\u00a0<\/strong>is alphabetically later than\u00a0<strong>Cat<\/strong>, while\u00a0<strong>Cow\u00a0<\/strong>is alphabetically ahead of\u00a0<strong>Dog<\/strong>. Which is why we get a\u00a0<strong>Yes\u00a0<\/strong>result.<\/p>\n<h2><span class=\"ez-toc-section\" id=\"Conclusion\"><\/span>Conclusion<span class=\"ez-toc-section-end\"><\/span><\/h2>\n<p>In this tutorial, you have learned how to use Between <a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/blog\/top-excel-formulas-function-examples-to-get-better-at-microsoft-excel\/\" target=\"_blank\" rel=\"noopener\">formula in Excel<\/a> even when there is no explicit formula available to do this. You can use a combination of various other available function to create <a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/blog\/free-microsoft-excel-online-course\/\" target=\"_blank\" rel=\"noopener\">Excel<\/a> if between range functionality.<\/p>\n<p>You can use <strong>MIN, MAX, MEDIAN &amp; AND<\/strong> functions to create a creative Between function in Excel for<strong> numbers, dates and text.<\/strong><\/p>\n<p><strong>Further Learning:<\/strong><\/p>\n<ul>\n<li><a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/blog\/filter-function-in-excel\/\" target=\"_blank\" rel=\"noopener\">FILTER Function in Microsoft Excel &#8211; The Easy Way<\/a><\/li>\n<li><a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/blog\/countif-with-or-logic-in-excel\/\" target=\"_blank\" rel=\"noopener\">3 Simple Methods to Explore the Power of COUNTIF with OR Logic in Excel<\/a><\/li>\n<li><a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/blog\/if-function-in-excel\/\" target=\"_blank\" rel=\"noopener\">How to Use the IF Function in Excel<\/a><\/li>\n<\/ul>\n<p><strong><span style=\"text-decoration: underline; color: #0000ff;\"><b>HELPFUL RESOURCE:<\/b><\/span><\/strong><\/p>\n<p><a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/podcast\/000-my-excel-story-myexcelonline-podcast\/\" target=\"_blank\" rel=\"attachment noopener wp-att-1685 noreferrer\"><img loading=\"lazy\" decoding=\"async\" class=\"alignnone wp-image-1685 size-full\" title=\"Add Columns to Pivot Table Slicer Buttons\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2015\/10\/Me_Podcast_Banner_EXCEL.jpg\" alt=\"Add Columns to Pivot Table Slicer Buttons\" width=\"728\" height=\"90\" srcset=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2015\/10\/Me_Podcast_Banner_EXCEL.jpg 728w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2015\/10\/Me_Podcast_Banner_EXCEL-300x37.jpg 300w\" sizes=\"(max-width: 728px) 100vw, 728px\" \/><\/a><\/p>\n<p><strong>Make sure to download our FREE PDF on the<a href=\"https:\/\/app.monstercampaigns.com\/c\/czseo0viyrmspcx5ndve\/\" target=\"_blank\" rel=\"noopener noreferrer\"> 333 Excel keyboard Shortcuts here<\/a>:<\/strong><\/p>\n<p><a href=\"https:\/\/app.monstercampaigns.com\/c\/czseo0viyrmspcx5ndve\/\" target=\"_blank\" rel=\"nofollow noopener\"><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter wp-image-14329 size-medium\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2020\/03\/333coffeMockups-300x200.jpg\" alt=\"\" width=\"300\" height=\"200\" srcset=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2020\/03\/333coffeMockups-300x200.jpg 300w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2020\/03\/333coffeMockups-1024x682.jpg 1024w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2020\/03\/333coffeMockups-768x512.jpg 768w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2020\/03\/333coffeMockups.jpg 1340w\" sizes=\"(max-width: 300px) 100vw, 300px\" title=\"\"><\/a><\/p>\n<style>.special-box a { color: #fff; } <\/style><div class=\"drop-shadow lifted special-box\" style=\"text-align: center; color: #fff; padding: 15px; background: var(--V4-primary-orange); border-radius: 6px;\">&#x1f449; <strong><a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/100-43.html\" target=\"_blank\" rel=\"noopener noreferrer\">Click Here To Join Our Free Formulas &amp; Functions Webinar Training on VLOOKUP, IF, SUMIF &amp; INDEX\/MATCH &amp; Advance Your Excel Level!<\/a><\/strong><\/div>\n","protected":false},"excerpt":{"rendered":"<p>There is no explicit Between formula in Excel, however, we can come up with creative ways to create this functionality. Our goal is to evaluate if a given value is between a range, for example, is 6 between 1 and 10? You can download this Excel Workbook and follow along: download excel workbookBetween.xlsx Between formula [&hellip;]<\/p>\n","protected":false},"author":2,"featured_media":33502,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"cos_headline_score":0,"cos_seo_score":0,"cos_headline_text":"","cos_headline_has_been_analyzed":false,"cos_last_analyzed_headline":[],"om_disable_all_campaigns":false},"categories":[3,297,12],"tags":[204,142],"acf":[],"_links":{"self":[{"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/posts\/5181"}],"collection":[{"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/comments?post=5181"}],"version-history":[{"count":0,"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/posts\/5181\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/media\/33502"}],"wp:attachment":[{"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/media?parent=5181"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/categories?post=5181"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/tags?post=5181"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}