# Excel Part 1—Writing Functions: Translating Chemistry into Excel

Student Learning Outcomes:

At the end of this activity, students should be able to

• Use Excel to create a basic spreadsheet.
• Translate mathematical equations into Excel functions.
• Use the extra features of Excel to communicate your results clearly.

## The Problem

The analysis of a plant-food preparation for its potassium ion content yielded the following data: 5.25%, 5.03%, 5.20%, 5.06%, 5.04%, and 5.16%.  Using Excel, write the functions and determine the mean, the standard deviation, and the % relative standard deviation for this set of replicate measurements.

### A. Entering the Data

Enter the heading Data in cell B3, and then enter the individual results in Column B under this heading (in cells B4-B9).

### B. Finding the Mean—How to Write a Formula

When I design a formula for Excel, I think it helps to write it out on paper before typing it into the appropriate cell!  A formula will always begin with “=” (the equals symbol).

To set up a formula, you have to know all of the little parts that go into your calculation.  For example-- what exactly do we have to do to calculate the mean of a set of measurements?

• Add up all of the measurements.
• Find out how many measurements there are.
• Divide this sum by the number of measurements.

Now we have to tell Excel what calculations we want it to do and the order in which we want it to do them.

Enter the following labels: Total in cell A12, N in cell A13, Statistical Analysis in cell A15, and Mean in cell A16.   In order to calculate the mean, we need the Total (the sum of the individual results) and N (the number of measurements).  Write the formula that you will need to enter in the cell just to the right of each of these labels:

• For cell B12, _________________________________

• For cell B13, _________________________________

• For cell B16, _________________________________.

### C. Finding the Deviations from the Mean—Absolute and Relative References

When you work with replicate measurements, you will often do the same calculations for each one.  Using absolute references (for values that must be the same for each calculation) and relative references (for values that have the same relative cell position in each row), you only have to write the formula once!

Enter the heading Deviation in cell C3.  Write the formula that you will need to enter in cell C4:

In order to apply this formula to the other percentages, click on cell C4, click on the fill handle, and drag the rectangle down through all of the appropriate cells.

### D. Writing Formulas—the Order of Operations

Excel performs operations in the same order as in algebra: for example, multiplication and division will be performed before addition and subtraction (PEMDAS).  You can change this natural order of operations by properly placing parentheses. These parentheses remind me of how we use street signs to direct the flow of traffic!

Here’s the equation for calculating the standard deviation for a set of replicate measurements:

$s = \sqrt{\dfrac{\sum_{i=1}^{N}(x_i-\bar{x})^2}{N-1}}\nonumber$

What calculations must we do, and in what order must we do them?

1. Calculating (Deviation)2

Enter the heading Deviation^2 in cell D3. Write the formula that you will need to enter in cell D4:

In order to apply this formula to the other percentages, click on cell D4, click on the fill handle, and drag the rectangle down through all of the appropriate cells.

In order to calculate the standard deviation, we can also go ahead and calculate the sum of the (deviations)2.  Write the formula that you will need to enter in cell D12:

2. Calculating the Standard Deviation

How do we get from here to the standard deviation? Enter the heading Std Dev in cell A17. Write the formula that you will need to enter in cell B17:

3. Calculating the Relative Standard Deviation

To calculate the % relative standard deviation, all you need is the standard deviation, the mean, and a formula that includes both of them.  The equation looks like this:

$\%RSD = \dfrac{s}{\bar{x}}\times 100\%\nonumber$

so the Excel formula looks like this:

Enter the heading %RSD in cell A18, then click on cell B18 and type your formula [enter].  What did you get for the %RSD?

1. Reporting the Final Result

Report the final result like this: mean + standard deviation. In general, the standard deviation is reported with one significant figure.  Enter the heading Final Result in cell D17, and then type the final result in cell E17.

In order for me to follow your work on paper (and to make it easier for you to remember what you did in these calculations), you must provide one formula for each different type of calculation. That means that if you do more than one of the same calculation, you still only show the formula once.

• Leave one blank row between the spreadsheet and the spreadsheet documentation; enter the heading Spreadsheet Documentation in cell A20. Starting with the next row, display the name (in column A) and the formula that you used (in the cell to the right, in Column B) for each different calculation. It’s a good idea to count the number of different calculations that you used (to make sure that you keep track of all of them), and choose how to order them.  There are different ways to do this.  You can just retype each formula, or you can use my copy and paste method, below. Regardless of which method you choose, do your Spreadsheet Documentation after your spreadsheet is completely finished to avoid the problem of cell # changes.

Dr. Strickland’s Copy and Paste Method:

• Highlight the first (or only) cell for a particular calculation.
• Copy the formula without the equals sign.
• Click after (to the right of) the formula, then press Enter.
• Paste the formula into the appropriate cell in Column B.

Remember that a spreadsheet is a way of communicating the results of your analysis to a variety of people.  Make this communication clear and interesting by making your spreadsheet orderly, clearly labeled, and well documented.  Include charts and graphs when they’re appropriate.

1. Title—Merging Cells

Enter a title into cell A1, and merge the appropriate number of cells to accommodate the title.  You can also make the title bold, a different color, a different size…  There are a number of options here!

1. Other Individual Style Details

Remember that you can also make headings bold, center the data in a column, right- or left-justify pieces of information, etc.  Make it clear, and make it your own!

1. Printing Details

There are things that you can see on the computer screen that will not be in the printed hardcopy unless you specify them.  Exactly how you get to these details depends on whether you’re working on a PC or a Mac.  Here’s what you need:

• Borders (the outline around each cell)