Assume you have the data for the mass of magnesium reacted with hydrochloric acid shown in table 1, but the graph you were assigned to plot requires the moles of magnesium used instead of mass.
Table 1
| trial number | mass of magnesium (grams) |
| 1 | 0.151 |
| 2 | 0.136 |
| 3 | 0.101 |
| 4 | 0.098 |
| 5 | 0.112 |
| 6 | 0.081 |
| 7 | 0.127 |
| 8 | 0.119 |
| 9 | 0.142 |
| 10 | 0.095 |
In order to convert from grams of magnesium to moles of magnesium, equation 1 would be used:
Moles Mg = Mass Mg (grams) / Molecular Weight Mg
Equation 1
where the molecular weight of magnesium is 24.305 g/mol.
This calculation can easily be done with a calculator, however, for large data sets, a spreadsheet is much faster.
A step wise procedure for using Microsoft Excel to do such calculations is given below.
1. Enter the data for the mass of magnesium shown in table 1 into rows 2 through 8 of column A. Row 1 should contain a data label of "Mass Mg (g)" to identify what the numbers in the spreadsheet represent. Cell B1 (i.e., column B, row 1) should contain the label "Moles Mg" which is what we want to calculate (figure 2)
Figure 2: Setup for Spreadsheet Calculation
2. Place the mouse cursor (represented as a "fat" "+" sign) on cell B2 (i.e., column B, row 2), click and hold the left mouse button, drag the pointer to cell B11, and release the mouse button. Cells B2 through B11 should be highlighted.
3. Next we have the spreadsheet program calculate the value for moles of magnesium for each row of data. Type the following exactly as it appears (shown in figure 3 - DO NOT hit "enter" when finished!):
=a2:a11/24.305
This is the same equation as equation 1, but it is entered symbolically so that the spreadsheet can understand it (see number 5 below).
Figure 3: Entering an Equation for a Spreadsheet Calculation
4. Simultaneously press and hold the CTRL, SHIFT, and ENTER (or RETURN) keys on the keyboard and the numerical values of moles of magnesium will be computed and automatically entered into cells B2 through B11.
5.Useful Info:
a. It is very important to use the "=" sign. The "=" sign tells the spreadsheet program that the information that follows is a formula and the values for the selected cells using that formula should be computed.
b. The "^" symbol means "raised to the power of...", the "*" means "multiplied by...", and the "a2:a11" means the range of cells A2 through A11.
6.Other Useful Info:
a. The order in which mathematical operators are executed in Microsoft Excel is:
a. Range (:)
b. negation of operand (-7)
c. exponentiation (^)
d. multiplication and division (* and /)
e. addition and subtraction (+ and -)
The best ways to avoid errors is to use parentheses to force the various operations to be executed in the order you wish.
b. Some other mathematical operators you may need are LN for the natural logarithm, LOG10 for the logarithm to the base 10, SQRT for the square root, and PI for the numerical value of .
