## Abstract

Students often enter biology programs deficient in the math and computational skills that would enhance their attainment of a deeper understanding of the discipline. To address some of these concerns, I developed a series of spreadsheet simulation exercises that focus on some of the mathematical foundations of scientific inquiry and the benefits of mathematical modeling in understanding some of the fundamental concepts of ecology, including topics such as population growth, selection, competition, predation, and sustainability. In the example presented here, students collect data from a computer-generated study site and then use basic spreadsheet skills to calculate, graphically visualize, and compare sample means (± SE) to effectively discover why random and transect sampling are superior to biased sampling, and to explore the effects of sample size on the standard error of the mean. Pre- and post-exercise questionnaires suggest that spreadsheet models such as this one are effective in improving students’ attainment of the target principles. Spreadsheet simulations can be used as a low-cost, easily accessible way to leverage technology to supplement lecture or laboratory content while fostering vital math and computer skills.

- Math skills
- computer skills
- spreadsheet simulation
- computer activity
- inquiry
- graphing and data visualization

It is no secret that many students think of biology as a set of facts to be learned rather than a set of skills to be mastered and applied. Traditionally, they have memorized the parts of a cell, the organs of the body, and the names and shapes of bones, and tossed them back to their teachers in the predictable volley of instruction and evaluation. It is absolutely essential for budding biologists to be conversant with the structure of organisms (hence, students’ fascination with dissection) and with their identification and classification from domain to species. However, these aspects of biology do not go much further than asking the descriptive end of the “W” questions – who, what, and where – and fall short of exploring the deeper issues of why and how.

Joel Cohen (2004) cites Charles Darwin’s observation that those who have an understanding “of the great leading principles of mathematics…seem to have an extra sense.” According to Cohen, mathematics is a biologist’s tool that is every bit as fundamental and revolutionary as the microscope, providing new views and – more importantly – new insights into both the structure and function of life. Our current understanding of genetics, evolutionary biology, and even the structure of DNA would never have come to be without mathematics.

Darwin’s “extra sense” is precisely the edge that today’s students need to appreciate the advances being made in the field and also to become more accomplished investigators in their own right. Students must develop skills in both critical observation and analysis in order to move beyond rote memorization of dry facts, and the growing emphasis on inquiry at all levels of education is moving students along that path. Besides being able to ask the right questions, it is important to be able to conduct investigations and objectively evaluate the results in meaningful ways to draw valid conclusions. To do this, it is becoming necessary to more fully integrate a suitable mathematics toolbox into the biology skill set, and to do this from the grade schools on through postsecondary programs.

Although long regarded as an anchor of the “three R’s” of basic education (reading, writing, and arithmetic), there is a growing realization that math skills are essential to success in others fields like biology. Hochberg & Gabric (2010) and Karsai & Kampis (2010) discuss the need for improved mathematical training of biology students at the secondary and postsecondary levels where mathematics not only allows for the analysis of collected information, but also provides a framework for the development of models to understand better the patterns and phenomena of nature. The BIO2010 document issued by the National Research Council (2003) also placed a high value on the integration of math and computing skills in the training of the next generation of life scientists.

Although the need for math and computer skills is amply evident in the literature and in everyday practice in the classroom, there continues to be a disconnect between students’ perceptions of their skills and their ability to demonstrate them. Investigators studying the skills of incoming students at North Carolina State University found that the students significantly overestimated their own computational skills in using Microsoft Excel, signaling a serious deficit in their preparation at the secondary level (Grant et al., 2009). With the need for better skills increasing in the work force, it becomes necessary to supplement students’ training at all levels with targeted instruction that specifically addresses the quantitative, analytical, and predictive value of mathematics in biological disciplines.

