Smoothing a weighted average is easy to do and understand. But, it has a problem. It gives equal weight to each time period in the average. That means your smoothed data will always lag behind the current movement in the data. A way to smooth data and still make it reactive to changing trends is to use a weighted average. Data that is more recent gets a higher weighting while data in the past is given less weight.
To smooth Excel charting data with a weighted average you need to take a percentage of each month in the range being averaged and then total the results. Of course, the percentages need to total to 100%. In this figure the formula in cell G26 is,
Cells E29, E30 and E31 contain percentages. In the example E29 contains 60% so 60% of the weighting is on the first month in the average. E30 has 30% so it applies 30% of the average and the remaining 10% comes from E31. You need to make sure the total is 100%.
If you download the example and experiment you can see that by giving a higher weight to the rightmost data cell you get more advanced notice of changes in trends while still smoothing the data.