There 2 fine alternatives to OFFSET formula.
OFFSET formulas are tricky to debug: Because the references are dynamic, debugging a workbook with lots of OFFSETs can get tricky quickly. But when you use lots of OFFSET formulas in large workbooks, you will end up cursing Excel as it takes too much time to recalculate. This is not an issue if you use OFFSET formula in a small workbook. OFFSET formula is volatile: In plain English it means, whenever there is any change in your workbook, OFFSET formula is recalculated, thus keeping Excel busy a tiny bit longer. While offset formula can return with a dynamic range when you beckon, it does have few limitations: To know how this formula works, watch below demo. The OFFSET would then starting point from C3 to latest week’s starting point. It counts how many values are there in column C and then subtracts 7 from it. This is where COUNTA(C3:C300) – 7 comes in to picture. To calculate latest week’s average, we need to go all the to the last data point and then get 7 rows from it and average those values. =AVERAGE(OFFSET(C3,COUNTA(C3:C300)-7,0,7,1)) Lets break-apart this formula and understand So how do we calculate average of latest week?Īssuming the values are in range C3:C18, we can write =AVERAGE(C12:C18)īUT, WE NEED TO CHANGE THIS FORMULA EVERYDAY!!!īy using the OFFSET awesome sauce, we can write the AVERAGE formula once and forget about it. We have been tracking the % of rejects by day in a spreadsheet that looks like this: One of the KPIs we monitor is % of rejected puppets. Lets say we monitor quality of a plant producing purple puppets. Practical use for OFFSET – Average of latest week After playing with it for a few minutes, you will understand the formula better. When you input all the 5 parameters, the workbook highlights the range that your OFFSET will give. Since OFFSET formula is somewhat tricky to get, I created an interactive workbook so that you can understand how it works. Understand OFFSET formula – Interactive Workbook Rather, we just know it is starting from a certain cell etc. We don’t know the exact address: Sometimes, we don’t know what our ranges actual address is. This is required because our data is changing (every month new row is added, every time we launch a product new column is added etc.) But sometimes, we want our ranges to be dynamic. Dynamic ranges: Reference like A1:C4 always refers to the range A1:C4. Why not write a reference like A1:C4 directly? See below examples to understand the formula better. having the size of D3 rows & D4 columns. That means, you can write =OFFSET(A1,D1,D2,D3,D4) which will refer to a range 4,3 would give you a range with 4 cells tall & 3 cells wide.Īnd yes, All the arguments to OFFSET can be references to other cells. Height & width: This is the size of range you want to return. Both of these can be positive, negative or zero. Rows & columns to move: How many rows & columns you want to move the starting point. Starting point: This is a cell or range from which you want to offset. 
=OFFSET(starting point, rows to move, columns to move, height, width) OFFSET formula gives us reference to a range, from a given starting point with given height and width in cells.
0 kommentar(er)
