{"id":569,"date":"2019-04-17T13:22:09","date_gmt":"2019-04-17T10:22:09","guid":{"rendered":"https:\/\/www.test4u.eu\/excelpedia\/?p=569"},"modified":"2019-05-06T12:55:08","modified_gmt":"2019-05-06T09:55:08","slug":"how-to-use-the-offset-function","status":"publish","type":"post","link":"https:\/\/www.test4u.eu\/excelpedia\/how-to-use-the-offset-function\/","title":{"rendered":"How to use the OFFSET function"},"content":{"rendered":"\n<p>One very useful Excel function is <strong><a href=\"https:\/\/www.test4u.eu\/excelpedia\/the-offset-function\/\" target=\"_blank\" rel=\"noreferrer noopener\" aria-label=\"OFFSET (opens in a new tab)\">OFFSET<\/a><\/strong>. It returns a range of cells which corresponds to a specified number of rows and columns starting at a given distance from a reference cell.<\/p>\n\n\n\n<figure class=\"wp-block-embed-youtube wp-block-embed is-type-video is-provider-youtube wp-embed-aspect-16-9 wp-has-aspect-ratio\"><div class=\"wp-block-embed__wrapper\">\n<span class=\"embed-youtube\" style=\"text-align:center; display: block;\"><iframe class=\"youtube-player\" width=\"640\" height=\"360\" src=\"https:\/\/www.youtube.com\/embed\/SkHHdPAzPmg?version=3&#038;rel=0&#038;showsearch=0&#038;showinfo=1&#038;iv_load_policy=1&#038;fs=1&#038;hl=en-US&#038;autohide=2&#038;wmode=transparent\" allowfullscreen=\"true\" style=\"border:0;\" sandbox=\"allow-scripts allow-same-origin allow-popups allow-presentation allow-popups-to-escape-sandbox\"><\/iframe><\/span>\n<\/div><\/figure>\n\n\n\n<div style=\"height:50px\" aria-hidden=\"true\" class=\"wp-block-spacer\"><\/div>\n\n\n\n<p class=\"has-large-font-size\"><strong>Practice File:<\/strong><\/p>\n\n\n\n<div class=\"wp-block-file\"><a href=\"https:\/\/www.test4u.eu\/excelpedia\/wp-content\/uploads\/2019\/04\/offset.xlsx\">offset<\/a><a href=\"https:\/\/www.test4u.eu\/excelpedia\/wp-content\/uploads\/2019\/04\/offset.xlsx\" class=\"wp-block-file__button\" download>Download<\/a><\/div>\n\n\n\n<div style=\"height:100px\" aria-hidden=\"true\" class=\"wp-block-spacer\"><\/div>\n\n\n\n<p class=\"has-medium-font-size\"><strong>Transcript<\/strong><\/p>\n\n\n\n<p>Sounds complicated,\nbut only until we see it in action.<\/p>\n\n\n\n<p>Suppose we want to\ncalculate each day the sum of the 10 largest sales. (the ones which produced\nthe greatest income).<\/p>\n\n\n\n<p>Sounds easy we just\nsum the 10 last values of the total column of our table since it is sorted in\nascending order by that same column.<\/p>\n\n\n\n<p>Well that is correct,\nbut it also means that each day when new sales will be added we would have to\ndo the same thing again.<\/p>\n\n\n\n<p>Offset can give us the\nsolution since it can give us the last 10 rows of the total column, if we just provide\nit with the total number of rows of data.<\/p>\n\n\n\n<p>Since the data change\nevery day to know what the last row is requires the use of one more function.\nThe countA function counts the numbers on a specified range. So if we use it\nfor column H it will return us the count of numbers in that column, thus the\ntotal number of rows of data.<\/p>\n\n\n\n<p>We keep this number on\nthis cell and we will use a reference to it on our offset formula for\nsimplicity.<\/p>\n\n\n\n<p>Now it is time for the\nmagic.<\/p>\n\n\n\n<p>In cell J2 we will use the offset formula to calculate the sum of the 10 largest sales.<\/p>\n\n\n\n<p>First we type the sum\nformula and nested inside it the offset formula.<\/p>\n\n\n\n<p>We type the name of\nthe function and then we have to type a reference cell. This could be any cell\nin our sheet but usually we select the first cell of our table, which is a1.<\/p>\n\n\n\n<p>Then we type the offset of the first row of our required data range. This is calculated by the value we found before, in cell K11, minus 10 since we want the last 10 rows of data. Then we type the offset column. Since we are currently on column 1 (the A1 cell) and the data range we need is on column 8 (the total column) the offset is 7 (1+7=column 8).<\/p>\n\n\n\n<p>So we have successfully directed our offset function to the beginning of our data range which is cell H34. Now we only have to set the height to 10 (the 10 largest sales) and the width to 1 since we only need the total column.<\/p>\n\n\n\n<p>We press enter and we\nhave the desired result.<\/p>\n\n\n\n<p>This cell now\ncalculates dynamically the 10 largest sales of our company. <\/p>\n\n\n\n<p>Excel provides us with a lot more ways to do exactly the same thing, either with tables or with the index function, but offset has its uses. Even if we decide to use another function for the given example, no one can deny the usefulness of offset which in combination with other functions can provide elegant solutions to complex problems. We will see on later lessons a very efficient way to <strong><a href=\"https:\/\/www.test4u.eu\/excelpedia\/advanced-lookup-functions-combinations-part-2\/\" target=\"_blank\" rel=\"noreferrer noopener\" aria-label=\"lookup values with the help of offset (opens in a new tab)\">lookup values with the help of offset<\/a><\/strong>.<\/p>\n\n\n\n<div style=\"height:50px\" aria-hidden=\"true\" class=\"wp-block-spacer\"><\/div>\n","protected":false},"excerpt":{"rendered":"<p>One very useful Excel function is OFFSET. It returns a range of cells which corresponds to a specified number of rows and columns starting at a given distance from a reference cell.<\/p>\n","protected":false},"author":2,"featured_media":571,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_jetpack_memberships_contains_paid_content":false,"footnotes":"","jetpack_publicize_message":"","jetpack_publicize_feature_enabled":true,"jetpack_social_post_already_shared":true,"jetpack_social_options":{"image_generator_settings":{"template":"highway","default_image_id":0,"font":"","enabled":false},"version":2}},"categories":[3],"tags":[49,52,32],"class_list":["post-569","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-tutorials","tag-lookup-functions","tag-offset","tag-reference"],"jetpack_publicize_connections":[],"jetpack_featured_media_url":"https:\/\/www.test4u.eu\/excelpedia\/wp-content\/uploads\/2019\/04\/offset.jpg","jetpack_sharing_enabled":true,"jetpack_shortlink":"https:\/\/wp.me\/paRnIz-9b","_links":{"self":[{"href":"https:\/\/www.test4u.eu\/excelpedia\/wp-json\/wp\/v2\/posts\/569","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.test4u.eu\/excelpedia\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.test4u.eu\/excelpedia\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.test4u.eu\/excelpedia\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/www.test4u.eu\/excelpedia\/wp-json\/wp\/v2\/comments?post=569"}],"version-history":[{"count":0,"href":"https:\/\/www.test4u.eu\/excelpedia\/wp-json\/wp\/v2\/posts\/569\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.test4u.eu\/excelpedia\/wp-json\/wp\/v2\/media\/571"}],"wp:attachment":[{"href":"https:\/\/www.test4u.eu\/excelpedia\/wp-json\/wp\/v2\/media?parent=569"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.test4u.eu\/excelpedia\/wp-json\/wp\/v2\/categories?post=569"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.test4u.eu\/excelpedia\/wp-json\/wp\/v2\/tags?post=569"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}