Science Learning Activity

Science Learning Activity By Megan McGolrick, Andriea Neto, and Beverly Barnes Title: Using Excel to Analyze Data Curriculum: Middle School Science- Science Research Grade-Level Span: 8th grade PURPOSE (Objectives): Students in 8th grade science will: 1. Be able to follow a procedure. 2. Be able to collect and record raw data with precise measurement. 3. Be able to use Microsoft Excel to input data, convert units from English (inches) to Metric (cm) and calculate averages and uncertainty (error). 4. Be able to use Microsoft Excel to create a bar and line graph. DESCRIPTION: This learning activity will take 4-5 50-minute class periods in the science classroom and computer lab. Students will be paired with a partner. Each student will need a computer. One will be used to view a Power Point titled “Creating a Spreadsheet” and the other will be used to complete the spreadsheet on Microsoft Excel. (If fewer computers are available, then students will do both activities on one computer.) Students need access to Microsoft Excel and Power Point. Day One Procedure: 1. The teacher should model skills needed in data collection including: marking a starting point with tape on the floor, how to perform a standing jump, using the stop watch, how to run in place safely, and using a yard stick and how to measure to the tenths place. 2. Assign students into pairs. 3. Give students 20 minutes to collect data. 4. Students should complete 10 trials of a standing jump. 5. Students should then run in place in place for 2 minutes and then complete 10 trials of a standing jump. 6. All information should be recorded in inches to the nearest tenth. Days Two and Three Procedures: 1. Students should be reminded of computer lab procedures including: walking to and from the computer lab, how to enter and exit the lab, seating arrangements, logging in, and saving work. 2. Students need to bring their data from the previous day. 3. Students will be working in pairs using 2 computers. 4. Students should open up a new Excel document (or the template for modification) on one of the computers and save it as their own file. If only one computer is used, there is a link to a blank spreadsheet on the PowerPoint. The blank spreadsheet will update as the student enters data into the spreadsheet. 5. Students should open up the PowerPoint presentation on the other computer and follow the directions to record and analyze their data. 6. Project the PowerPoint to assist students. Microsoft Excel Directions (Power Point Directions) 1. Open the Excel template provided. 2. Save it as your own .xls file. 3. Go to view-header and type your name. 4. Enter data for the trials in inches. 5. If anything pops up to ask if you are managing a list SAY NO!

6. Use a formula to convert data from inches to cm (multiply inches times 2.5) and then use editfill down for the other four cells. 7. Highlight all four columns and then click format-cells (choose number and 2 decimal places.) 8. Click insert-function to calculate an average for the two columns with centimeters only. 9. Use a formula to calculate uncertainty (error) highest minus lowest divided by two for the two columns marked “centimeters” only. 10. Re-type the averages for each data set into a new worksheet. Name the worksheet “Averages” (or use the template worksheet called averages). 11. Create a line graph of all trials- open it in a new worksheet and call the worksheet “Graph 1” (use apple key to highlight cm columns only). Be sure to add titles for axes and an overall title for the graph. 12. Create a bar graph of averages with labels- save in a new worksheet and call the worksheet “Graph 2”. To name the series go to chart-source data then click series. You can rename the series (the blue line). Day Four Procedure: 1. Distribute Data Analysis Rubric to students and read through the meeting expectations column. Take questions as needed. 2. Students should use their data in the Excel worksheet to calculate a range (take the average minus the uncertainty for the bottom number of the range, and the average plus the uncertainty for the top number of the range). Clarify that this is a BEST VALUE range, or a range of the average with error (uncertainty) factored in, and not the other “range” that is used in math. Ex: if the average is 10.5 and the uncertainty is 0.25, the range of the average would be 10.25-10.75). 3. Students should compare the two ranges of the averages to see if there is overlap. This can be modeled with colored chalk on a chalkboard. If there is overlap, then it is impossible to draw a conclusion based on your data. Overlap means the data is not statistically significant. No overlap means the data is significant, and you can compare the two averages to each other. 4. Students should write a data analysis in paragraph form that follows the guidelines in the rubric. Students who are struggling should take time to identify the manipulated and responding variables (MV and RV). They should also identify any sources of error when conducting their procedure (poor measurement, one partner leaving for the restroom, moving the tape, etc.) 5. Students should each complete an individual rubric when submitting their work. The data worksheet and two graphs should be stapled to the data analysis. Example of Worksheet with proper labels: (This is also the template that would be used for modification). TYPE YOUR NAME HERE Standing jump before and after exercise Reg Jump (in.) Reg Jump (cm.) Post-run Jump (in.) trial 1 trial 2 trial 3 trial 4 trial 5 trial 6 trial 7 trial 8 trial 9 trial 10 average highest

