As a CFO, you are constantly looking for ways to manage financial risk and predict future performance. One of your biggest fears? Volatile monthly data that makes it hard to spot trends or make informed decisions.
When numbers spike or dip unexpectedly, it can be difficult to get a clear picture of your company’s financial health. But what if there was a way to smooth out those fluctuations and identify meaningful patterns? That’s where a rolling 3-month average comes in.
A rolling average helps you eliminate noise from short-term data changes, allowing you to see the bigger picture. In this blog, we’ll show you how to calculate a rolling 3-month average in Excel, step by step.
Whether you’re tracking sales, expenses, or other key metrics, this technique can help you stay ahead of the curve and make more strategic decisions. By looking at a 3-month average, you can filter out anomalies and see how your business is really performing. This allows for better strategic planning and more accurate predictions.
Let’s start from the beginning.
What Is a Rolling 3-Month Average?
A rolling 3-month average is a calculation that takes the average of the last three months of data, shifting with each new period. This is a useful tool in data analysis because it helps you track trends over time without getting thrown off by monthly volatility.
Why Use a Rolling Average?
Rolling averages smooth out short-term variations in your dataset, helping you focus on long-term patterns. For example, in business, you might use this technique to identify seasonal trends or long-term sales growth. By looking at an average of three months rather than just one, you can get a more reliable snapshot of performance.
Example Dataset
To better understand how a rolling 3-month average works in practice, let’s take a look at a sample dataset that tracks monthly sales over a year
The Dataset Featuring Monthly Data Over a Year
Let’s imagine you’re analyzing sales data for a business over 12 months. Your dataset contains monthly sales figures, starting from January to December. Each row represents a month, and the column labeled “Sales” shows the total revenue for that particular month.
For example, the data might look something like this:
| Month | Sales ($) |
| January | 50,000 |
| February | 52,000 |
| March | 48,000 |
| April | 55,000 |
| May | 53,000 |
| June | 60,000 |
| July | 62,000 |
| August | 58,000 |
| September | 59,000 |
| October | 61,000 |
| November | 63,000 |
| December | 64,000 |
You can use this database to calculate a rolling 3-month average of sales figures, providing a smoother view of performance trends by filtering out short-term fluctuations and identifying longer-term patterns.
Importance of Choosing the Correct Dataset for Accurate Results
The accuracy of your rolling 3-month average depends heavily on the quality and relevance of the dataset you choose. To get meaningful results, ensure the dataset you use has the following characteristics:
- Consistency and Completeness: Your dataset should have complete monthly entries for your analysis period. Missing data can distort your averages, so filling gaps or handling missing values is crucial.
- Relevance to the Analysis: Ensure the dataset you use is directly related to the objective of your analysis. For example, if you’re analyzing sales trends, using a dataset that includes external factors like marketing campaigns or product launches may enhance the relevance of your rolling average.
- Appropriate Timeframe: The timeframe you choose should align with your analysis objectives. For instance, a 3-month rolling average might be ideal for short-term sales trends, but if you’re looking at yearly growth, you might need to consider longer periods like a 12-month moving average.
In short, selecting the correct dataset ensures the accuracy of your rolling average calculation and helps provide actionable insights that can influence key business decisions.
How to Calculate the Rolling 3-Month Average
Here’s how you can calculate a 3-month average.
Step 1: Setting Up Your Data
Make sure your data is arranged chronologically, with each month’s values listed in order. The more organized your dataset is, the easier it is to work with the formula.
Step 2: Formula Entry
Now, in the cell where you want your rolling average to appear, type the following formula:
| =AVERAGE(B2:B4) |
This formula calculates the average of the values in cells B2, B3, and B4. The AVERAGE function in Excel simplifies the calculation, giving you the mean value of these three months.
Step 3: Formula Application
Next, drag the formula down the column to apply it to the remaining dataset. To do this, hover over the bottom right corner of the cell until the cursor changes to a cross, then drag down. This action ensures the formula adjusts automatically to calculate the average for each subsequent 3-month period.
Note: When dragging the formula, you need to be cautious so that you don’t overwrite or skip any rows.
Step 4: Results Interpretation
Once the formula is applied, you’ll see a new 3-month average for each period. For example, the average for March would cover data from January, February, and March. This gives you a more stable value than any month’s data.
Step 5: Automating the Formula
The great thing about using a rolling average in Excel is that it updates dynamically. Each time you add new data, Excel recalculates the average for the relevant months. This makes it especially useful for large datasets where manually recalculating each time would be time-consuming.
Special Considerations
When calculating a rolling 3-month average, it’s important to consider a few key factors to ensure the accuracy and reliability of your results.
1. Starting the Formula in the Correct Cell for Accurate Results
When calculating a rolling 3-month average, starting the formula in the correct cell is crucial to ensure that your results are accurate. The first instance of the rolling average, typically in the third data point (since it’s a 3-month average), should only include the preceding three months of data.
- Correct Cell Placement: For example, if your data starts in cell B2, you would begin the formula in cell B4 to calculate the average of months 1 to 3. Starting the formula too early (in B2 or B3) would result in incomplete or inaccurate averages, as those cells would lack enough historical data.
- Avoid Misinterpretation: If the formula is placed in the wrong cell, it could skew the analysis and provide incorrect moving averages, misleading your data-driven decisions. Make sure that your initial formula always includes a complete set of prior months (in this case, three months).
2. Handling Datasets with Missing or Incomplete Data
It’s common to encounter missing or incomplete data in real-world datasets. A rolling average can be sensitive to gaps in the data, which may distort your averages if not handled correctly.
- Identifying Missing Data: Missing data could occur due to various reasons—such as incomplete records or seasonal factors. It’s important to identify and assess the impact of missing values on your rolling average. An obvious gap in data could make the average unreliable and give you false insights.
- Handling Gaps: There are several strategies for handling incomplete data:
- Ignore Missing Data: One simple approach is to use the AVERAGE function as is, but this may lead to less reliable averages. Excel will skip empty cells in an average calculation, but this can still be problematic if the gaps are frequent or spread out over time.
- Use IF Statements to Handle Missing Data: A more refined approach would involve using an IF condition to check for missing or incomplete data. For example, you can use:
| =AVERAGE(IF(ISNUMBER(B2:B4), B2:B4)) |
This formula ensures that only cells containing numbers are considered in the average, avoiding the impact of empty cells.
- Interpolate Missing Values: In cases where the missing data can be reasonably estimated, you might interpolate the missing values based on previous months or averages of nearby months. This ensures that gaps in your dataset don’t unduly affect your analysis.
Handling incomplete or missing data with care is essential for ensuring that your rolling 3-month average provides meaningful and accurate insights.
Common Variations and Extensions
As your dataset grows or changes, it’s essential to have a flexible formula that automatically adjusts to accommodate new data. One powerful way to achieve this is by using the OFFSET and COUNT functions, which allow you to create a dynamic rolling average that updates automatically as new data is added.
- Dynamic Calculations with New and Incoming Data: This variation is ideal for large datasets or when your data is frequently updated. The COUNT function counts the number of entries in your data range, and the OFFSET function helps you create a reference to a range that adjusts as data changes.
- Example Formula:
| =AVERAGE(OFFSET(B2, COUNT(B2:B100)-3, 0, 3, 1)) |
In this formula, the OFFSET function adjusts the average range of cells as new data is added. This ensures that the rolling average always reflects the most recent three months of data, even as the dataset grows.
Adapting the Formula for Rows
If you’re working with data that’s laid out in rows instead of columns, you can easily adapt the rolling average formula to accommodate this layout. This variation works well when your data spans multiple rows rather than columns, such as when tracking monthly figures across a spreadsheet.
- Formula Adaptation for Row Calculations: Instead of working down a column, this adaptation allows you to calculate the average for a horizontal range of cells. You’ll still be able to calculate a rolling average, but the formula will reference the row instead of the column.
- Example Formula:
| =AVERAGE(OFFSET(B2, 0, COUNT(B2:N2)-3, 1, 3)) |
Here, OFFSET dynamically references the last three cells within the row. This formula works well when the data spans across a row, with monthly values recorded horizontally.
Creating a Moving Average Chart in Excel
Visualizing your rolling average data can provide even greater insights into trends and patterns. Excel makes adding a moving average trendline to your charts easy, giving you a powerful visual tool to complement your numerical analysis.
Steps to Add a Moving Average Trendline to a Chart
- Create Your Chart: Start by selecting your data and inserting a chart, such as a line or column chart, that best represents the trends you’re tracking.
- Add Trendline: Click on the data series on the chart, and in the “Chart Tools” section, select “Add Trendline.” Choose the “Moving Average” option from the available trendline options.
- Adjust the Period: In the trendline options, you can specify the period for the moving average (e.g., 3 months) to match your rolling average calculation.
Visual Adjustments and Customization Options for Trendline
Excel offers several customization options for your moving average trendline. You can change the line style and color, or even make the trendline appear as a dashed line to differentiate it from the rest of your chart. You can also adjust the number of periods used in the moving average to match your analysis needs.
Differentiating Between Excel Versions for Trendline Creation
Remember that different Excel versions may have slightly varying methods for adding trendlines. In older versions, you might need to access the trendline option through a different menu or interface.
However, the general steps remain the same. If you’re using Excel 2016 or later, adding a moving average trendline is straightforward and can be done directly from the “Chart Elements” button.
These variations and extensions allow you to customize your rolling average calculations and visualizations to suit your specific needs, whether you’re working with large datasets, horizontal layouts, or visualizing trends with charts.
End Note
Calculating a rolling 3-month average is a powerful tool for smoothing out short-term fluctuations and revealing long-term trends in your data. By following the steps outlined in this guide, you can easily set up a dynamic formula that adapts to new data, ensuring your insights remain accurate and up-to-date.
Whether you’re analyzing sales figures, tracking performance metrics, or identifying seasonal patterns, the rolling average provides a clear and reliable view of trends over time.
Tools like Bunker can further streamline the process of managing finances or tracking business performance. Bunker’s dashboards automate many of these calculations, allowing you to track key financial metrics, including cash flow and accounts receivable, with ease.
Integrating rolling averages into Bunker’s intuitive platform can help you confidently make data-driven decisions, ensuring long-term financial stability and growth.
Want to streamline your financial analysis? Start applying the rolling 3-month average to your own data and unlock clearer insights into your trends. For a more automated approach, explore Bunker—our powerful platform that makes tracking key metrics and managing data simpler than ever. Get started with Bunker today and take control of your financial future!
