Trendlines

[jtj10003]

Hey All, I'm trying to determine the percentage rate of change in a trendline. I've got a sales chart with 90 days worth of sales figures. I can see that the trendline is trending down, but I'm not sure exactly how much.

If you take the first data point in a trendline and subtract it from the last data point and then divide that by the first it should give you the percent change. Excel must be able to do that math for you but I can't even figure it out to save my life. I can't even figure out how to display the first and last data point much less the "rate" of change I'm looking for. Any help would be great. Thanks.

[shg]

You can display the equation of the trendline on the chart (right-click the trendline) if you just want a glance at the slope, or you can get the parameters using LINEST.

[jtj10003]

Thanks for the response but what I'm looking for is a percentage value. If I had a hypothetical sales history of $100 on Monday and $90 on Tuesday then my sales would be trending down at 10%. I'm just looking for a way for excel to give me that 10% number when I have 90 days worth of sales trend analysis as opposed to two.

[shg]

Post a workbook and I'll show you.

[jtj10003]

Thanks again. I'm sure I'm missing something easy. Here's and example workbook. It shows a down trend line. The question is what percentage is the downward trend?

[shg]

You can read it right off the trendline formula. The slope is -0.986, so the trendline trends down by (1 - 0.986) or 1.4% per period.

If the slope were, say, 1.1, then it would be trending up by 10% per period. Capische?

To see that same value (the 0.986) in a cell, =LINEST(H14:H25).

[shg]

Gotta correct that, sorry.

You're using a linear trendline, so the interpretation of -0.986 is that sales is decreasing by 0.986 per period.

If you were instead using an exponential trendline (which would almost always be the right choice), then the formula you'd see is

y = 100.9044e-0.010473x

Without explaining the details (but I will if you want), to see how much sales are changing per period,

=LOGEST(H14:H25) - 1

Format the result as % to see -1.04%.

[jtj10003]

I think I'm probably just being dense here, or just asking the question poorly, but in the example the downturn should be more like 10% rather than 1.4%.

Looking at that trendline it starts at roughly 99 and ends at roughly 89. So to find the %change take (end - beginning) / beginning * 100 or 89 - 99 * 100 = -10.10101%. The answer to the question, "how much are we down" is therefore, "about -10%."

I was just looking for a setting that would show that -10% figure without having to guesstimate the start and end point of the trendline and do the (albeit simple) math. Sorry if this is a hassle and thanks again for the response.

[shg]

You have 11 periods (12 measurements). A best-fit exponential series says you're decreasing at an average rate 1.04% per period. You can calculate the decrease over 11 periods like this:

= LOGEST(H14:H25) ^ (ROWS(H14:H25) - 1) - 1 ~10.9%

That the number you're looking for?

BTW, the reason you should use an exponential trendline (instead of linear) is that many thing (like sales, and absent complexities like market saturation) tend to grow (or decline) by some fraction of their size per period -- and that's intrinsically an exponential series. People talk about growing, say, 5% quarter over quarter, not some fixed amount (e.g., 100K) per quarter.

[jtj10003]

Outstanding. I'll plug it into my charts and let you know what happens. Thanks very much.

[bornmccready]

I'm interested in doing this same analysis but I have positive and negative margins over time. My understanding is exponential won’t work with zero’s or negatives. Would it be appropriate to use =LINEST(H14:H25)/AVERAGE(H14:H25)? I haven’t been able to find anything better. Any help appreciated.

PS Sorry will start new thread

[Nicktheswitchhitter]

"If you were instead using an exponential trendline (which would almost always be the right choice), then the formula you'd see is

y = 100.9044e-0.010473x

Without explaining the details (but I will if you want), to see how much sales are changing per period,

=LOGEST(H14:H25) - 1

Format the result as % to see -1.04%."



You do realize you are stating that an Exponential Trend line and that Logarithmic function are the same... when they are not the same. Using the LOGEST function to compare to the LOGARITHMIC trend line equation is more accurate. OR an exponential function to an exponential equation would make sense. Apples to apples.

While i do agree that the LOGEST will work perfectly fine to give the trending % increase or decrease, it's not right to say an exponential trend line equation will give the same results as a log function.

Deciding between an exponential trend line and a logarithmic trend line depends on the coefficient of determination (r^2 value, the closer to 1 the better fit for your data).