Post-run Jump (cm.)

lowest H-L uncertainty Example of Completed Worksheet: TYPE YOUR NAME HERE Reg Jump (in.) trial 1 48.00 trial 2 48.50 trial 3 49.00 trial 4 48.00 trial 5 49.50 trial 6 48.50 trial 7 48.00 trial 8 49.50 trial 9 49.50 trial 10 49.00

Standing jump before and after exercise. Reg Jump (cm.) Post-run Jump (in.) Post-run Jump (cm.) 120.00 50.00 125.00 121.25 50.50 126.25 122.50 49.00 122.50 120.00 50.00 125.00 123.75 49.50 123.75 121.25 51.00 127.50 120.00 51.25 128.13 123.75 51.00 127.50 123.75 50.50 126.25 122.50 50.00 125.00

Average Highest Lowest H-L Uncertainty

121.88 123.75 120.00 3.75 1.88

125.69 128.13 122.50 5.63 2.81

Example of Data Analysis in Paragraph Form Will a human’s ability to jump from a standing position be improved by exercising beforehand? This question has been analyzed, researched, and tested in a controlled experiment. Data shows that the average jump with no exercise beforehand averages 40.3 centimeters. With an uncertainty of 0.7 the best value range, or range of the average including error, is 39.6-41 centimeters. Ten trials were conducted in a precise manner and error was minimized throughout each trial by starting at the exact same location for the jump each time. Next, a different subject spent two minutes exercising by running in place. Then, when the muscles were warmed, that subject performed the same jump procedure as the subject with no exercise. The average for the ten trials of this procedure was 41.5 centimeters. There was an uncertainty of 1.2, so the best value range was from 40.3 to 42.7 centimeters. It is interesting to note that the uncertainty was 0.5 centimeters greater for the jump with exercise. Perhaps the subject was more tired towards the end of the trials and had difficulty jumping as far in a consistent way. When the two data ranges are analyzed, there is an overlap in the average ranges. The average ranges overlap from 40.3-41 centimeters. That means that there is an amount of 0.8 centimeters where the data could fall for both testing groups. Due to the overlap in averages with the error factored in, we cannot draw a clear conclusion about the data. Despite taking caution to conduct a controlled experiment, the data is not significant.

ACTIVITIES

Students collect data by completing a standing jump lab. Students follow a Power Point presentation to record and analyze data. Students complete standard set up of an Excel document including opening, saving as .xls file, titling, renaming, titling columns, readjusting column widths, and formatting cells to 2 decimal points. Students will enter data into a spreadsheet. Students will use formulas to convert data from English to metric units. Students will use formulas to calculate averages and uncertainties. Students will create line and bar graphs for all trials. Students will summarize in writing the data collected in an experiment. Students will determine the sources of error that limit the accuracy of an experiment.

CURRICULUM STANDARDS (Science HCPSS and MD VSC) Goal 15g, 16a, 17a. Goal 17a, 15g

NETS PERFORMANC E INDICATORS

1 ,2 Goal 17a Goal 17a Goal 15g Goal 17d Goal 17a Goal 16d

1, 2, 3 1, 2, 3 1, 2, 3 1, 2, 3 1, 2, 3

Goal 17d

