20.9 C
London
Tuesday, July 22, 2025
HomeSoftware TutorialsExcelHow to Create a Lag Function in Excel (With Examples)

How to Create a Lag Function in Excel (With Examples)

Related stories

Learn About Opening an Automobile Repair Shop in India

Starting a car repair shop is quite a good...

Unlocking the Power: Embracing the Benefits of Tax-Free Investing

  Unlocking the Power: Embracing the Benefits of Tax-Free Investing For...

Income Splitting in Canada for 2023

  Income Splitting in Canada for 2023 The federal government’s expanded...

Can I Deduct Home Office Expenses on my Tax Return 2023?

Can I Deduct Home Office Expenses on my Tax...

Canadian Tax – Personal Tax Deadline 2022

  Canadian Tax – Personal Tax Deadline 2022 Resources and Tools...

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.

Additional Resources

The following tutorials explain how to perform other common tasks in Excel:

How to Interpolate Missing Values in Excel
How to Find the Top 10 Values in a List in Excel
How to Find the Top 10% of Values in an Excel Column

Subscribe

- Never miss a story with notifications

- Gain full access to our premium content

- Browse free from up to 5 devices at once

Latest stories