Overview
This lecture explains how to create a scatter plot in Excel to analyze density by plotting mass versus volume, adding proper labels, inserting a trend (best fit) line, and calculating the slope and intercept both graphically and with formulas.
Setting Up the Data
- Enter volume values in one column and mass values in another in Excel.
- Highlight both columns to select your data for plotting.
Creating the Graph
- Go to Insert and choose a Scatter Plot to display the data points.
- Check that volume is on the x-axis and mass is on the y-axis.
Labeling and Formatting the Graph
- Add axis titles (e.g., "Volume" for x-axis, "Mass (g)" for y-axis).
- Change the chart title to something descriptive, like "Mass vs. Volume for an Unknown Liquid."
- Remove gridlines for a cleaner appearance if preferred.
Adding and Interpreting the Trend Line
- Use the Chart Elements button to add a linear trend line to the scatter plot.
- Right-click the trend line and select "Format Trendline."
- Display the equation of the trend line on the chart to show the linear relationship.
- The equation is in the form y = mx + b, where m is the slope (density).
Calculating Slope and Intercept Using Formulas
- Use the formula
=SLOPE(known_y's, known_x's) in Excel to get the slope without a graph.
- Use
=INTERCEPT(known_y's, known_x's) to calculate the y-intercept value.
- Both calculated values should match those in the chart equation.
Key Terms & Definitions
- Scatter Plot — A graph that displays individual data points for two variables.
- Trend Line (Best Fit Line) — A straight line that best represents the data on a scatter plot.
- Slope (m) — The change in y per unit change in x; represents density when graphing mass vs. volume.
- Intercept (b) — The point where the line crosses the y-axis in the equation y = mx + b.
Action Items / Next Steps
- Prepare your own mass and volume data.
- Create a properly labeled scatter plot in Excel.
- Add and interpret the trend line equation.
- Practice using SLOPE and INTERCEPT formulas in Excel for analysis.