How to find smoothest/most stable line in a dataset

[magnum55]

Hi All,

I have 100 countries, all with 36 months of sales data and Im trying to find the countries with the smoothest data set - by that i mean the country with least peaks and troughs in its data. Can anyone suggest the best way to do this rather than by eye? Is there was a formula which determines "smoothness"?

Many Thanks

 

[Sulprobil]

Take the lowest STDEV.

 

[magnum55]

I considered the standard deviation but does this take into account the size of each of the countries? Some of the countries are 50 times larger than others. Im looking for a way to compare across the countries. I also assumed that a 45 degree sales line and a horizontal line would give differing standard deviation values when they are as smooth as each other. Put another way I'm looking to identify the least volatile line i.e the red line below

 

[Sulprobil]

The "smoothest" lines in your picture seem to be lines closest to the bottom
Maybe you want to take the STDEV of the differences between Sales planned vs Sales realized?
Or are you just after lines which are very linear, i. e. are looking like a straight line? Then you might want to calculate the square root of the total of each squares of differences between your line values and their FORECAST line.

 

[magnum55]

Hi, The lines at the bottom look smoothest but thats the effect of the axis. Maybe the term i am using is incorrect. I'm looking for the least volatile or the most linear as you say. I know how to calculate the Forecast line but how do i calculate the square root of the total of each squares of differences between each line values and their FORECAST line. Is this an excel function?

Many Thanks

 

[Sulprobil]

This is what I had in mind:

Mappe1
	D	E	F	G	H	I	J	K	L
4		0	0,26352314	0,94758465	0,27386128
5		a	b	c	d	Fa	Fb	Fc	Fd
6	1	1	0,5	2	3	1	0,72222222	2,2	2,8
7	2	2	1	3	3	2	0,72222222	2,98333333	3,1
8	3	3	0,5	5	3	3	0,72222222	3,76666667	3,4
9	4	4	1	4	4	4	0,72222222	4,55	3,7
10	5	5	0,5	6	4	5	0,72222222	5,33333333	4
11	6	6	1	4	4	6	0,72222222	6,11666667	4,3
12	7	7	0,5	7	5	7	0,72222222	6,9	4,6
13	8	8	1	8	5	8	0,72222222	7,68333333	4,9
14	9	9	0,5	9	5	9	0,72222222	8,46666667	5,2
Tabelle1

Cell Formulas
Range	Formula
E4:H4	E4	=STDEV.S(E6:E14-I6:I14)
I6:L14	I6	=FORECAST.LINEAR($D$6:$D$14,E6:E14,$D$6:$D$14)
Press CTRL+SHIFT+ENTER to enter array formulas.

Fa is the Forecast of a, and since a is identical to Fa the corresponding STDEV of its difference from its forecast is 0, a is the smoothest here.

 

[magnum55]

Ah I think this is exactlty what im looking for so i interpret this correctly,
Row 4 is the STDEV of the actuals vs the forecast line - the closer the number to 0, the more linear the line?
Column D is the time period,
Col E-H contains the data,
Cols I-K are the forecast lines

Many Thanks

 

[Sulprobil]

Yes, correct.

 

[magnum55]

That's great many thanks for this!!