How to make a scatterplot and obtain treddline

Draft

to do:

  • update instructions
  • add graphs

Here’s a short video stepping you through creating a trend line from a scatterplot in Google Sheets. The video also shows how to log-10-transform the X variable.

Steps to make the plot and obtain trend line

Example data were reported How to obtain slope of the regression1

  1. Take the time to arrange your data columns so that X (e.g., MYA or logMYA) and Y (e.g., genetic Distance) columns next to each other, the column with X variable left of the column with Y variable.
  2. Highlight the X and Y columns (click and hold on the header for the X column then move to the Y column)
  3. From menu bar select Insert → Chart
  4. Select data series by clicking on any data point in the chart
  5. Check Trendline from the Chart Edit menu at right of your screen
  6. Label, select Use equation
  7. Check Show R2

 

Next, log10-transform the X variable (e.g., logMYA)

  1. Create a new column, label logMYA. For this example, let’s go with column G. I assume, again for this example, that the raw MYA are in column C and the Distance values are in column D.
  2. In the first open cell, G2, type the equals sign and the function call =log10(c2), then hit enter. The cell will be updated with the log-base-10 of the value in C2
  3. Complete the transform for the rest of the values. An easy way to do this is to double-click on the “+” that appears when you hover the mouse over the lower-right part of the cell.
  4. Now, copy and paste all values from column D to column H.
  5. Make the new plot and obtain slope and R2 from the new trendline by repeating steps 3 – 8