LE Title: Calculating & Graphing Using a Spreadsheet

Author: Barry Smith

Grade Level: 11 – 12

Subject Area: Science (Physics)

School Address: Poland Central School, 74 Cold Brook Street, Poland, NY 13431.

School Phone/Fax: (315) 826-7900/7516

Title of Learning Experience: Calculating & Graphing Using a Spreadsheet

1. LEARNING CONTENT – describe the purpose of the learning experience, including:

a. The learning standards and the specific performance indicators from the standards documents on which the learning experience is based.

MST Standard 1- Students will use mathematical analysis, scientific inquiry, and engineering design, as appropriate to pose questions, seek answers, and develop solutions.

1. Elaborate on basic scientific and personal explanations of natural phenomena, and develop extended visual models and mathematical formulations to represent their thinking.
2. Work toward reconciling competing explanations; clarifying points of agreement and disagreement.
3. Use various means of representing and organizing observations and insightfully interpret the organized data.

2. A description of the connection to instruction in other curricular areas or where this experience fits in the school or course curriculum.

This experience involves mathematics in graphing and slope calculations as well as in solving algebraic equations. It fits the Physics course curriculum since it studies velocity and acceleration. It also connects to the technology curriculum in its use of computers and spreadsheet software to process data and produce output.

• What students need to know and/or be able to do to succeed with this learning experience.

Students need to have some experience with graphing. They should know the basics of velocity and acceleration including the use of formulas. (This material should be taught in class before starting this experience.) It would also be helpful, but not necessary, if the students have some experience with computers including spreadsheets.

1. Procedure

1. What the students do. (Student copy of lab is included)

The first day student groups of 2 or 3 will use the lab apparatus to collect data. Since each trial will need 5 timers the students will also help other groups collect their data.

Once all the groups have their data the groups need to create a spreadsheet (see student sample work) to calculate the average velocity and average acceleration at several points. They will then use the spreadsheets to plot distance vs. time, velocity vs. time, and acceleration vs. time graphs. They will have to use and analyze the graphs to answer questions. They will also need to make copies of their data to give to the other lab groups. The second day the students will add the other group’s data and produce graphs that include all the group’s data. (See sample work) They will then have to answer questions on that graph and summarize their findings.

• What the teacher does.