Science objectives can be found at http://www.hcpss.org/academics/science. At this site select “Middle School Skills and Processes”. The HCPSS science objectives are adapted from and aligned with the Maryland Voluntary State Curriculum for Science 6-8 so we have not added the VSC objectives separately. NETS Performance Indicators can be found at http://www.iste.org/inhouse/nets/cnets/currstands/cstandsnetss.html.

TOOLS AND RESOURCES: Hardware: A computer for each child. If this is not possible, then at least one computer for each pair of students is needed. LCD projector in computer lab for teacher to model Software: Access to Microsoft Excel Access to Microsoft PowerPoint presentation with directions for the lesson. Access to Microsoft Excel template if modification is being made for students. Other Materials: One yardstick per pair of students Two small pieces of masking tape per pair One stopwatch per pair Pencil and data table to record data per pair

ASESSMENT: Data Analysis Rubric Below Standard Information is fragmented, bulleted, or otherwise not in paragraph form. Information is presented in the first person (using I, me, my) Topic sentence is not present or is incomplete.

Meeting Standard Information is organized in paragraph form, with the first line indented. Information is consistently presented in the third person.

Some data is not discussed.

Data from all experimental groups, as well as control groups (if available) are discussed. All averages/means included for all groups.

Averages/means not included for some or all experimental/control groups. Uncertainties and or/ ranges not included.

Topic sentence includes the MV and RV.

N/A Topic sentence includes the MV and RV, and also refers the reader to tables and graphs. N/A

N/A

Data is not said to be either statistically significant or not statistically significant.

Data has been analyzed based on analyzing ranges for overlap. Data is said to be statistically significant or not statistically significant.

The data is not used to support or refute the hypothesis. There are three or more spelling and/or grammar errors. Units are not included for some values.

The hypothesis is restated and is either refuted or supported.

