Imagine that you are investigating the relationship between the size of a treat and the rate at which a dog wags its tail. You can collect data for a series of trials where a dog is shown a treat of a given size and you measure the rate at which it wags its tail. In this situation, the experimental factor (treat size) varies continuously rather than in discrete categories.

To examine the effect that the experimental factor has on the response variable (the wag rate), we can plot each trial as a point on a type of graph called an X-Y scatter plot. To set up a scatter plot in Excel, enter the pairs of data in two columns with each value of a pair on the same row. By default, Excel considers the column on the left to contain the horizontal (X) values and the column on the right to contain the vertical (Y) values.

Select the block of cells to be included in the scatter plot by clicking and dragging, then from the Insert ribbon under Chart drop down the Scatter or Bubble menu and select Scatter. A chart will appear on the spreadsheet. If you click on the + sign at the upper right of the chart, a list of checkboxes will appear. Check Axes, Axis Titles, and Trendline.

If the plot is to go thru the origin, check the 'Set Intercept' box, and enter 0 in the box. To show the equation of the line (y=mx +b), check the 'Show Equation' box. To change the scale to make the plot take up most of the space, right click on a gridline and select 'format gridline'. Mar 13, 2018 - Essentially, the slope describes how much the 'y' variable (on the. Into an Excel spreadsheet, the program can produce a scatter plot graph. The equation will be in the form of 'y = mx + b' where m and b will be numbers.

Uncheck everything else. You should edit the Axis Titles to include the name of the factor and any units associated with it. App Double-click on the Axis numbers to bring up the Format Axis dialog, then click on the bar-graph icon to access Axis Options. Set the bounds and units appropriately and set the tick marks to something sensible, like this. In the example above, we had Excel calculate and plot a linear trendline through the points.

You should notice that the trendline is the best line that fits through the points. It may or may not actually pass through any particular points. That's why another name for trendline is best-fit line. In scientific graphs, one almost never 'connects the dots'.

(There is another chart type that Excel makes which does that - don't use it!) The name of the process used to create the best-fit line is called linear regression. When we fit the best line through the points of a scatter plot, we usually have one of two goals in mind. One important use of linear regression is predictive. In the example, we might like to predict how fast the tail will be wagged given a treat which is a size that we didn't specifically measure. If we know the equation of the best-fit line we can plug numbers into it to calculate the predicted value.

The other important use of linear regression is as a statistical test of significance. In that case, we simply want to know if changing the size of the treat has a significant effect on the rate of tail wagging.

We may not actually care about describing the way that the factors vary (the equation); we may just want to know if they vary significantly or not. In that case, what we want to know is whether the slope of the best fit line is different from zero or not. If the tail wagging rate were completely random and not affected by the size of the treat, the best-fit line would be horizontal, showing that the average wag rate was the same regardless of the treat size. A linear regression can facilitate both of these uses. In the example to demonstrate how to create a scatter plot in Excel, we saw that the best-fit line through the treat size/tail-wagging rate data had a positive slope.

However, one could make the case that tail-waging rate is unrelated to treat size and that it was simply a coincidence that the points on the right side of the graph were higher than those on the left side. Because there were only five data points, this would be a fairly likely outcome. We can frame this situation using the same language of Section 5.2. In a regression, our null hypothesis is that the slope of the best fit line is zero. When we conduct the regression statistical test, the software will calculate a value of P. Recall the general interpretation of P: if the null hypothesis were true, P is the probability that deviations as great as or greater than those seen in the sample would occur due to chance unrepresentative sampling.

In this particular test, P states the probability that a slope as great or greater than that of the best fit line would occur due to chance deviations from an actual slope of zero. If the deviations from a slope of zero are great enough for the value of P to fall below 0.05, then we say that the best fit line differs significantly from zero. In this case, we additionally state that the factor graphed on the horizontal axis has a significant effect on the factor graphed on the Y axis. In a simple one-factor experiment such as the blood pressure study example of Sections 4.2 through 5.3, we assume that the presence of the factor has a fixed effect on the size of the measured quantity in the trials. Free unlimited vpn for mac torrent. That effect manifests itself by creating a difference in the mean value of the measured quantity.