Excel Scatter Plot and Trendline

Excel Scatter Plot and Trendline

https://softtechtutorials.com/microsoft-office/excel/excel-scatter-plot/

0:00 Intro 0:32 Inserting Scatter Plot 0:53 Appearance 1:43 Excel Scatter Plot Trendline 3:13 Forecast 4:22 R-squared

Hello, In this tutorial, I will show how you can insert a scatter plot or dot plot in Excel. A scatter plot is used to show the relationship between two variables and highlights the degree of correlation.

To insert the scatter plot, you select the entire table including the titles, navigate to Insert then the Charts section where we select the “Insert Scatter or Bubble Chart”.

So, the chart appears on the screen. Each combination of a distance and the corresponding amount paid is visualized as a dot. Now that we have our plot, we will add axis titles and change the plot’s title. To change the title, you click on it, select the text, and type whatever you like. Let’s take Scatterplot of London taxi rides.

Using this chart we are able to verify if a certain relationship is present for our data. Here, you can recognize a linear relationship, each change in the distance brings about the same change in Price.

To help us with this, we can add a trendline to the plot. To do so, you click on the chart, navigate to Add Chart Element under the Chart Design tab. Click, navigate to trendline, and select Linear because we recognized a linear relationship in the data.

In the chart you see a straight line appear. This line is calculated using the least squares method. This method finds the best fit for a data set by minimizing the sum of the offsets of points from the plotted curve. The side panel appears in which you can format the trendline. In this panel you can indicate the number of periods forward you would like to make the forecast for.

Now you see that the Excel scatter plot trendline is extended to 20.

To be sure that we read the figure correctly we can add the trendline’s formula and compute the exact price value for a distance of 20 miles. To add the formula we tick the “Display equation on chart” box in the side panel.

Here you type equals TREND of the known y’s which are the price values, then we enter the known x’s which are the distances and finally we enter the new x which is 20. So, the same value appears.

Finally, let’s add the R squared value. We add this value by clicking on the trendline, then the “Format Trendline” side panel opens where you can highlight the “Display R-squared value on chart” checkbox.

This concludes our tutorial on Excel Scatter Plot and trendline. I'm inspired by content creators as Leila Gharani and Teacher's Tech.

#Excel #Tutorials #Statistics ... https://www.youtube.com/watch?v=KDRy_mEX0kA