Activity 4

Using Spreadsheets in the Laboratory

Graphing

Title of Graph

Best Fit line� Equation of line� y = mx + b

R²

​

​

Independent variable (x) with units

Dependent Variable (y) � with units

Spreadsheet - Inputting Data

Table 1: Data Entry of known values

Table appearance, data labels and units,

significant figures

​

Create the x-y scatter plot of the above data and add the trendline, the equation of the trendline and the R² correlation coefficient on the graph. Label the axes and the graph.

Graph Set up

Click on the "Insert" menu.  Select “Chart…”

The Chart Editor will open. Select the scatter plot option (see below). Insert the graph.

To change the appearance, add titles and axis, click in the Customization tab that can be seen in the Chart editor above. Use the options to put in the titles, scale the axis and add the “best-fit” line to the graph with the equation and correlation coefficient.

The Trendline or “Best Fit Line” (y = mx + b)

The trendline is added using the Chart Editor under customization. Edit the chart, click on Customize, Series and select the Trendline.

​

Once Trendline is selected, additional options will open. Set the label to be “Use Equation” and select Show R²

Graphing data, mass is dependent variable (y)

Graph 1: Mass versus Volume

Required Elements

Chart title�X and y Axis title with units�Trendline�Equation of trendline�Correlation coefficient

A graph that contains the origin can have the axis modified to spread out the data points.

Intercept

When adding a trendline in Excel, the intercept can be set to any value. If the concentration of the solution is zero, we would expect the absorption to be zero. Create a second graph with the data above, but set the trendline to have an intercept of zero so that the line passes through the origin (0,0). You can copy and paste Graph 1 and then modify the options.

In Google, create the second graph by adding the data point 0,0 to the graphed values. Note this changes the graph slightly but does not set the y-intercept equal to zero. You can use a function, to determine the equation of the line with an intercept of zero using the LINEST function.

Interpolation and extrapolation

Make a copy of the graph above and extrapolate (extend) the trendline to show the x-intercept value at volume = 0 (y=0).

In Excel, use the Forecast function in the graph format menus.

In Google to extend the line, adjust the x axis minimum and maximum values.