Excel Tutorial: Finding Trendline Equation and Calculating Rates of Change

undefined
 
H
OW
 
TO
 
FIND
 
THE
 
EQUATION
 
OF
 
A
TRENDLINE
 
IN
 
EXCEL
, 
AND
 
USE
 
THAT
EQUATION
 
TO
 
CALCULATE
 
RATES
 
OF
CHANGE
D
EVELOPED
 
ON
 2016 W
INDOWS
 E
XCEL
(
DETAILS
 
MAY
 
BE
 
SLIGHTLY
 
DIFFERENT
 
ON
 
OTHER
 
VERSIONS
)
 
S
TEP
 1
 
Import your data
into excel
Place your x-
values in the left
hand row, and
your y-values in
the right hand
row
 
S
TEP
 2
 
To plot your data, go
insert 
 charts 
 line
graph (or whichever style
of graph you are looking
for)
 
Your graph should then
appear
 
S
TEP
 3
 
To add a trendline,
go design 
 Add
chart element 
scroll down the list
and hover over
trendline 
 click
on linear trendline
 
S
TEP
 4
 
Your trendline may be hard to see (shown in dotted blue line here)
Double-click on your trendline
A right hand window will pop up (format trendline window)
 
S
TEP
 5
 
On the format trendline window, click the paint bucket and
change the appearance of your trendline
 
S
TEP
 6
 
To display the equation of the line, click on the chart symbol
under trendline options and scroll down until you see “display
equation on chart”
By checking the box, your equation will appear on your graph
 
S
TEP
 7 (
OPTIONAL
)
 
To lower your x-axis position (it looks bad here), double-
click on your y-axis values 
 a righthand window (format
axis) will pop up 
 click on the chart symbol 
 type in y-
value value for where you want your x-axis to cross
 
U
SING
 
YOUR
 
TRENDLINE
 
EQUATION
 
You can use your trendline equation to calculate rates of
change
Your trendline equation may look something like this: 
y =
2.5822x – 5175.6
or in general form, as y = m*x + c
where m is the number for the gradient or slope of the line,
and c is the intercept, the y-value when x = 0.
In our example, the x-values represent time and the y-values
represent the change in mean sea level (in mm)
 
U
SING
 
YOUR
 
TRENDLINE
 
EQUATION
 
The slope of the line is 
2.5822
. What does this number mean?
 
The slope of a line is the change in y produced by a 1 unit
increase in x.
 
In our case, the equation is treating a 1 unit increase in x as 1
year.
 
Our equation tells us that the during the period 1993–2003
the average change in sea level every year is plus 2.5822 mm.
 
U
SING
 
YOUR
 
TRENDLINE
 
EQUATION
 
So, if we wanted to calculate the average change in sea level after 5
years, then we would figure: 2.58*5yrs = 12.91mm
 
If we wanted to calculate the average change in sea level after 20
years, we would figure: 2.58*20yrs = 51.64mm.
 
After 10 weeks, then we would figure: 2.58*.1923 = 0.496mm.
 
Now you can predict the change in sea level through any time
period!
 
Note: the example here is based on the time period 1993–2003.
The slope will vary depending on which time period is being
considered.
 
Slide Note

Excel interface will differ depending on the version of the software that is used.

Embed
Share

Learn how to find the equation of a trendline in Excel and use it to calculate rates of change. This step-by-step guide includes importing data, adding a trendline, displaying the equation, and interpreting it for analysis. Make the most of Excel's features for data analysis.


Uploaded on Sep 24, 2024 | 0 Views


Download Presentation

Please find below an Image/Link to download the presentation.

The content on the website is provided AS IS for your information and personal use only. It may not be sold, licensed, or shared on other websites without obtaining consent from the author. Download presentation by click this link. If you encounter any issues during the download, it is possible that the publisher has removed the file from their server.

E N D

Presentation Transcript


  1. HOW TO FIND THE EQUATION OF A TRENDLINE IN EXCEL, AND USE THAT EQUATION TO CALCULATE RATES OF CHANGE DEVELOPED ON 2016 WINDOWS EXCEL (DETAILS MAY BE SLIGHTLY DIFFERENT ON OTHER VERSIONS) This work is supported by the National Science Foundation s Directorate for Education and Human Resources (TUES-1245025, IUSE- 1612248, IUSE-1725347). Questions, contact education-AT-unavco.org

  2. STEP 1 Import your data into excel Place your x- values in the left hand row, and your y-values in the right hand row

  3. STEP 2 To plot your data, go insert charts line graph (or whichever style of graph you are looking for) Your graph should then appear

  4. STEP 3 To add a trendline, go design Add chart element scroll down the list and hover over trendline click on linear trendline

  5. STEP 4 Your trendline may be hard to see (shown in dotted blue line here) Double-click on your trendline A right hand window will pop up (format trendline window)

  6. STEP 5 On the format trendline window, click the paint bucket and change the appearance of your trendline

  7. STEP 6 To display the equation of the line, click on the chart symbol under trendline options and scroll down until you see display equation on chart By checking the box, your equation will appear on your graph

  8. STEP 7 (OPTIONAL) To lower your x-axis position (it looks bad here), double- click on your y-axis values a righthand window (format axis) will pop up click on the chart symbol type in y- value value for where you want your x-axis to cross

  9. USING YOUR TRENDLINE EQUATION You can use your trendline equation to calculate rates of change Your trendline equation may look something like this: y = 2.5822x 5175.6 or in general form, as y = m*x + c where m is the number for the gradient or slope of the line, and c is the intercept, the y-value when x = 0. In our example, the x-values represent time and the y-values represent the change in mean sea level (in mm)

  10. USING YOUR TRENDLINE EQUATION The slope of the line is 2.5822. What does this number mean? The slope of a line is the change in y produced by a 1 unit increase in x. In our case, the equation is treating a 1 unit increase in x as 1 year. Our equation tells us that the during the period 1993 2003 the average change in sea level every year is plus 2.5822 mm.

  11. USING YOUR TRENDLINE EQUATION So, if we wanted to calculate the average change in sea level after 5 years, then we would figure: 2.58*5yrs = 12.91mm If we wanted to calculate the average change in sea level after 20 years, we would figure: 2.58*20yrs = 51.64mm. After 10 weeks, then we would figure: 2.58*.1923 = 0.496mm. Now you can predict the change in sea level through any time period! Note: the example here is based on the time period 1993 2003. The slope will vary depending on which time period is being considered.

Related


More Related Content

giItT1WQy@!-/#giItT1WQy@!-/#giItT1WQy@!-/#giItT1WQy@!-/#