How to Resample Time Series in Excel (3 Examples)

Dataset Overview

Here, we have the following time series and the gap between time is 0.1, starting from 0 to 10. With respect to these time periods, we have kept records of the population of a species. We will use this dataset for most of the cases in the following examples to resample this time series.

dataset to Resample Time Series in Exceldataset to Resample Time Series in Exceldataset to Resample Time Series in Excel


Example 1 – Resampling a High-Frequency Time Series to a Low-Frequency Time Series

  • You have a high-frequency time series dataset with a gap of 0.1 between each time period.
  • To convert it to a low-frequency distribution, where the gap between each time period is 1, follow these steps:
    • Consider the starting row number (which is 5 in your case).
    • Calculate the next row number by adding the step value (10 in this case) to the previous row number.

conversion of high frequency to low frequency to Resample Time Series in Excel

Steps:

  • Link up the value of cell D8 with the Start Row column.
=E4

  • For the second-row number (in cell D9) enter:
=D8+$E$5

Here, D8 is the previous cell value, and $E$5 is the step value which will be added to the previous row number to give us the next row number.

  • Press ENTER and drag down the Fill Handle tool.

You have gathered the row numbers from which we will extract the time series data and their corresponding population.

conversion of high frequency to low frequency to Resample Time Series in Excel

  • For the time values (in cell E8) enter:
=INDIRECT(ADDRESS(D8,2))

 Formula Breakdown

  • ADDRESS(D8,2) → becomes
    • ADDRESS(5,2) → The ADDRESS function gives the address of a cell at the intersection point of Row 5 and Column 2.
      • Output → “$B$5”
  • INDIRECT(ADDRESS(D8,2)) → becomes
  • Press ENTER and use the AutoFill feature to copy the formula down to E18.

  • For the population values (in cell F8) enter:
=INDIRECT(ADDRESS(D8,3))
  • Press ENTER and use the AutoFill feature to copy the formula down to F18.

Formula Breakdown

  • ADDRESS(D8,3) → becomes
    • ADDRESS(5,3) → gives the address of a cell at the intersection point of Row 5 and Column 3.
      • Output → “$C$5”
  • INDIRECT(ADDRESS(D8,3)) → becomes
    • INDIRECT(“$C$5”) → returns the value of cell “$C$5”
      • Output → 0

conversion of high frequency to low frequency to Resample Time Series in Excel


Example 2 – Extracting a Sub-Sample from Larger Data

  • You have a time series dataset with a starting time of 6:00 AM and a 1-minute time period increment.
  • You want to extract a sub-sample with a time span of 5 minutes.

Extracting a Sub-Sample from a Larger Sample Data to Resample Time Series in Excel

  • Add a Helper column.

Steps:

  • Enter the following formula in cell C5:
=MOD(MINUTE(B5),5)=0

Formula Breakdown

  • MINUTE(B5) → becomes
  • MOD(MINUTE(B5),5) → becomes
    • MOD(0,5) → The MOD function gives the remainder value after dividing 0 by 5
      • Output → 0
  • MOD(MINUTE(B5),5)=0 → becomes
    • 0=0 → returns TRUE when the remainder value is 0 otherwise FALSE
      • Output → TRUE
  • Press ENTER and drag down the Fill Handle tool.

formula

This formula returns TRUE for time periods every 5 minutes.

Extracting a Sub-Sample from a Larger Sample Data to Resample Time Series in Excel

  • Sort the data based on the Helper column:
    • To gather the time periods having a TRUE value.
      • Select the dataset and then go to the Data tab, click on Sort & Filter group, and select Sort.

sort

  • The Sort dialog box will appear.
    • Select the following options.
      • Sort by → Helper
      • Sort On → Cell Values
      • Order → Largest to Smallest
    • Click on Add Level option to add additional criteria for sorting.

    • Choose the following options.
      • Then by → Time
      • Sort On → Cell Values
      • Order → Smallest to Largest
    • Press OK.

You have gathered all of the time periods with a gap of 5 minutes between each other.

Extracting a Sub-Sample from a Larger Sample Data to Resample Time Series in Excel

  • Select the values corresponding to TRUE in the Helper column (press CTRL while selecting non-adjacent columns) and copy them.

copy

  • You can now resample this sub-sample.

paste

Read More: How to Perform Bootstrapping in Excel


Example 3 – Resampling Time Series after Interpolation

In this scenario, we have a time series dataset with population values recorded at intervals from 0 seconds to 10 seconds, with a step of 0.1 seconds between each time period. Our goal is to resample this data and fill in missing values using interpolation. We’ll achieve this by leveraging the FORECAST.ETS function.

Performing interpolation to Resample Time Series in Excel

Steps:

  • For each desired time value (e.g., D5), enter the following formula in cell E5:
=FORECAST.ETS(D5,$C$5:$C$105,$B$5:$B$105)
    • Here:
      • D5 represents the specific time value for which we want to calculate the population.
      • $C$5:$C$105 is the range of known population values.
      • $B$5:$B$105 corresponds to the time series sample.
  • Press ENTER and drag down the Fill Handle tool.

This completes the interpolation process, resulting in a new resampled time series dataset.

result

Read More: How to Calculate Bootstrapping Spot Rates in Excel


Practice Section

To practice, we have created a Practice section on the right side of each sheet.

practice


Download Practice Workbook

You can download the practice workbook from here:


Related Articles


<< Go Back to Excel for Statistics | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!
Tanjima Hossain
Tanjima Hossain

TANJIMA HOSSAIN is a marine engineer who enjoys working with Excel and VBA programming. For her, programming is a handy, time-saving tool for managing data, files, and online tasks. She's skilled in Rhino3D, Maxsurf C++, MS Office, AutoCAD, and Excel & VBA, going beyond the basics. She holds a B.Sc. in Naval Architecture & Marine Engineering from BUET and is now a content developer. In this role, she creates tech-focused content centred around Excel and VBA. Apart from... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo