Excel tutorial

	Tutorial 04
Algebra
Solving basic algebraic equations with Excel

on to symbols

Excel is very useful when solving algebraic equations. The program, however, will not actually perform any algebraic operations; you must supply the proper formula.

Thickness of a piece of paper

For instance, we can use Excel to determine the thickness of one sheet of paper. Say we know that a ream of paper contains 500 sheets and is 4.895 cm thick. The thickness of a single sheet can be determined by dividing the total thickness by the total number of sheets in the ream. Let's utilize the spread sheet program by first recording our known values, i.e., the total number of sheets and the total thickness. These are entered into columns A and B, respectively, in the example below. The formula (=B2/A2) can then be entered in cell C2. The worksheet example is shown below.

Note that the cell containing the formula is highlighted and the equation is also printed and highlighted.

Worksheets submitted for grading:
When submitting worksheets, it is important that you highlight and print your formulas so your instructor can follow your steps and and help you identify errors. Also be sure to print the grid lines and the Row and Column headings. For information about these and other basic actions, see the previous tutorial.

Tables and Graphs submitted for grading:
When submitting Tables and graphs as part of a formal lab report need to conform to the Department of Biology Style Manual and grid lines and formuli would not be included.

Solving for X in a linear equation (Protein concentration from a calibration curve)

Here is another slightly more complicated example in which Excel can save you an enormous amount of work. Let's say that you need to determine the protein concentration in each of ten cockroach gut samples. You have a calibration curve that relates spectrophotometric absorbance at 595 nanometers= to protein concentration with the linear equation y = m * x + b or in this case; absorbance = (protein concentration + the y intercept)/ slope. Abs = (m * [Pr] + b). Solving for X or protein concentration allows you to use spectrophotometric readings to determine protein concentration from the formula. [Pr] = (abs - b)/m. Therefore your worksheet may look like the following:

Conversions

We can use the work in the above example to determine the protein concentration even if we needed to dilute the sample to get an on scale reading from the spectrophotometer. If for example we dilute the sample with 4 volumes of water, the original protein is now in 5 ml of water instead of 1ml (a 1:5 dilution) and we will have to multiply the protein concentration of the diluted solution by the dilution factor (5) to determine the original protein concentration. This is a trivial operation for Excel as shown below. :

To determine the protein concentration for any of your roach gut samples, all you need to do is change the contents of cell A2 and D2 to the absorbance and dilution factor for that sample and the protein concentration is automatically recalculated for you! There is no need to alter or retype any of your formulas!

Complex equations

hese are pretty simple formuli and reentering the numbers on a calculator might not be as difficult as learning Excel. Here is an example that might convince you that it is easier to learn to enter formuli in Excel. The general quadratic equation is given by . Its solution is, of course, the quadratic formula, or

Say we are given the quadratic equation, , and we wish to find both of its solutions. We can use Excel to help us do this and the screen shot below shows how that is done. (The spaces within the formula were added to make the formula easier to read, but are not necessary.)

Of course, to solve another quadratic equation, all one needs to do is change the values of the constants a, b and c in cells A1, B1 and C1. There is no need to alter the formula and the new result is given immediately after the new values are entered!

Are you beginning to see how Excel can save you time in preparing lab reports?

Calculating the radius of a sphere Lets consider one last example with which all are familiar. You should know that the volume of a sphere is given by the formula , where r is the sphere's radius and p = 3.14159. In this example, we are told that the volume of a bacterium is 7200 µm³. Use Excel to determine the radius of the bacterium. First, remembering your seventh grade algebra rearrange the equation to. Theis screen shot shows one way to use Excel to solve for the radius:

Built in constants

In the above example, we used the number 3.14159 to represent the constant p. Excel has a built-in constant, PI( ), which returns the value for p when used in an Excel formula. Therefore, the equation for the radius in the above example can be rewritten as shown below:

Either method is fine to use. Some prefer to use the numerical value and some prefer to use the built-in constant. The choice is yours but I know that I can't write out p to 15 decimal places and Excel can.

Finally, let's say, that we know the volumes of four other bacteria and we would like to determine their radii so we can identify them under the microscope. You could simply change the value of cell A2 in the above examples and determine the radius of each sphere one at a time. You could type new formulas for each sphere. Or you could use the copy and paste functions that you learned of in the previous tutorial and use the time you saved for truly significant endeavors like watching reruns of the Simpsons.