All uncertainties and ranges are included for all experimental/control groups, and the reader is referred to error bars on a graph. (ex: Graph 2 shows the ranges) In addition, numbers are used to support the claim of s.s. or not s.s. (Example: The acid water is shown to be statistically significant when compared to the basic water, as the ranges (4-5.5 cm and 6-7.7 cm, do not overlap.) In addition, numbers are used to support or refute the hypothesis.

One to two spelling and/or grammar errors.

No spelling and/or grammar errors.

Units are included for all values.

Excel worksheet and graphs have missing or incomplete information.

Excel worksheets contain all trial data, averages, and uncertainty for all experimental/control groups. Excel graphs have a title and labeled axes with units. Both group members worked well together and needed teacher redirection only once.

Units are included for all values. In addition, scientific words are used enhance the quality of this piece. In addition to meeting the standard, the bar graph of averages has error bars (they can be hand-drawn).

The teacher had to redirect one or both group members to stay on task two or more times.

All uncertainties and ranges are included for all experimental/control groups.

Above Standard N/A

Both group members consistently contributed in a positive way and needed no redirection from the teacher.

ADDITIONAL INFORMATION ON SCIENCE RESEARCH: Science Research Project Links! 1. Choosing a topic. http://members.ozemail.com.au/[email protected]/scifun/projects.htm#N42 This website was created by Peter Macinnis and copyrighted in 2001. This page is part of the GEM, the Gateway to Educational Materials, which is sponsored by the US Department of Education. Click on “topic ideas for projects” and scroll down through the list. Choosing a topic is the most challenging part of science research! This list will help you to find a topic you are interested in and that has a real-life connection. 2. Still can’t find a topic? http://www.sciencebuddies.org/science-fair-projects/project_ideas.shtml?From=body This website is maintained by the Kenneth Lafferty Hess Family Charitable Foundation and was copyrighted in 2002-2008. These topics are arranged by category, which is useful because many students have difficulty deciding which category their project fits into. These topics branch out into engineering, and some creative ideas such as photography and digital video. 3. I have a topic. Now what? http://www.sciencebuddies.org/science-fair-projects/project_guide_index.shtml?From=body The most important thing is not to panic! A science research project follows the pattern of the scientific method, which is really just a way of problem solving. This website will lead you through the steps of the scientific method, and will help you to keep your project organized. From identifying variables to creating a presentation of the final results, this website has you covered! This website is also maintained by the Kenneth Lafferty Hess Family Charitable Foundation and was copyrighted in 2002-2008. 4. Finding out more about your topic. http://www.hclibrary.org/training/eresources.php Your favorite library has all of the online resources you need! The Howard County Public Library has a collection of online resources for you to use. Ask your science teacher or school librarian for an access code if you do not have your own library card. Library cards are free! Just go with an adult to your local branch to sign up. This website will be useful during three parts of your project: collecting background research, explaining your results in a conclusion, and citing other scientists to support your conclusion (by using other science journals). 5. The Discovery Channel thinks of everything. http://school.discoveryeducation.com/sciencefaircentral/scifairstudio/askjvc.html Janice VanCleave, who is a science fair expert and author of more than 45 books on science fairs, will answer any question you have about science fairs! Well, perhaps not any question, but the website will search a database of over 300 science fair questions. Just type your question into the box and click for her answer. 6. So you’re going to do a science fair project… http://school.discoveryeducation.com/sciencefaircentral/scifairstudio/handbook/index.html Janice VanCleave and the Discovery Channel will rescue you from the depths of your despair of a research project. Janice wrote a book, and then made most of it available (free!) for students like you online. Be sure to click on Ms. McGolrick’s favorite parts: explaining your results, the sample presentation, and anything with Do’s and Don’ts.

7. Elmer’s glue has another use. http://school.discoveryeducation.com/sciencefaircentral/?pID=fair Discovery School has a site called science fair central- sponsored by Elmer’s glue. Here you can find definitions, rules for projects and fairs, tips, web resources, and ideas for displays. Also, the links and books section is helpful. http://school.discoveryeducation.com/sciencefaircentral/scifairstudio/links.html 8. Ask an expert. http://www.k12science.org/askanexpert.html This site is run by the Center for Innovation in Engineering and Science Education. It is affiliated with the Charles V. Schaefer, Jr. School of Engineering. You can click on any of the links to take you to a variety of subjects, from space science to oceanography. This is a good resource to find background information and more facts to support your conclusion. I like the section on pests- you can ask the Orkin man! 9. eNature http://enature.com/home/ This is a great site if you are doing a biology project. It has field guides, local nature resources, plant information, expert information, and some fun things like puzzles and screensavers. There is a searchable database of articles on nature topics that could be used in background research or in your conclusion. I especially like the picture database called “Not sure what you just saw?” It is an interactive field guide that helps you identify things based on pictures. 10. Links to even more links http://school.discoveryeducation.com/schrockguide/sci-tech/scigs.html Another teacher named Kathy Schrock made a fortune by working with the Discovery Channel! Smart lady. She has a list of resources that are useful to both teachers and students. There are tons of searches, databases, and resources to look through. For you sports fans there is even a link on the science behind hockey. Also, you will see a site called invention dimension, which has information on inventions and inventors. 11. You could be famous. http://school.discoveryeducation.com/sciencefaircentral/scientist.html Enter the Discovery School Challenge to be America’s Young Scientist.

CREDITS (INCLUDING CONTACT INFORMATION): Megan McGolrick [email protected] Andreia Neto [email protected] Beverly Barnes [email protected] COMMENTS: Megan originally taught this activity without the PowerPoint. The students used Excel to enter their individual data, and students learned how to enter data, create formulas, and make graphs by following oral directions. The assessment rubric was used in the original lesson. We created this learning activity in response to the frustration she experienced during that lesson. This particular lesson has not been tried in this final form but knowing the areas of difficulty that needed to be addressed. We feel that this lesson should be successful for the students and the teachers. REFERENCES: The items in this learning activity came from teacher prior knowledge. Used with permission of the International Society for Technology in Education (ISTE) National Educational Technology Standards (NETS) Project (http://www.iste.org or http://cnets.iste.org) Contact: Lajeane Thomas, Louisiana Tech University, P.O. Box 3161, Ruston, LA 71272; Voice: 318 257-3923 Email: [email protected]