Concepts like random sampling and the need for adequate sample size are difficult concepts for many beginning science students, yet they are central to the practice of scientific inquiry. For example, many teachers and science-fair judges have probably seen examples of projects like the one I always recall when discussing how not to design an experiment: a single plant is given some strange treatment (like motor oil) and compared with a single control (given water only), demonstrating a whole suite of experimental mistakes, not the least of which is inadequate sample size. We often deal with questions related to sampling and sample size as we perform other experiments or laboratory exercises to examine specific biological principles, or by direct lecture involving the delivery of rules that require more-or-less unquestioned acceptance. It is likely that students may focus more on the results of the experiment than the process used to get there, and instructors may do the same – I know I have. But if we hope to help students develop appropriate skills for more independent inquiry, fundamental principles of experimental design are important enough to warrant a more focused exploration. By specifically addressing questions related to topics like sampling in a controlled format such as a spreadsheet simulation, we can help students develop a greater understanding of why we approach investigations as we do, and this knowledge can then be expanded and applied in their own independent or group investigations.

In order to address concerns such as these, I developed a series of spreadsheet exercises that focus on building proficiency in data entry, coding, and simple analysis as well as demonstrating some important principles of experimental sampling and modeling. The exercises allow students to gain transferrable experience in the use of spreadsheet applications and also allow for a quick way to visualize principles that would be difficult and more time-consuming to explore in more traditional laboratory conditions.

## Target Audience

These exercises were designed to be used with students who have a minimal knowledge of spreadsheet operations and basic math skills. As presented, the activities would be useful in junior- or senior-level high school classes, and with introductory-level courses at the college level. For younger students or students who may have little or no experience with spreadsheets, a short introduction to spreadsheets along with a spreadsheet “boot camp” exercise to explain the order of operations and basic rules of data and formula entry would be helpful.

## Objectives

In this exercise, students explore three different approaches to collecting data, and compare those using simple statistical calculations, along with graphs to easily visualize the comparisons. At the end of the exercises, students should be able to (1) explain what sample bias is, and why random or transect sampling helps to reduce sample bias in an ecological field study; and (2) explain the effect of sample size on standard error (SE) and why it is important to include an appropriate number of observations in a sample.

## Proposed Activities

For each of the simulations, the goal is to provide an overall estimate of the primary productivity of the entire study site, which, as often seen in the natural world, is not perfectly uniform in the abundance and distribution of organisms. The first three activities address the issue of sample bias compared with the benefits of random sampling. Bias involves any kind of systematic error that potentially adds to or takes away from the true values of the observations (Rumsey, 2003). Elements of bias can be found in sampling methods, in poor experimental design, or in inappropriate analysis of data.

In the first activity, we introduce sampling bias by taking measurements only from specific areas – for example, only the wettest areas, only the mesic (or medium moisture) zone, or only the driest parts of the plot. The overall picture of productivity may be skewed either low or high, because neither of the extreme locations accurately represents the entire site. In the second activity, we examine transect sampling (taking measurements along a linear path across the entire study site) along the gradient as a way to take more representative observations from all parts of the site. In the third activity, we use randomly generated numbers to find specific cell addresses on the data grid that we use for data collection. If all goes as expected (since you made the data set), transect sampling will provide a fairly reasonable approximation of random sampling, and both of these methods are shown to be superior to the biased results.

The last activity explores the issue of sample size. Students take samples of different sizes (i.e., n = 6, 12, 24, 36, and 144) from the simulated environment and compare them in terms of the calculated means and the magnitude of the standard errors. As a general rule when examining sample means, a sample size of 30 should be adequate for many investigations, because according to Gossett (the original “Student” of Student’s t-test), the modified t-distribution, which is shorter and wider than a normal probability distribution and provides the basis for numerous statistical tests involving smaller sample sizes, is virtually indistinguishable from a normal probability distribution in which the number of observations is ~30 (Rumsey, 2003). Increasing sample size will lead to smaller SE values, because a larger data set means that individual variations get “averaged out” to a greater extent. Mathematically, as the number of observations (n) in a sample increases, the denominator for the SE calculation (i.e., the square root of n) also increases, and the size of the error is reduced. Smaller SE values give more confidence that the sample mean is a good representation of the true population mean.

