How to calculate the trend for monthly data?

[bssol]

I want to create the trend line to the month average date. My manual method is to calculate the different averages for the previous months and divide them by the number of months. This give me a good trend but I have to do it manual and I want to know to do it automatically. also, what is the best way to show the trend. I'm not sure if I'm doing the best method.

[hamjam]

Hello
There is already a formula that calculates the arithmatic average so replace your formula in manual trend row by

Please Login or Register  to view this content.

Easiest is the enter the 1st cell as =average($B3:b3) and then drag this cell to the right

The best solution will be determined by the context, I may use a bar graph type but that is appropriate for my audience. I like your line graphs, typically would just put a description on the axis

Regards

[bssol]

ok, you provided a better way to calculate the average which I really appreciate.

but it there more professional methods to get the trend? I'm not convinced that the average is the best method.

any ideas?

[MrShorty]

I'm not a business guy, and you haven't stated exactly what this data represents, so I don't really know what "professional methods" are generally used for this. I also don't know that it is practical for us to give you a detailed lesson on all of the different ways one can analyze data like this.

A moving average like you are using is a fairly common way of analyzing this kind of data.
The other main technique that is used is regression/curve fitting. In these techniques, one chooses an equation that they believe will represent the data (for example, a straight line y=m*x+b). Using regression techniques appropriate to the data and the chosen equation, one then determines the parameters of the function.

[hamjam]

Hello
As highighted MrShorty and my earlier comment, you would need to describe the context of your data. So whilest moving average is the general method to examine trends in share prices for example, it is not appropriate in other applications and prehaps a regression equasion may be the better indicator.
It also depends on whether your requirement is to introduce a level of forecasting, as a best fit equasion will then assist with that.

Regards

[bssol]

Thank you for the clarification MrShorty and hamjam.

I'm now using the formula provided by hamjam and convinced that the method is correct. I originally did not know that the name of the method is "moving average". now I know that the method does actually have a name which makes it "professional" in my view.

Best of luck to you,