{"id":2628,"date":"2016-03-07T19:28:00","date_gmt":"2016-03-07T18:28:00","guid":{"rendered":"https:\/\/www.myexcelonline.com\/?p=2628"},"modified":"2021-09-29T08:44:24","modified_gmt":"2021-09-29T06:44:24","slug":"excel-offset-function-introduced","status":"publish","type":"post","link":"https:\/\/www.myexcelonline.com\/meo-staging\/blog\/excel-offset-function-introduced\/","title":{"rendered":"OFFSET Function Introduced"},"content":{"rendered":"

What does it do?<\/strong><\/em><\/p>\n

It returns a reference to a range, from a starting point to a specified number of rows, columns, height and width of cells<\/p>\n

Formula breakdown:<\/strong><\/em><\/p>\n

=OFFSET(reference<\/span>, rows<\/span>, columns<\/span>, [height]<\/span>, [width]<\/span>)<\/p>\n

What it means:<\/strong><\/em><\/p>\n

=OFFSET(start in this cell<\/span>, go up\/down a number of rows<\/span>, go left\/right a number of columns<\/span>, height of range<\/span>, width of range<\/span>)<\/p>\n

\n

The OFFSET function in Excel is one of the Lookup functions and is great if you want to reference a range of cells and use that reference to do a calculation.<\/p>\n

It is often used when you need to reference a range that is moving or resizing E.g. Daily sales going down one row per day.<\/p>\n

Instead of updating your formula each day to include the new row of data, you would simply use an OFFSET function!<\/p>\n

Note: The OFFSET function is a volatile function, which means that Excel recalculates the function whenever there is a change in your workbook, which may cause it to slow down, but don’t let this stop you from using its super POWER!<\/p>\n

There are many ways were you would use an OFFSET function and here i explain a few scenarios:<\/p>\n

    \n
  • * Get\u00a0the sum or average of the last 7 transactions;<\/li>\n
  • * Return the last value in a column;<\/li>\n
  • * Lookup two values in a table;<\/li>\n
  • * Create dynamic ranges that will be used in a chart;<\/li>\n
  • * Calculate moving averages.<\/li>\n<\/ul>\n

     <\/p>\n

    \n
    \n

    Table of Contents<\/p>\n<\/span><\/div>\n