Michel asks how to get the chart trendline coefficients from the chart into the grid.
Table of Contents
(0:00) Question from Michel
(0:27) Adding a trendline to a chart with the formula
(1:14) How to tell if your Excel has Dynamic Arrays
(1:30) Entering LINEST function with additional statistics
(2:54) Entering LINEST with Ctrl+Shift+Enter (Before Dynamic Arrays)
Table of Contents
(0:00) Question from Michel
(0:27) Adding a trendline to a chart with the formula
(1:14) How to tell if your Excel has Dynamic Arrays
(1:30) Entering LINEST function with additional statistics
(2:54) Entering LINEST with Ctrl+Shift+Enter (Before Dynamic Arrays)
Transcript of the video:
Learn Excel from MrExcel podcast episode 2338.
Extract the trendline formula to the grid.
Hey welcome back to the MrExcel netcast. I'm Bill Jelen.
Today's question sent in by Michel.
"How can I extract the coefficients from a chart trendline formula to the grid?
I am typing them manually now but when the inputs change i have to type them again". All right this is a great question.
First off, what is Michel talking about?
We have a line chart here that's based on that data.
If i right-click on the line and say that i want to add a trendline.
Over here on the far right-hand side I can choose which type of trendline.
Linear, Log, Polynomial, Power, Moving Average. I can even forecast into the future.
But the one that Michel is talking about is Display The Equation On The Chart.
And possibly Display The R-squared Value On The Chart.
See back here in the chart we get this nice little text box. But it's just a text box.
How do we extract those numbers into the grid?
Now, there are two different answers to this, depending on whether you have dynamic arrays or not.
Dynamic arrays - just last week in July 2020 were released to the general population of Microsoft 365 subscribers.
So this great feature that we've had on Insiders for over a year now has reached a lot more people.
Here is how you can tell if you have it. Come out here and type =SEQUENCE(.
Choose any number. Five.
If you get an answer, then you have dynamic arrays. If you don't (get an answer).
If you get a #NAME! error then you are going to have to do the harder steps.
With dynamic arrays, type = LINEST(. What do you call LINEST?
Do you say "linest" or do you call it "Line S T"? My friend Claire calls it "Linest".
I always called it "Line S T". I don't know.
Let me know, down in the YouTube comments how you actually pronounce that function.
It stands for straight line regression.
It asks for the known y's.
The Y values is the height of the line. So that's B2 to B41.
For the Known x's, A2 to A41. Then, is the intercept forced to be zero?
You want to say "True" so it is calculated normally.
It doesn't have to be zero.
Then most important: ask for all of the additional regression statistics.
using a second True. To recap: point to the y's. Point to the x's.
True, True. It returns five rows two columns.
You will see that the formula is "Y = mx + b".
The b, or the y intercept is there in the second column, first row.
The m value, the slope, the 27.117 is there in D2.
The R-squared is down here in row three column one.
These other statistics I have those in a book. But I don't have them memorized.
I would have to go pull the book out to tell you what those are.
All right now if you do have to do this without dynamic arrays. It is pretty weird.
You have to select the five rows two columns. Select that whole range. Type the formula.
But don't press Enter.
Instead hold down Control and Shift and then press Enter.
That gives you an old-fashioned array formula wrapped in the curly braces like that.
There you go. I want to thank Michel for sending that question in.
If you enjoy these videos please subscribe down below and ring that bell.
Feel free to post any questions or comments in the comments below.
My new book: MrExcel 2020. Click that "I" in the top right-hand corner for info.
I'll see you next time for another netcast from MrExcel.
Extract the trendline formula to the grid.
Hey welcome back to the MrExcel netcast. I'm Bill Jelen.
Today's question sent in by Michel.
"How can I extract the coefficients from a chart trendline formula to the grid?
I am typing them manually now but when the inputs change i have to type them again". All right this is a great question.
First off, what is Michel talking about?
We have a line chart here that's based on that data.
If i right-click on the line and say that i want to add a trendline.
Over here on the far right-hand side I can choose which type of trendline.
Linear, Log, Polynomial, Power, Moving Average. I can even forecast into the future.
But the one that Michel is talking about is Display The Equation On The Chart.
And possibly Display The R-squared Value On The Chart.
See back here in the chart we get this nice little text box. But it's just a text box.
How do we extract those numbers into the grid?
Now, there are two different answers to this, depending on whether you have dynamic arrays or not.
Dynamic arrays - just last week in July 2020 were released to the general population of Microsoft 365 subscribers.
So this great feature that we've had on Insiders for over a year now has reached a lot more people.
Here is how you can tell if you have it. Come out here and type =SEQUENCE(.
Choose any number. Five.
If you get an answer, then you have dynamic arrays. If you don't (get an answer).
If you get a #NAME! error then you are going to have to do the harder steps.
With dynamic arrays, type = LINEST(. What do you call LINEST?
Do you say "linest" or do you call it "Line S T"? My friend Claire calls it "Linest".
I always called it "Line S T". I don't know.
Let me know, down in the YouTube comments how you actually pronounce that function.
It stands for straight line regression.
It asks for the known y's.
The Y values is the height of the line. So that's B2 to B41.
For the Known x's, A2 to A41. Then, is the intercept forced to be zero?
You want to say "True" so it is calculated normally.
It doesn't have to be zero.
Then most important: ask for all of the additional regression statistics.
using a second True. To recap: point to the y's. Point to the x's.
True, True. It returns five rows two columns.
You will see that the formula is "Y = mx + b".
The b, or the y intercept is there in the second column, first row.
The m value, the slope, the 27.117 is there in D2.
The R-squared is down here in row three column one.
These other statistics I have those in a book. But I don't have them memorized.
I would have to go pull the book out to tell you what those are.
All right now if you do have to do this without dynamic arrays. It is pretty weird.
You have to select the five rows two columns. Select that whole range. Type the formula.
But don't press Enter.
Instead hold down Control and Shift and then press Enter.
That gives you an old-fashioned array formula wrapped in the curly braces like that.
There you go. I want to thank Michel for sending that question in.
If you enjoy these videos please subscribe down below and ring that bell.
Feel free to post any questions or comments in the comments below.
My new book: MrExcel 2020. Click that "I" in the top right-hand corner for info.
I'll see you next time for another netcast from MrExcel.
