Question

How can I use the specific trendlines for a scatterplot in Excel?

Answer 1

You use a trend line to make predictions.

Explanation:

A scatter (`xy`) plot has points that show the relationship between two sets of data.

Assume that you own the local ice cream shop.

You keep track of how much ice cream you sell versus the noon temperature on each day and create a scatter plot with a trend line.

You can use the trend line to predict what your sales might be at a given temperature.

Interpolation

Interpolation is the estimation of a value within a sequence of values.

You can use interpolation to estimate your sales at 21 °C.

At 21 °C you should expect your sales to be about $470.

Extrapolation

Extrapolation is the estimation of a value based on extending a trend beyond an area of known values.

If you extend your trend line, you can use extrapolation to estimate your sales at 28 °C.

You should expect sales of about $680 if the temperature reaches 28 °C.

Using a Formula

You could also create a formula of the type `y=mx+b` to help you (Excel can create this formula for you automatically).

Let's estimate two points on the line near actual values: (12 °C, $200) and (25 °C, $590).

`"slope" = (y_2-y_1)/(x_2-x_1) = ($590 -$200)/"25 °C - 12 °C" = "$390"/"13 °C" = "$30/°C"`

Now put the slope and the point (`"25 °C, $590"`) into the "point-slope" formula:

`y = mx +b`

`b=y-mx = $590 - $30/(color(red)(cancel(color(black)("1 °C")))) × color(red)(cancel(color(black)("25°C")`

`b = $590-$750 = -$160`

Omitting the dollar signs,

`y = 30x-160`

Now you can use this equation to interpolate a sales value at 21 °C:

`y = 30×21 – 160 = $470`

And you can extrapolate a sales value at 28 °C:

`y = 30×28 -160 = $680`