While you may be experiencing exponential decay, your rating system won't work with that. To find R-squared, select the cell with the correlation formula and. The function then extends the linear trendline to calculate additional y-values for a further supplied set of new x-values. Excel really isn't designed with this in mind and doesn't have the functions for this. The Excel formula for finding the correlation is CORREL(Data set 1, Data set 2). The Excel TREND function calculates the linear trend line through a given set of y-values and (optionally), a given set of x-values.

I am a little concerned that your model may be fragile for the purposes of making a prediction. Is a road rated as a 10 actually 10 times better than one whose rating is a 1 and is one rated a 10 exactly 2 times better than one rated a 5? Your model also does not consider changes in weather or the comparability of building materials from twenty years ago to today.įinally, your dependent variable is probably ranked data. Although this loosely implies a quadratic-like model, the maximum value should always be for a new road and it is not under your specification. It seems like roads remain quite good for a long time, but once they begin deteriorating they begin to come apart faster and faster. You can also extend trendlines to forecast future data. It may be the case that no pre-built Excel model will be a good predictive fit. Microsoft Office Microsoft Excel How to Work with Trendlines in Microsoft Excel Charts Alan Murray Computergaga1 Aug 2, 2019, 9:00 am EDT 3 min read You can add a trendline to a chart in Excel to show the general pattern of data over time. For example, I am assuming that roads cannot spontaneously improve themselves. My concern with your model is that you may be ignoring important mathematical properties and get bad predictions.

Trendline equation in excel