BIO 256 - Computer Applications in Biology

Assignment 8 - Spreadsheet calculations

Important References:

• BIO 256 - Using Spreadsheets

One of the most common uses of spreadsheets among biologists is to perform calculations that have to be done repeatedly. Let's say that you are working with DNA, and you need a standard concentration for your analyses, but when you extract DNA from an organism, the concentration varies from one sample to the next. You measure DNA concentration with a fluorometer, in units of nanograms/microliter. If you need 50 microliters of standard solution, at a concentration of 10 nanograms/microliter, and the fluorometer says that the concentration of your DNA is 25 nanograms/microliter, the amount of DNA you want to add to make a standard solution is (10 nanograms/microliter divided by 25 nanograms/microliter) times 50 microliters, or 20.00 microliters. You will dilute the DNA in buffer; the amount is 50 microliters minus 20 microliters, which equals 30 microliters.

Clearly, doing a bunch of these one at a time on a calculator will get old really fast. And these kinds of calculations are common in many areas of biotechnology. But by making a spreadsheet "template", you can automate the calculations, so that you only have to type in the desired volume of the standard solution, its desired concentration, and the fluorometer values for each sample. Here is what such a spreadsheet would look like (numbers in bold are the values you enter, numbers in italics are calculated by the spreadsheet):

50	µl of finished standard solution
10	ng/µl of DNA in standard solution
sample #	fluorometer reading	µl DNA stock sol	µl buffer
245	44	11.36	38.64
246	135	3.70	46.30
247	58	8.62	41.38
248	99	5.05	44.95
249	28	17.86	32.14

(Note: The example above contains the lower-case Greek letter "mu" in the abbreviation for microliter. If you are viewing this page with a recent version of Netscape or Internet Explorer, it will appear correctly, but it may look like a different character if you are using an older browser. Some people use the letter "u" instead, but that is a mark of ignorance. To produce a "mu" with Windows, hold down the Alt key and type 0181 on the numeric keypad. To make a "mu" on a Macintosh, type Option-m.)

Assignment

Your assignment is to make a spreadsheet that looks like this (except for the bold and italics; that's just to help you see what needs to be done, although you can include them if you want). The numbers shown here in italics must be formulas in the spreadsheet you turn in, and of course they must result in the values shown (if you do the math right, they will). I will enter other values for the fluorometer reading when I grade it and see if I get the correct results. Remember that the volume and concentration of the finished standard solution may also change, and should be incorporated in the formulas (hint 1: search for help in your spreadsheet program for copying formulas with absolute cell references; hint 2: it involves the $ sign). I will also enter different values for these to see if the formulas recalculate correctly.

Your spreadsheet should be saved and turned in according to the directions in BIO 256 - Using Spreadsheets (this is Assignment 8).

Summary of assignment