## Materials & Methods

*Required software.* A spreadsheet program such as Microsoft Excel or LibreOffice Calc with statistical and graphing capability is required to perform these simulations. In this exercise, specific references to the Excel ribbon and dialogs refer to the Windows version of Excel. Basic commands are very similar between Excel and Calc, which makes it fairly easy to transition between the two. Packages such as Microsoft Works may be good for general household bookkeeping, but they do not offer the statistical depth and graphing flexibility needed for this type of activity.

*Simulated data set.* I built a simple 12 × 12 data grid (Table 1) using the “RANDBETWEEN(X,Y)” command to represent a plant community in a mixed habitat simulating a moisture gradient from wet (left) to dry (right). The data in each cell represent the aboveground biomass of all the plants in that cell, recorded in grams dry weight per square meter (gdwt/m^{2}). I overlapped the ranges in adjacent zones to add more realism to the simulation and give a better approximation of a blended gradient. Columns 1 through 4 were designated as the “wet” zone, and values were generated with limits of 50 to 100; columns 5 through 8 simulate a mesic zone, with limits of 25 to 75; and columns 9 through 12 represent a dry area and were assigned limits of 0 to 50. A grid of any size could be generated, but the 12 × 12 example fit well on a page and provided a large enough population to demonstrate the principles I chose to focus on. I provided one data set for all students to use, but it would be easy to generate a data grid for each group or even for each individual student.

*Data analysis.* In terms of analysis and interpretation, I ask students to compute the mean (=AVERAGE(X:Y), where X and Y represent cell addresses that define the range of the data to be included in the calculation), standard deviation (=STDEV(X:Y)), and standard error (=STDEV(X:Y)/SQRT(N), where N is the number of observations in the sample) for each of the required samples.

Although standard deviations are used frequently in some disciplines, the standard error of the mean is used here because we are specifically studying sample means. Standard deviation is a measure of the average difference of any individual observation compared with the sample mean. Standard error is a measure of how far the sample mean may be from the true population mean. Standard error values are based on more data and provide for greater consistency from sample to sample (Rumsey, 2003).

Students graph their results using the software’s charting function, including SE bars for each treatment mean. In Excel 2007 and later, the dialog to add error bars appears under the “Chart Tools,” “Layout” tab on the ribbon. As a caution, it is important to avoid using Excel’s “Standard Error” option in the error bar dialog, because this does not provide the SE for each sample group’s mean as required for this exercise. Instead, it calculates an SE of all the plotted means, which is meaningless in this context. The dialog does provide the “Custom” option in the “Error Amounts” box, which allows you to easily assign the calculated values to each of the appropriate means.

Visualizing the data in this manner provides an easy way to roughly judge whether or not the compared sample means are likely to be significantly different from each other. According to the empirical rule (Rumsey, 2003), 68% of sample means should lie within ±1 SE unit of the population mean, which we might consider to be the “true mean” for the overall population from which the sample was taken. Ninety-five percent of the sample means should lie within ±2 SE units of the population mean. Although this is only a “quick-and-dirty” sort of assessment, it can be instructive: for example, if two means are graphed and their respective mean ± SE ranges overlap, it is not as likely that the two means are going to be significantly different from each other. If the two ranges do not overlap, it is more likely that the sample means may be significantly different from each other. Additional statistical tests could be employed to more exactly determine the case, but that is not the focus of this particular set of exercises.

## Results

*Biased sampling.* Recall that the goal of the survey was to measure the overall productivity of the entire site, and the effect of moisture was not the focus of this descriptive study. In the first exercise, students collected data from the grid by taking samples from columns that are perpendicular to the simulated left-to-right moisture gradient. The column samples were biased in that they were collected from distinct moisture conditions (i.e., wet, mesic, or dry zones). Students calculated basic statistics (mean, SD, and SE) for each sample and plotted the mean ± SE values in a column graph.

