Table of Contents
Creating a lag function in Excel is a great way to quickly compare values in a data set. The lag function can be used to compare values in two different columns or rows, or to compare the current value with the previous value. It can also be used to compare values to a predetermined value. To create a lag function in Excel, you need to use the OFFSET function, which allows you to create a reference to a cell or range of cells that is relative to the current cell. You can then use this reference in the lag function to calculate the difference between two values. With examples, you can understand how to use the OFFSET function and the lag function to quickly compare values in your data set.
Often you may want to calculate lagged values in Excel. Fortunately this is easy to do use the OFFSET() function.
The following examples show how to use the OFFSET() function in practice.
Example 1: Calculate Lagged Values in Excel
Suppose we have the following dataset in Excel that shows the total sales made by some store in 10 consecutive days:
We can use the following formula to calculate the lagged sales values in a new column:
=OFFSET(B3, -1, 0)
We can type this formula into cell C3 and drag it down to every remaining cell in column C:
The “Lag Sales” column shows the sales for a lag of n=1.
For example, on day 2 the store made 19 sales. The lagged value of sales on day 2 (e.g. the sales made on day 1) is 13 sales.
Example 2: Calculate Lagged Values by Group in Excel
Suppose we have the following dataset in Excel that shows the total sales made by two different stores during 5 days each:
We can use the following formula to calculate the lagged sales values by store in a new column:
=IF(A3=A2, OFFSET(B3, -1, 0), "")
We can type this formula into cell C3 and drag it down to every remaining cell in column C:
This function first checks if the store value in the current row is equal to the store value in the previous row.
If it is, then it returns the lagged sales value. If it’s not, then it returns a blank.
For example, in row 3 the sales value was 19. Since the store value in row 2 is equal to row 3, the lagged value of sales is calculated as 13.
However, in row 7 the value for store does not match the value for store in row 6, so a blank value is returned instead of a lagged sales value.