1. Prior to the experience the teacher should do their regular lesson on velocity and acceleration. They need to make sure they include the definition of instantaneous velocity and an example of how to calculate it from a graph. (Draw a tangent line at the point and calculate the slope of the tangent line using .
2. Prior to the experience set up the apparatus. Attach 6 m of "Hot Wheel’s" track using a "C" clamp high enough so the car has enough momentum to make it to the end. A start line should be drawn near the "C" clamp and distances of 1, 2, 3, 4 and 5 meters should be marked. (A 25’ garden hose with windows cut out so a steel sphere can be seen rolling through it will work as well.)
3. At the start of the experience do the following initiating activity. Ask the students to decide if a ball traveling a curved path (CD) or a ball traveling a straight path (AB) will reach the end first. (The horizontal distance covered by each ball is the same.) Also have the students give a reason for their choice.
4. Next the teacher demonstrates how to make a simple spreadsheet using a computer that is attached to a TV monitor or a video projector. Make sure commands such as fill down and fill series are shown as well as how to put in formulas.
5. Observe the students as they collect data and work on the computers. Try to get all students to take an active part in the experience, and try to keep any one member of the group from doing the work without explaining what is being done. Help groups that are having trouble.
6. After all groups have turned in their labs, discuss the results. Showing the graphs on a TV monitor or projecting them on a screen makes it easier to talk about.

1. Instructional/Environmental Modifications – describe the procedures used to accommodate the range of abilities in the classroom.

1. Instructional modifications made.

Try to form pairs or groups that include a mix of strong and weak students. Also try to pair students with computer experience with partners that have little computer experience.

• Physical modifications of the classroom setting.

If the room does not allow for 6m of track the experience can be done in a hallway on a portable desk. If the room does not have 5 computers with spreadsheet software arrange for visiting the library or computer lab so all the students will be near each other. This should make it easier to observe and help the students.

1. Materials and Supplies – identify the materials, supplies, and equipment needed to successfully complete this experience.

1. For the student

The students need no materials except for pen or pencil. The teacher will supply the lab and "How to Graph Using Excel" information sheet.

• For the Teacher

The teacher needs to have the following materials ready before the experience begins.

1. "How to Graph Using Excel" information sheets. (One for each student, they will need them throughout the course.
2. Student copies of the lab.
3. 6 m of track with a start line and markings at 1, 2, 3, 4 and 5 m. (Hot Wheels or garden hose)
4. Car or steel sphere
5. 5 stop watches
6. Meterstick
7. Computer for each group with Microsoft Excel
8. Computer with TV monitor Hookup or Video Projector recommended but not required.

1. Assessment Tools/Techniques

1. Techniques used to collect evidence of student progress toward meeting the learning standards (e.g., observation, group discussion, higher analytical questioning.)

Since the teacher has no other duty at the time it offers a good opportunity to observe the students working together during the collection of data and while working on the computers.

• Tools used to document student progress (e.g., scoring guides, rating scales, checklist, projects, taped performances).

Each member will get a grade from a rubric based on:

1. How well the students worked together and shared the work. (20% individual)
2. Quality and completeness of the graphs. (40% group)
3. Quality and completeness of the lab questions and calculations. (40% individual) They may work together but each must member must turn in a lab.

1. Time Required – For each aspect of the learning experience, state the amount of time for:

1. Planning

The time required for planning depends on how much of the attached materials are used. If the lab is used as is and if the computers use Microsoft Excel all the preparation necessary is setting up the apparatus. Setting up and marking the track should take about 10 minutes. (The initial cutting and marking of the hose should take about 30 minutes.)

• Implementing

The lab should be completed, turned in, and discussed in about 1½ , 80 minute periods or 3 40 minute periods.

• Assessment

Observing the students can be done during class time. Grading should take approximately five minutes per lab group.

Student Work/Handouts – samples of student work.

1. Blank student lab
2. "How to Graph Using Excel" information sheet.
3. Sample completed lab including graphs. (One lab group)
4. Sample of graphs containing all groups. (Put on one page to conserve space)

Name ___________________________ Date __________

Lab Period __________ Group Number ________

Title: Calculating and Graphing Using A Spreadsheet

Objectives: To test the hypothesis the student formulated in the initiating activity.

To learn how to calculate and graph using a spreadsheet. (Microsoft Excel)

Equipment:

6 meters of marked track 1 car or steel sphere

1 meterstick Computer with spreadsheet and printer

5 stopwatches

Initiating Activity Hypothesis:

Procedure: First day

Part A

1. One group member holds the track so that it touches the ground the designated distance from the base of the lab table. (Group 1 – 1 m, group 2 – 2 m, group 3 – 3 m, and

group 4 – 4m.)

• Organize five timers (some will be from other groups) to time how long it takes the car to get to the 1m, 2m, 3m, 4m, & 5m marks. Have them hold the track to keep it straight.
• Place the car (steel sphere) at the start line and release once the timers are ready.
• Record the times in the table in the data section.
• Help other groups collect their data before starting part B.

Part B

1. Set up a spreadsheet to complete the information requested in the table in the data section.
2. Plot graphs for distance vs. time, velocity vs. time, and acceleration vs. time.

3. Save Your Work to a floppy disk.

4. Make copies of your completed spreadsheet (graphs are needed) and distribute them to the teacher and other lab groups.

Data Section:

Questions: Answer the following based on your data only. Be sure to use complete sentences and answer fully. For all calculations you must start with a formula, plug in numbers with units, and end with an answer with units.

1. What does the slope of a distance versus time graph physically represent? (Hint: check the units.)

• Calculate the instantaneous velocity at time=1.5 seconds. What would a negative sign indicate?

• What is the difference between average and instantaneous velocity? Under what condition would they be the same?

• What does the slope of a velocity versus time graph physically represent?

• What is the instantaneous acceleration at t=1.50 seconds? What does the sign (+ or -) mean?

• What does the slope of an acceleration versus time graph physically represent?

Procedure: Day 2

1. On your group’s spreadsheet add the other group’s data and "fill down" the rest of the columns.
2. Plot velocity vs. time and acceleration vs. time graphs. Put each group’s data on as a separate series and label them group 1 – 2m, group 2 – 4m, etc..

Questions: (Based on all groups’ data)

1. According to the graphs was your hypothesis correct? Explain the results?

Graphing Using Microsoft Excel

• Enter Microsoft Excel
• Type all data for the x – axis in the A – column
• Type all data for the y – axis in the B – column
• Go to "Insert" and click on "Chart" (or click on the multicolored chart icon)
• Choose "XY scatter"
• Click on "next" twice
• Fill in the missing information. (Title, x-axis and y axis labels including units)
• Click "next"
• Click on "As new sheet" and then click on "Finish" to enlarge graph.
• Click on any data point and then right- click it.
• To put on your line.

1. Best – Fit line Right click anywhere on the graph and Click "Add trend line"
2. To connect the points – right click on any data point, Choose "Format Data Series". Under the heading "Line" click "Automatic" and OK

*. If you want to change anything, click anywhere on the graph with the right button and choose "Chart Options"

** If you need to change either axis, click on that axis with the right button.