Table of Contents
To create a Lag Function in Google Sheets, you can use the built-in function “OFFSET” and specify the range of cells you want to shift by a certain number of rows or columns. This will allow you to retrieve the value from the previous or next row or column, essentially creating a lag effect. You can also use the “INDEX” function in combination with “ROW” or “COLUMN” to dynamically reference cells based on their position relative to the current cell. By adjusting the offset value, you can control the lag effect and use this function to analyze time series data or track changes in a dataset.
There does not exist a LAG function in Google Sheets to calculate lagged values, but you can easily calculate lagged values by using the OFFSET function instead.
The following examples show how to use the OFFSET function to calculate lagged values in practice.
Example 1: Calculate Lagged Values in Google Sheets
Suppose we have the following dataset in Google Sheets 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 17 sales. The lagged value of sales on day 2 (e.g. the sales made on day 1) is 13 sales.
Note: To calculate the lagged value for a different number of previous periods, simply change the -1 in the OFFSET formula to a different number.
Example 2: Calculate Lagged Values by Group in Google Sheets
Suppose we have the following dataset in Google Sheets 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), "")
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 17. 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.
Note: You can find the complete documentation for the OFFSET function in Google Sheets .
Additional Resources
The following tutorials explain how to perform other common tasks in Google Sheets: