BIO 256 - Computer Applications in Biology
Assignment 9 - Spreadsheet graphics
Important References:
A graph is a visual representation of quantitative data. Some biologists have the ability to look at columns of numbers and easily make sense of them, but most of us are aided by seeing the data in a graph.
Biologists use many different kinds of graphs, but there are three that are perhaps the most common:
- Bar graphs depict numbers as the height or length of bars. They are best used to show numeric data that represent discrete items or experiments. Bars imply that there are no intermediate values (unlike a line graph: see below), and in many (but not all) cases the order of the bars along the X-axis will be arbitrary.
- Line graphs best represent data that are samples from continuous phenomena. The visual implication of the line is that intermediate points exist, but were not sampled. Values taken over time or through space fit this criterion, as do observations at different dosages (assuming that the dosage could be varied continuously). The order of the data along the X-axis is of course not arbitrary with a line graph.
- Scatterplots: In bar and line graphs, the values for the independent variable are truly independent and are generally chosen in advance. A scatterplot instead shows the relationship between two measured variables as a scatter of individual points, each representing an item with its position determined along the X and Y axes by its values for the two variables. The points of a scatterplot are never connected by lines, although sometimes a regression line is plotted, showing how one measurement varies in relation to the other.
Spreadsheets have included graphics for two decades, but their graphing functions are designed more for business than for science. Many scientists use specialized scientific graphics programs to produce exactly the type of graphs they need. Nevertheless, it is possible to get scientific graphs from a spreadsheet, and that is the purpose of this assignment.
Spreadsheet programs don't always use the same vocabulary about graphs that scientists do. Scatterplots are called "XY" graphs, and vertical bar graphs are often called "column graphs". Spreadsheets often add unnecessary visual elements: garish colors, fake "3D" effects, and such. These have to be removed to make graphs suitable for scientific presentation. Fortunately, recent versions of Excel avoid many of these "enhancements".
Assignment
For this assignment, you will be constructing examples of the three kinds of graphs. If you are using recent versions of Excel (or another spreadsheet with multiple pages), you should put each graph on a separate "sheet" of the "workbook". If you are using an old spreadsheet program that only gives you one page, you can turn in the assignment as three separate files: follow the naming convention, but make the last letter before the period an "a" for the first graph, "b" for the second, and "c" for the third (if Charles Darwin were turning in the second graph, it would be called 9darwinb.xls).
For each graph, the data are available on a separate web page. Click the link to go to it, and use copy-and-paste to transfer it to your spreadsheet. For the scatterplot there is an alternate way to get the data.
- Bar graph: The data are the maximum number of seats for each lecture class in Spring 1998 that had a BIO, BOT, MIC, or ZOO prefix (the data come from the Schedule of Classes web page; I copied them from the web page into Excel and modified them into the format you see here). You must calculate the average number of seats per lecture for each of the four course prefixes, and plot the averages in a bar graph, with the bars in order alphabetically by course prefix.
The finished graph should look like this (you don't have to match the black-and-white appearance):
- Line graph: When the internal environment of a salamander becomes too acidic, it produces the hormone aldosterone, which causes it to excrete acid. When a synthetic hormone, Angiotensin II, is injected, it causes an increase in aldosterone level (if you are interested in this experiment, you can read more in Eskandari, S., and D. F. Stiffler. 1997. Interrenal function in larval Ambystoma tigrinum. IV. Acid-base balance and the renin-angiotensin system. General and Comparative Endocrinology 105: 18-30).
The data are the measured levels of aldosterone in the control and the Angiotensin II-treated salamanders at 0, 2, 4, 6, and 8 hours. You could make this graph as an XY graph, but since the time intervals are equal, you could also make a line graph (make sure the X axis is properly labeled with the times).
The finished graph should look like this (you don't have to match the black-and-white appearance):
- Scatterplot:The data are elevation and mean annual temperature for a number of reporting stations in Nevada. (Why not California? Because there are lots of stations in California.) You want to see whether temperature varies with elevation, so you will make a scatterplot (Excel calls this an "XY" graph) with elevation on the X (horizontal) axis and temperature on the Y (vertical) axis. The graph may initially have lines connecting the points; you'll have to turn them off. Also, be sure you don't make a line graph by mistake--in some spreadsheets, that is easy to do.
The finished graph should look like this (you don't have to match the black-and-white appearance, but there must be no lines connecting the points):
Summary of assignment