Figure 1 compares results from three biased samples taken from the simulated environment. Samples taken perpendicular to the gradient resulted in wide differences in the means of the three samples. The means ± SE do not overlap, which indicates that these means may be significantly different from each other. So what does this mean? It suggests that measurements within a single study site can be quite variable and, therefore, that attention must be paid to reduce sampling bias related to such differences.

*Transect sampling.* Because natural habitats are rarely completely homogeneous, variations in the sampling areas require us to adapt our sampling strategies. One way to do this is to set up a number of transects that traverse the whole sample plot (Greenwood, 1996). This way, sample observations can be collected from all parts of the habitat, and the local variability can be accounted for. In this exercise, measurements from transects that parallel the gradient (from left to right) were compared and the means ± SE were plotted.

Figure 2 is a comparison of three transect samples. The means are relatively close to each other, and the error bars overlap for all three samples, which indicates that these means are probably not going to be significantly different from each other.

*Random sampling.* There are several ways to randomly generate cell addresses from which to collect sample observations; one of the easiest is to use a spreadsheet command. On a separate spreadsheet, I labeled one column as “row” and another column as “column.” Using the command “=RANDBETWEEN(1,12)”, I randomly generated coordinates corresponding to cell addresses on Table 1. So, if cells A2 and B2 on this random generation sheet reported numbers of “1” and “7,” I took the value, “72,” from row 1, column 7 of Table 1 as the corresponding observation. Twenty-four random sets of coordinates were generated to provide two samples of 12 observations each for this exercise.

To broaden the scope of this exercise and to further demonstrate the value of transect sampling versus the danger of biased sampling, I compared the data from the biased dry-zone sample (column 11) from the first activity along with a transect sample (row 5) from the second activity to the randomly collected data. (You can easily copy and paste these from the previous pages to simplify the workflow.) As before, the four means ± the calculated SE of each sample were visualized with a bar graph.

Figure 3 compares the results of the random sampling procedure with the biased and transect sampling procedures. Note that the random and transect sampling procedures rendered results that are very similar to each other, with overlapping SE bars. It appears likely that the biased sample mean from the dry zone will be significantly different from the other values. In this scenario, transect sampling provides a good approximation of a random sampling technique without the need to generate random sample observation coordinates.

*Effects of sample size on standard error.* In the last exercise, students collected samples of various sizes (n = 6, 12, 24, 36, and 144) from specified rows (i.e., in the same way that they previously simulated “unbiased” transect sampling). Again, the basic statistics were calculated and graphed for each sample, with special attention to the magnitude of the SE values.

Figure 4 is a comparison of means of samples of different sample size. Note the effect of the sample size on the SE value: as expected, with increasing sample size, the SE diminishes. This reinforces the value of appropriate sample sizes along with the concept that smaller SE values give greater confidence that the sample means provide a more accurate representation of the expected population response.

## Student Assessment

I assign several spreadsheet modules during the course of a semester. The first assignments have lower point value than later ones, because the difficulty level increases as students gain skills and experience. For evaluation, I use a three-part rubric involving (1) a minimal number of points for attempting the assignment, (2) a similar number of points if all parts of the assignment are complete, and (3) the majority of points for accuracy of the content, including properly constructed formulas, appropriate graphs, and accurate responses to questions.

Students are encouraged to discuss their projects with each other as well as with me, but each student must individually submit an original file via e-mail attachment to receive a grade. This not only saves paper, but also lets me examine the formulas that the students have used. I return a grade slip to each student showing her score based on the three-part rubric, along with specific comments about errors.

## Recommendations for Implementation

This assignment was designed for what is essentially a college sophomore-level course. Advanced high school biology students, especially in Advanced Placement courses, should be able to complete any of these exercises quite easily. Similar activities could be done with middle school and lower-division high school students by reducing the emphasis on the statistics (i.e., SD and SE) and focusing more on graphing and visually comparing the means from samples collected using a biased sampling approach opposed to more random methods, and by observing differences in the means obtained from samples of different sizes.

