Graphing Circumference & Diameter in Excel 2007 - Vista

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?
4. How accurate are your results? How do you know this?

This graph compares the distance perceived by a LEGO robot with the actual distance as measured by a tape measure.

graph here

Use the above graph to answer questions 5-8:

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

6. Is the data accurate? How can you tell?

7. If the actual measured distance is 120.0 cm, what distance will the robot most likely perceive? Is this an interpolation or an extrapolation? How 'trustworthy' do you think this prediction is?

8. If the actual measured distance is 43.58 cm, what distance will the robot most likely perceive? Is this an interpolation or an extrapolation? How 'trustworthy' do you think this prediction is?