How to use the trendline to find percentage change?

Hi all

I’m trying to use the trendline to find the percentage change in revision percentage over time (the difference of max. and min. points of the blue dotted trendline in the graph).
These are the formulae that I have in hand but I am not too sure what they mean... Wondering if anyone can help explain the below formulae work?

After getting the percentages (rev/job):

- To get percentage decrease per month, use the formula “=LOGEST(RANGE)-1”

- To get percentage decrease over the entire range of months, use “=LOGEST(RANGE)^(COLUMNS(RANGE)-1)-1” or “=LOGEST(RANGE)^(ROWS(RANGE)-1)-1” depending on whether you have the values laid out in columns or months

Thank you for your help!!

Welcome to the forum.

Instructions about attaching your sample workbook are at the top of the page.

Your file does not include either of those formulas, so I'm not exactly sure how you are using them.

Since both formulas are based on the LOGEST() function, maybe start by understanding the LOGEST() function: https://support.office.com/en-us/art...b-a272c1d18b4b The LOGEST() function performs a linear regression and returns m and b for the equation ln(y)=ln(b)+x*ln(m) [equivalent to y=b*m^x]. When you only provide one range of values (for the known_ys argument), Excel assumes values for known_xs. When you use the function as a non=array function, it ony returns the m result and b is discarded.

So the first formula takes your known_ys range, performs the regression to get m, then subtracts 1 from the resulting m. Exactly what that means for your particular problem depends on exactly what your known_ys represent.

Your second formula takes the known_ys range, performs the regression to get m, uses the COLUMNS() or ROWS() function to determine how many entries are in known_ys, raises m to that power less 1, the subtracts 1 from the final result. Again, exactly how this is interpreted as a percentage or a growth rate or a decay rate depends on exactly what known_ys represent.

From there, we've probably got to see exactly how you are using these formulas and understand exactly what you are trying to do with these regressions.

That tells me what row you ran the regression on, but I am not sure what your question is. What I see happening:
1) You are assuming a simple exponential growth/decay model (https://www.mathsisfun.com/algebra/e...al-growth.html )
2) Your LOGEST() function (if you output both m and b) is telling you that it starts at b=4.34% at period/month 0
3) then decays at a rate of 96.5% (-3.5%) of the previous period's/month's value each period/month.
Does that seem correct? Are you expecting something different?

Thank you for looking into this! I'm not sure how the above formula forms though, like why would we need to subtract 1 in the logest formula? (sorry I am really new to logest formula ><). It would be great if you can help explain it in more detail

What I need to do is to calculate the difference between the maximum and mininum point of the blue trendline, and I have tried to obtain the corresponding values of the y-values of the trendline using logest function:
=LOGEST(I4:U4,I1:U1,TRUE,TRUE) (though I am not really sure what it means), and got the below results (I don't have any ideas for the numbers on rows 2-5 yet)

0.965616549 0.04336624
0.035803019 0.284177657
0.079884121 0.483009139
0.955015941 11
0.222803145 2.566276112

So since logest is equivalent to y=b*m^x, I substituted the value 0.04336624 to b and 0.965616549 to m, while x equals to the month

and got the below result. It seems the slope numbers match with the trendline.

Month 1 2 3 4 5 6 7 8 9 10 11 12 13
slope 4.19% 4.04% 3.90% 3.77% 3.64% 3.52% 3.39% 3.28% 3.17% 3.06% 2.95% 2.85% 2.75%
actual 4.30% 2.95% 6.11% 3.04% 4.49% 1.50% 2.77% 8.22% 5.67% 2.26% 3.48% 2.17% 2.26%

and when I use this formula to calculate the difference of max and min pt of trendline = (2.75%-4.19%)/4.19%. It is still -0.342862273080, which is the same as the result obtained from =LOGEST(I4:U4)^(COLUMNS(I4:U4)-1)-1

Rows 2-5 of the LOGEST() output are additional statistics. The LOGEST() help file failed to describe them, but the LINEST() help file does: https://support.office.com/en-us/art...a-fa7abf772b6d

when I use this formula to calculate the difference of max and min pt of trendline = (2.75%-4.19%)/4.19%. It is still -0.342862273080, which is the same as the result obtained from =LOGEST(I4:U4)^(COLUMNS(I4:U4)-1)-1

How much of your question is Excel and how much is math/algebra? Because the explanation for this is simple algebra:
y13=b*m^13
y1=b*m^1
(y13-y1)/y1=(b*m^13-b*m^1)/b/m^1 -> b cancels
=(m^13-m^1)/m^1 -> distributive property
=m^12-1

You might review the basic math behind exponential growth/decay, if that is what you are having trouble with: https://www.mathsisfun.com/algebra/e...al-growth.html

Administrative Note:

Welcome to the forum.

We would very much like to help you with your query, however it has been brought to our attention that the same query has been posted on one or more other forums and you have not provided the required cross-post link(s) here.

Please see Forum Rule #3 about cross-posting and adjust accordingly. Read this to understand why we (and other sites like us) consider this to be important.

I will add it for you.Please read forum rules

https://www.mrexcel.com/board/thread...hange.1125169/
https://chandoo.org/forum/threads/ho...3/#post-260910