*Sample questions for thought and discussion.* Although this exercise is focused on collecting data in a simulated, descriptive ecological study, you can apply the basic principles much more widely. Introducing some questions for discussion would be a good way to add another critical-thinking component and encourage the generalization of the concepts beyond a field study. For example:

In the first exercise, we considered the sampling from different zones to introduce bias. When would it be appropriate to specifically sample from the different areas?

Dr. Cheng has completed a study involving observations from 11 randomly selected individuals, and Dr. Patel has published a similar study involving observations from 79 randomly selected individuals. Their studies were similar in focus and methods, yet they each reported very different results. Based only on this information, which result would you be more inclined to accept as representative of a larger population? Why?

Warren is interested in studying how many people in his town have developed skin cancer. If he selects only fair-skinned, blond-haired people for his study, will he get a true estimate of how many people get skin cancer in a diverse population? Is there a better way to approach this problem? Explain your thoughts.

## Discussion

Has this exercise been effective in improving students’ understanding of the principles of random sampling and sample size? Has it helped to enhance the understanding of the role of standard error? In order to address these questions, I had my General Ecology students answer a brief five-question, multiple-choice questionnaire before the assignment was made, and then compared their responses on the same questionnaire after they had completed the assignment. A total of 50 students reported that they had completed and submitted the assignment prior to the posttest.

Among these 50 students, the mean pretest score (± SE) was 49.60 ± 2.75, and the posttest mean was 62.80 ± 3.02 – an overall performance increase of 26.61%. Fully 50% of the students improved their scores. The average change in individual scores was +53.50%. Although limited, these data suggest that spreadsheet activity modules may be helpful in teaching sampling principles, which are crucial to developing the skills necessary to conduct scientific investigations.

It is true that there are numerous excellent software modeling packages for biology that require little more than data input to quickly visualize changes in populations, allele frequencies, and so on. But to teach students how to code data, construct formulas, calculate statistics, and visualize and interpret results provides multiple layers of benefit beyond the demonstration of specific biological principles. As with any assignment, some students will view such activities as busy work. However, I can anecdotally attest that others have accomplished precisely what I have tried to achieve. For example, I ran into a former student in town one day, and she reminded me of the spreadsheet assignments that I “made” her do. She proudly reported that when she had to do some number crunching and graphing in another course she was taking, she was the one in her group who knew how to do it, and she said that it made her look “really smart.” Another student stopped by to discuss the analysis of his undergraduate research project and related that he was well on the way to completing it, thanks in part to the experience he had gained from working through these modules. Another high-ability, low-motivation student remarked that these were the only homework assignments that he had ever actually enjoyed.

Based on the implementation of spreadsheet models and simulations in my course, I am confident that these activities help to reinforce the broader usefulness of mathematics as a fundamental tool of the practicing biologist. The necessary software is universally available, whether already installed in campus computer labs or available as a free download, which suggests that the benefit-to-cost ratio is high enough. With the only real expense being in development time, spreadsheet models and simulations are a cost-effective way not only to help students explore principles associated with biology course content, but equally (if not more) importantly, build vital and transferrable math and computer skills.

## Acknowledgments

Microsoft Excel and Microsoft Works are registered trademarks of Microsoft Corporation. For more information, visit http://www.microsoft.com . LibreOffice Calc is a trademark of The Document Foundation. For more information, visit http://www.libreoffice.org . The spreadsheet activity module described here could not have been developed without the patience and cooperation of scores of students enrolled in Biology 331: General Ecology, at UT-Martin. This module is part of a larger collection developed partly through the generous support of the Nelson-Van Dyck Faculty Development Leave Program.

- © 2013 by National Association of Biology Teachers. All rights reserved. Request permission to photocopy or reproduce article content at the University of California Press’s Rights and Permissions Web site at http://www.ucpressjournals.com/reprintinfo.asp.