Difficulty
Intermediate
Estimated Time
20–30 minutes
The Problem
Single-month numbers are noisy.
In real reporting, analysts smooth trends using rolling averages to understand what’s really happening over time.
Your task is to calculate a rolling 3-month average of sales, a pattern commonly used in finance, forecasting, and performance monitoring.
Dataset
Create a worksheet named MonthlySales with the following data:
| Month | Sales |
|---|---|
| 2024-10 | 12500 |
| 2024-11 | 13800 |
| 2024-12 | 14200 |
| 2025-01 | 15100 |
| 2025-02 | 14750 |
| 2025-03 | 15900 |
| 2025-04 | 16250 |
| 2025-05 | 16800 |
| 2025-06 | 17200 |
Your Task
Part A — Prepare the Data
- Ensure the Month column is treated as a date (not text).
- Keep the data sorted chronologically.
Part B — Calculate the Rolling Average
- Add a new column named 3-Month Rolling Average.
Rules:
- Each value should represent the average of the current month and the two previous months
- The first two months will not have a rolling average (leave blank or show
N/A)
Part C — Trend Check (Optional)
- Add a column called Trend Direction:
"Up"if the rolling average increased vs the previous month"Down"if it decreased"Flat"if unchanged
Requirements
- Use Excel formulas only
- No manual calculations
- Results must update automatically if data changes
Expected Output (Examples)
| Month | Sales | 3-Month Avg |
|---|---|---|
| 2024-12 | 14200 | 13500 |
| 2025-01 | 15100 | 14367 |
| 2025-02 | 14750 | 14683 |
| 2025-03 | 15900 | 15250 |
(Values rounded for illustration.)
Hints (Optional)
- Use
AVERAGE()with relative ranges - Lock ranges carefully to avoid copy errors
- For Trend Direction, compare current vs previous rolling values
Learning Objectives
By completing this challenge, you will learn how to:
- Smooth time-series data
- Build rolling calculations
- Prepare Excel data for forecasting and dashboards
Why This Matters
Rolling averages appear everywhere:
- Revenue trends
- KPI monitoring
- Forecast models
- Executive dashboards
This challenge introduces time-aware thinking in Excel.
Next Challenge
Excel Challenge #7: Sales vs Target Variance Analysis
🔗 View reference solution on GitHub
(After you’ve tried the challenge)
Want more practical Excel challenges?
Subscribe to the Solve With Excel newsletter and get new problems delivered to your inbox.
SolveWithExcel 