Getting Started
Using spreadsheets |
 |
 |
Studies in vegetation science typically collect a lot of numbers. Effective handling of these data sets saves time, reduces errors, and improves the chances of making thorough and insightful interpretations. Many options are available for handling data, from paper and pencil through customized database systems. A general yet powerful way to handle your data is with a spreadsheet program like Quattro Pro or Excel. Spreadsheets are also good for doing the calculations required by the course assignments. Spreadsheets hold a special advantage over other types of software because they show how you did the calculations, making it easy for me to grant partial credit even if the final answer is incorrect. For all these reasons, using a spreadsheet to organize and analyze your data is a required part of the course, and when you have data to report you will attach an electronic copy of your spreadsheet when you submit your report.
Click the spreadsheet example to see a simple but effective way to organize and analyze the kind of data you'll be generating in the course.
Learning how to use spreadsheets
If you are new to spreadsheets but have access to a spreadsheet program, you can learn all you need for the course in a short amount of time. Online tutorials are available at several places on the Web. I can't endorse them (I haven't looked through them carefully and they are not from OSU), but they might be useful:
- A compendium of spreadsheet tutorials is available from the University of Alberta. Click here
- A detailed, basic step-by-step introduction to Excel is available from the University of South Dakota. Click here
- A less detailed, less basic tutorial for Excel is offered by Cal State Stanislaus. Click here
A few skills will let you do almost all you need. This is all you need to know to get started:
- Spreadsheets are organized like tables, with cells arranged in rows and columns.
- You enter data into a cell, and can later edit it.
- The address of the cell is its row number and column letter.
- You can use the cells in arithmetic and more complicated expressions. (Don't copy the value by hand - that increases the chance of round or entry errors. Instead refer to the value by its address.)
- Spreadsheets also allow you to use mathematical functions, saving you lots of work. (See a list of common functions in the table below.)
- A spreadsheet can be easier to read with a little formatting. For example, you can adjust the width of a column, make text bold, or change a cell's background color.
|
Spreadsheet functions useful in BOT 440/540 |
||
|
To calculate |
Use in Quattro Pro |
Use in Excel |
|
mean |
@pureavg |
=average |
|
median |
@median |
=median |
|
totals |
@sum |
=sum |
|
standard deviation |
@stds |
=stdev |
|
t-statistic |
@tinv |
=tinv |
|
|
@pi |
=pi |
Referring to other cells, and a note on relative and absolute addresses
A strength of spreadsheets is their ability to refer to other cells. This means you can enter your data just once and then refer to it from then on. For example, to calculate the mean of a series of numbers in column D, rows 11 through 20, the formula might look like @pureavg(D11..D20) or =average(D11:D20). If this formula is in cell D21, then you can refer to the average in cell D21 rather than copy the number into a new formula, say for standard deviation. This avoids both transcription error and rounding error, errors you don't want to creep into your calculations.
Cell references will shift as you move formulae from cell to cell. This shifting is called relative addressing. If you want to prevent this shifting, use absoluted addressing in your formulae. An absolute address is just like a relative one, only it have a dollar sign in front of the column and row. The absolute address of D21 is $D$21. A formula using this address will always point to cell D21, no matter where you move the formula.
See examples of these points by downloading the spreadsheet example.
Two pitfalls when using spreadsheets
Warning #1
Make sure you know what a spreadsheet functions does! Some functions have names that sound good but calculateare something unexpected. An important example is the confidence function (=confidence in Excel, @confidence in Quattro Pro). This function does not calculate confidence intervals for sample data! Rather, these confidence functions assume that you have measured the entire population, a situation never encountered in vegetation science, so they are of no use in BOT 440/540.
Warning #2
Be alert to the way some spreadsheet functions handle blank cells or cells with text instead of numbers. Blank cells and cells with text are ignored by Excel when calculating =average, =median, =sum, and =stdev. So far, so good. But that means that a cell with the character 0 is not the same as a cell with the number 0. Quattro Pro ignores blank cells when calculating @median, @sum, and @stds, but not when calculating @avg! Quattro Pro treats blanks cells as zero when calculating @avg and treats cells with text as zero when calculating @avg and @stds.
Spreadsheet organization
There are many ways to organize a spreadsheet. Choose a style that is clear to you and clear to your reader (that's me). A clear format also reduces the chances of making an error.
Click the spreadsheet example to see a simple but effective way to organize and analyze the kind of data you'll be generating in the course. The first sheet shows the example; the second sheet adds notes that explain what makes the spreadsheet effective. Notice that nowhere is a number copied by hand -- the spreadsheet just refers to the proper cell. This reduces transcription errors and makes it much easier if you have to update your calculations. The spreadsheet also makes liberal use of built-in functions to do the calculations.
An important point: Putting the calculations and functions in the cells of your spreadsheet is a completely satisfactory way to "show your work."
Finally, you can always make a spreadsheet more fancy with special fonts and cell colors, but the most important things are clarity and accuracy.
|
|