Graphing Circumference & Diameter in Excel

Graphs can often give more information, faster, than just a collection of numbers.  Use these instructions during the school year to help you make graphs as needed in your science class.

 

1)     Open up the Microsoft Excel program.  A new, blank worksheet should appear.

2)     Click on the “Home” ribbon.

3)     Copy your diameter & circumference data into this blank worksheet.

a)     Diameter (independent variable) into column A

b)     Circumference (dependent variable) into column B

c)     NO units in these cells; just numbers, or you’ll confuse the program!

data entry.JPG

4)     Insert a row at the top for headers

a)     Select Row 1 (highlighted)

b)     Click “Insert;” an empty row should appear, a “new” Row 1.

c)     Label Column A as Diameter (cm) and Column B as Circumference (cm).

5)     Choosing a graph type

a)     Choose the “Insert” ribbon.

b)     Select “Scatter

c)     Choose the top-left option, “Scatter with only Markers

d)     A graph should appear in your spreadsheet which looks something like this:

 

6)     Adding a best-fit straight line

(a) Right-click on a data point and choose the option, "Add Trendline."

(b) Choose a linear fit.

(c) DO NOT SKIP THIS STEP!!! Check the last two boxes at the bottom to display the equation and the R2 value.

About the equation of the best-fit straight line and the R2 value . . .

a)     The best-fit line “belongs” to the equation you’ll see.  It is given in slope-intercept form, y = mx + b.  The values for the slope and intercept of the best-fit straight line can give you valuable information about the relationship between the independent variable (x-axis) and the dependent variable (y-axis).

b)     The R2 value is a number somewhere between -1 and 1.  It describes, on average, how close the points are to the best-fit line and thus reflects the precision of your data.  As this number gets closer to -1 or to 1 (away from 0), the points get closer to the best-fit straight line.

 

Your chart should now look something like this:

equation and r^2 result.JPG

(we got rid of the legend at the right by clicking on it and deleting – it just took up room)

7)     Finishing touches

a)     Labeling the x-axis: in the Layout tab, in the Labels group, select Axis Titles.

labeling axes.JPG

i)      Choose Primary Horizontal Axis Title . . .

ii)     Select Title Below Axis.

iii)    A text box will appear below the x-axis with the text Axis Title.  Replace those words with Diameter (cm). [The label for the independent variable always goes on the x-axis.]

b)     Labeling the y-axis: in the Layout tab, in the Labels group, select Axis Titles

i)      Choose Primary Vertical Axis Title . . .

ii)     Choose Rotated Title

iii)    A text box will appear to the left of the y-axis with the text Axis Title.  Replace those words with Circumference (cm). [The label for the dependent variable always goes on the y-axis.]

c)     Replace the title at the top of the graph by clicking on it and entering the title, Circumference v Diameter.  Here’s what your graph should look like, approximately –

 

d)     Place your name in a cell in the spreadsheet near your data.  Place your class period in the cell immediately below your name.

8)     Turning in your work

a)    Answer the ANALYSIS QUESTIONS below in the space below your data.

b) Click on any cell in your spreadsheet – but NOT on the chart.

c)     Print out your spreadsheet by going to the orb, selecting Print, then Print (again). 

d)     Select the appropriate printer.

e)     Choose Preview to make sure your work fits on one page.  If it doesn’t, choose Page Setup and choose the Landscape option.

f)     Now you can print and turn the assignment in to your teacher as instructed.

 

 

ANALYSIS QUESTIONS:


1. What is the value of the slope of your line? How do you know this?


2. How precise are your results? How do you know this?


3. What physical quantity is represented by the slope of the line? (An interesting, related article here.)


4. How accurate are your results? How do you know this?


This graph shows the relationship between the size of a house (in square feet) and its price (in thousands of dollars). Use this graph to answer questions 5-8.


incmhon4

 


 

 

 

 

 

 

Data from “Statistics for Managers Using Microsoft Excel,” 5e © 2008 Prentice-Hall, Inc.

 

5. Is the data precise? How can you tell?

6. If the house has 1293 square feet . . .

A. Calculate its price. (Show your work!)

B. Is this an interpolation or an extrapolation, and how can you tell?

C. How 'trustworthy' do you think this prediction is, and why?

 

7. If the house has 3600 square feet . . .

A.  Calculate its price. (Show your work!)

B. Is this an interpolation or an extrapolation?

C. How 'trustworthy' do you think this prediction is, and why?

 

8. Adding 500.0 square feet to your house will increase its price by _______.  How do you know this? (Show your work!)

9. A house that costs $600,000 would have approximately ________ square feet. SHOW YOUR WORK!