How to work out general trend in a series of numbers (film rankings)

[stefanaalten]

Hi,

I have a list of film titles with position rankings. A simplified example is shown below. I would like to work out what the "general trend" (sorry to be vague) is for a given title's position rankings, i.e. whether a film generally "improves" (or "worsens") over time. Just to get an idea of which ones "stand the test of time". I thought this would be an easy thing to work out (so easy I could do it by myself! ) but I've still managed to get myself all muddled up and would be grateful for help from the community!

Points to clarify:
1) A title may have a position in some, all or no years.
2) A position is a number between 1-1000, with 1 being highest, 1000 being lowest. Position rankings below 1000 are omitted.

Book1
	A	B	C	D	E	F	G
1	Title	Pos 2016	Pos 2017	Pos 2018	Pos 2019	Pos 2020	Trend
2	A	10	25	35	21	15
3	B	100	130	190
4	C			600	599
5	D		200	200	130	170
Sheet1

I've been dabbling with the SLOPE function but not getting very far with that. If at all possible I would like to keep the formula to a single column (column G in my example) and avoid the need to insert many additional "helper" columns (though can do this if necessary). Can anyone please help?

(I'm getting these rankings from theyshootpictures.com, an excellent film reference site.)

Many thanks!

 

[Cubist]

How about a linear trend?

Book1
	A	B	C	D	E	F	G
1	Title	2016	2017	2018	2019	2020	2021
2	A	10	25	35	21	15	23
3	B	100	130	190			320
4	C			600	599		597
5	D		200	200	130	170	135
Sheet8

Cell Formulas
Range	Formula
C1:F1	C1	=B1+1
G2:G5	G2	=FORECAST.LINEAR($G$1,B2:F2,$B$1:$F$1)

 

[stefanaalten]

Many thanks for that @Cubist , but it's not quite what I'm after (and I do realise I was rather vague with my initial question!) That tells me "what is the position of this film likely to be next year", but I'm really after something that tells, at a glance, whether a film has, over time, remained constant in its position, improved, or declined. So, taking title A in the above example, the number "23", taken by itself, does not show whether the film has remained constant/improved/declined.

 

[Eric W]

You could use the slope to estimate the trend:

Book1
	A	B	C	D	E	F	G	H
1	Title	Pos 2016	Pos 2017	Pos 2018	Pos 2019	Pos 2020	Trend	Est. Cur. Rating
2	A	10	25	35	21	15	0.6	24.8
3	B	100	130	190			45	455
4	C			600	599		-1	594
5	D		200	200	130	170	-16	87
Sheet6

Cell Formulas
Range	Formula
G2:G5	G2	=SLOPE(B2:F2,MID(B$1:F$1,5,4)+0)
H2:H5	H2	=FORECAST(YEAR(TODAY()),B2:F2,MID(B$1:F$1,5,4)+0)

In this case, title A has a trend close to zero, so its position is pretty static. Title B has a large positive value, so it is getting a higher rating (or a worse position) over time. And with a significant negative value, title D seems to be getting "better" over time.

 

[Cubist]

Many thanks for that @Cubist , but it's not quite what I'm after (and I do realise I was rather vague with my initial question!) That tells me "what is the position of this film likely to be next year", but I'm really after something that tells, at a glance, whether a film has, over time, remained constant in its position, improved, or declined. So, taking title A in the above example, the number "23", taken by itself, does not show whether the film has remained constant/improved/declined.

You can look at the slope. A negative slope indicates an increase in position, and vice versa.

Book1
	A	B	C	D	E	F	G
1	Title	2016	2017	2018	2019	2020	Trend
2	A	10	25	35	21	15	Decline
3	B	100	130	190			Decline
4	C			600	599		Increase
5	D		200	200	130	170	Increase
Sheet8

Cell Formulas
Range	Formula
C1:F1	C1	=B1+1
G2:G5	G2	=LET(s,SLOPE(B2:F2,$B$1:$F$1),IF(s<0,"Increase",IF(s>0,"Decline","No Change")))

 

[stefanaalten]

Thanks both @Eric W and @Cubist - both your posts provided the solution I was looking for!