How to Resample Time Series in Excel (3 Examples)

Get FREE Advanced Excel Exercises with Solutions!

If you are trying to resample time series in Excel, then you can follow this article. Sometimes you may need to redistribute the time spaces of your dataset to resample it, to represent it differently. You may want to extract a small portion of a large dataset or want to get the values for a particular time period that is not showing directly in your dataset. So, in this article, we will discuss the possible ways to resample the time series of your dataset easily.


How to Resample Time Series in Excel: 3 Examples

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

For creating this article, we have used Microsoft 365 version. However, you can use any other version at your convenience.


Example-1: Resampling a High-Frequency Time Series Data to a Low-Frequency Time Series Data in Excel

As we introduced earlier that the time duration of our dataset is between 0 to 10 and the gap between each time period is 0.1. So, we will convert this high-frequency time series distribution to a low-frequency distribution data series. In our resampled time series data the gap between each time period will be 1.
To formulate this new dataset, here we are considering the starting row number of our main dataset which is 5, and the gap between each row will be 10.

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 having the second-row number, use the following formula in cell D9.
=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.

In this way, we 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 gaining the time values insert the following formula in cell E8, press ENTER, and finally, use the AutoFill feature.
=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

  • Now we will get the population values by using the following formula in cell F8, pressing ENTER, and finally, using the AutoFill feature.
=INDIRECT(ADDRESS(D8,3))

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 a Larger Sample Data to Resample Time Series

In the following time series data, we have starting time from 6:00 AM, and then the time period increases by 1 minute.

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

This sample data ends at 7:40 AM. We want to get a sub-sample of this data to resample this time series. To do this we will gather the values for time series with a time span of 5 minutes instead of 1 minute. To do this, we have added a Helper column.

Steps:

  • Apply 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

In return, we will get TRUE for time periods after every 5 minutes.

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

Now, we have to sort this data to gather the time periods having a TRUE value in the Helper column.

  • Select the dataset and then go to the Data tab >> Sort & Filter group >> Sort.

sort

Afterward, 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 other criteria for sorting.

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

In this way, we 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 of the Time column and Population column up to TRUE in the Helper column (you have to press CTRL while selecting these non-adjacent columns) and press CTRL+C to copy it.

copy

  • After pasting these values we will be able to resample our time series data like the following figure.

paste

Read More: How to Perform Bootstrapping in Excel


Example-3: Resampling Time Series after Doing Interpolation

Here, we will resample the following time series data where we have population values starting at 0 sec and ending at 10 sec with a step of 0.1 sec between each time period. The resampled data will be gathered in the table beside this time series data. For resampling, we have to interpolate as the values are missing for these time periods. For doing the interpolation, we will be using the FORECAST.ETS function.

Performing interpolation to Resample Time Series in Excel

Steps:

  • Apply the following formula in cell E5.
=FORECAST.ETS(D5,$C$5:$C$105,$B$5:$B$105)

Here, D5 is the time value for which we are calculating the population value. $C$5:$C$105 is the range of population, and $B$5:$B$105 is the time series sample.

  • Press ENTER and drag down the Fill Handle tool.

In this way, we have completed the interpolation to create the new resampled time series data.

result

Read More: How to Calculate Bootstrapping Spot Rates in Excel


Practice Section

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

practice


Download Practice Workbook


Conclusion

In this article, we have discussed different ways to resample time series data in Excel. Hope these methods will help you a lot. If you have any further queries, then leave a comment below.


Related Articles


<< Go Back to Excel for Statistics | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
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