Tanjima Hossain
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.
Download Practice Workbook
3 Ways to Resample Time Series in Excel
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.
For creating this article, we have used Microsoft Excel 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.
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$5Here, 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.
- 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”
- ADDRESS(5,2) → The ADDRESS function gives the address of a cell at the intersection point of Row 5 and Column 2.
- INDIRECT(ADDRESS(D8,2)) → becomes
- INDIRECT(“$B$5”) → The INDIRECT function returns the value of cell “$B$5”
- Output → 0
- INDIRECT(“$B$5”) → The INDIRECT function returns the value of cell “$B$5”
- 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”
- ADDRESS(5,3) → gives the address of a cell at the intersection point of Row 5 and Column 3.
- INDIRECT(ADDRESS(D8,3)) → becomes
- INDIRECT(“$C$5”) → returns the value of cell “$C$5”
- Output → 0
- INDIRECT(“$C$5”) → returns the value of cell “$C$5”
Read More: Descriptive Statistics – Input Range Contains Non-Numeric Data
Similar Readings
- Remove Last Modified By in Excel (3 Ways)
- How to Create Butterfly Chart in Excel (2 Easy Method
- Create an Organizational Chart in Excel from a List
- How to Move Up and Down in Excel (5 Easy Methods)
- Create Fibonacci Sequence in Excel ( 2 Easy Methods)
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.
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)=0Formula Breakdown
- MINUTE(B5) → becomes
- MINUTE(6:00) → The MINUTE function returns the minute value
- Output → 0
- MINUTE(6:00) → The MINUTE function returns the minute value
- MOD(MINUTE(B5),5) → becomes
- MOD(0,5) → The MOD function gives the remainder value after dividing 0 by 5
- Output → 0
- MOD(0,5) → The MOD function gives the remainder value after dividing 0 by 5
- MOD(MINUTE(B5),5)=0 → becomes
- 0=0 → returns TRUE when the remainder value is 0 otherwise FALSE
- Output → TRUE
- 0=0 → returns TRUE when the remainder value is 0 otherwise FALSE
- Press ENTER and drag down the Fill Handle tool.
In return, we will get TRUE for time periods after every 5 minutes.
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.
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.
- 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.
- After pasting these values we will be able to resample our time series data like the following figure.
Read More: How to Get Summary Statistics in Excel (7 Easy Methods)
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.
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.
Read More: How to Do Descriptive Statistics in Excel (2 Suitable Methods)
Practice Section
To practice by yourself, we have created a Practice section on the right side of each sheet.
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
- How to Calculate Root Mean Square Error in Excel
- If a Value Lies Between Two Numbers Then Return Expected Output in Excel
- How to Make Fishbone Diagram in Excel (with Easy Steps)
- Make Sankey Diagram in Excel (with Detailed Steps)
- How to Make a Venn Diagram in Excel (3 Easy Ways)
- Make a Modified Box Plot in Excel (Create and Analyze)
- How to Move Data from Row to Column in Excel (4 Easy Ways)
