BIO 256 - Computer Applications in Biology

Assignment 7 - Spreadsheet data handling

Important References:

• BIO 256 - Using Spreadsheets

Most biologists, if given the choice of using only one piece of application software, would pick a word-processing program. The same biologists might not agree on a second application, but almost all biologists find uses for spreadsheets, once they know what spreadsheets can do.

Virtually all spreadsheets consist of places to put data called cells. The cells are arranged in columns and rows, so that every cell has a unique address specified by its column and row identifiers. Thus, cell C14 is at the intersection of column C and row 14.

Three things can go into a cell: a value, a label, or a formula. Values are simply numbers. Labels are text: words, letters, even numerals, if you don't want the spreadsheet to work with them mathematically. Formulas manipulate values and labels; for example, the formula A1+A2 would give the sum of the values in A1 and A2, and =AVERAGE(B1:B25) would give the arithmetic mean of the values in cells B1 through B25. The value or label that is produced by a formula appears in the cell where it is entered, and the formula itself appears in a separate edit line.

Just these simple functions allow you do do useful things with a spreadsheet. You can make tables that automatically calculate values, you can store any information that consists of long columns of data, you can even prepare templates, spreadsheets that have all the calculations for such things as statistics or enzyme kinetics, and all you have to do is plug in the values. But that is not all spreadsheets can do. They can also make graphs, alphabetize or sort numerically, and search for data in long files. In this class, you will only "scratch the surface" of the functions of modern spreadsheets, but once you start to use them, you will find more and more things they will do for you.

Before you begin, read BIO 256 - Using Spreadsheets. It contains important information about all the spreadsheet assignments.

Assignment

The following data are the numbers of articles about gene regulation that were found in five journals (we'll just call the journals A, B, C, D, and E) in each year from 1985 through 1989.

	A	B	C	D	E
1985	2	0	5	2	0
1986	5	0	4	9	6
1987	4	4	6	17	1
1988	9	12	6	25	4
1989	15	19	7	29	2

Your task is to enter the data in a spreadsheet file. The rows should be years, and the columns should be journals, just as you see above. In addition, your spreadsheet should include the average number of articles in all journals for each year (these would be row averages), the average number of articles for each journal in all years (these would be column averages) and the average in all journals over all years, (yes, there are several ways to achieve this). The averages must be formulas, not numbers that you have calculated elsewhere. You should also include labels for the rows and columns, much as the example above. The worksheet should be saved and turned in according to the directions in BIO 256 - Using Spreadsheets (this is Assignment 7).

Summary of assignment