Topic 13 of 15, EHL Maths & Excel final (AP)
⋰
Scatterplot and trendline
A scatterplot shows the relationship between two numerical variables. With a linear trendline and its equation on the chart you can describe the relationship and make a prediction.
When the exam asks for it
- “relationship between …”, “scatter”, “trendline”, “equation of the line”, “predict”
Formulas
Trendline equation shown on the chart
y = m*x + cPrediction
put the x value into the equationWorked example
D6fx
Trendline: y = 2.5x + 40| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Advertising (k CHF) = X | Bookings = Y | |||
| 2 | 2 | 45 | Equation on chart | ||
| 3 | 4 | 50 | y = 2.5x + 40 | ||
| 4 | 6 | 55 | |||
| 5 | 8 | 60 | |||
| 6 |
Sheet1
⬇ Download practice ExcelExample, Practice and Answers sheets with working formulas. Free.
Step by step
- Put the X variable in the first column and the Y variable in the second columnA = advertising, B = bookings
- Select both columns, including their valuesA2:B5
- Go to Insert → Scatter Plotthe points appear
- Click the points on the chartall points are selected
- Choose Add Trendlineright-click menu or the + button
- Select Lineara straight line through the points
- Tick Display Equation on charty = 2.5x + 40 appears on the chart
- Predict for x = 102.5*10 + 40 = 65 bookings
⚠ Exam traps
- Put X (the cause) in the first column, otherwise Excel swaps the axes and the equation is wrong.
- Choose a Scatter chart, not a Line chart: a line chart treats X as labels.
- Choose Linear in the trendline options, not Exponential or Moving average.
Practice question
The trendline is y = 2.5x + 40. How many bookings for 12k CHF of advertising?
Show answer
✓ 2.5*12 + 40 = 70 bookings.
Stuck on this topic before your exam? We explain it one-to-one on your own Excel files, live online. Short crash courses before the EHL Maths & Excel final are possible on weekdays and weekends.
Book a lesson
Tell us your course, your exam date and the topics that worry you. We start with what matters most for your grade.
WhatsApp is preferred
The fastest way to book: send us a message and we usually answer the same day.
or email info@ytutor.net
Send a message
We answer by email or WhatsApp.