Using Excel's Formulas

Some users of the QI Macros miss the opportunity to use Excel's formulas to prepare the data for charting or analysis. Here are some useful ways to use Excel to your advantage.

Ratios

Most companies need to analyze defects in relationship to overall volume (e.g., repeat calls to a call center, repeat visits to an emergency room for the same problem, returned product, etc.) Although p and u control charts will show this kind of data, it's also acceptable to turn the numbers into a ratio and use an XmR (Individuals and Moving Range) chart.

Most hospitals keep track of patient falls and they report this as falls per thousand patient days. If the data looks like the following, how do you express it as a ratio?

Simply click on the cell to right of the data and enter the formula starting with an equal sign::

=B2/(C2/1000)

Where:
B2 is number of falls
C2 is number of patient days

C2/1000 yields the number of thousand patient days.
Putting the formula in parenthesis tells Excel to do this first, then divide it into B2, otherwise the answer would be incorrect.

This formula could also have been written as:

B2*1000/C2

Then, simply move the mouse over the lower right corner of the cell containing the formula (look for the cursor to change to a plus shape) and double click. Excel will copy the formula down to the last non-blank cell.

Then just select the labels in column A, hold the ctrl key, and select the ratios in column D to draw an XmR chart using the control chart wizard.

Excel Will Guide You

If you don't know what function to use, Excel can show you the way. Simply select an empty cell and click on Insert-Function (Excel 2000-2003 left; Excel 2007-2010 right):

Excel will show you the functions available:

Text

Let's use Excel's functions to split a cell containing a first and last name into two cells. Simply, click on the cell next to the full name and insert a text function. It took me a while to find this function (MID):

:

I often find it's easiest to start with a simple formula and then enhance it to do what I want. In this case, let's select just the first name of the first person by putting A2, 1, and 5 as the parameters to the function. The MID function will select "Wayne" from the name in cell A2.

Of course, this only works for the first person. It won't work for the next one. So, we'll want to set the length to be up to the first blank. There are two functions that search for strings inside other strings (FIND, SEARCH). Let's use SEARCH. Rather than try to build this into the existing formula directly, I often use a different cell so that I can see the result. In this case I'm telling Excel to find a blank (" ") in A2 starting at position 1, giving the answer, 6:

Six is one greater than the five I need, so I'll have to subtract one from the result. Now I just copy the SEARCH formula from cell D2 and insert it into the MID formula where the "5" used to be:

If I copy and paste the formula for the other names, we get the first name for each person. Excel will show the formula in fx:

The formula for the last name is similar, except that we use the SEARCH+1 as the starting point and use the LEN function for maximum length of the name:

Now, we've effectively split the first and last name out of the full name.

Time and Date Data

Time or Date data is another format that people can tweak to their needs.

If you want to know the difference between two times (e.g., a start and end time), simply input a formula to subtract one from the other (B2-A2 = elapsed time):

Then, if you want to convert the time or date format into minutes, you will need a formula that converts days, minutes and seconds into a single value. There are 1440 minutes in a day and 60 per hour. DAY, HOUR and MINUTE convert dates or times into a count you can multiply and add to get the result you want:

What if we want to convert times or dates into hours? The formula is very similar:

This gives us the elapsed time in various formats that can be easily graphed using the QI Macros:

Logic

Sometimes we need some IF-THEN-ELSE logic. Recently, a client was trying to figure out how to evaluate a report to show Pass/Fail so that it could be counted with a PivotTable. To do this, we had to add some formulas. In this case, a certain value "X" had to be between two values. The first formula evaluates if Prod1's X value is less than 0.05 and puts the word "Pass" or "Fail" in the Cell:

Of course, this formula won't work for all of the products in the report which have other specification limits:

So, we had to expand the formula to check the product name and choose the right limits:

The Pass/Fail results can now be counted easily with a PivotTable.

Here's My Point

Sometimes data has to be manipulated to provide the right starting point for analysis. Sometimes you need a simple mathematical formula, sometimes text, sometimes IF-THEN-ELSE logic. Regardless, sometimes you will need a basic grasp of Excel formulas and functions to make your life easier.

Have fun exploring Excel's functions. If you're struggling with how to get the data in a format you want, send me an email with your data and your problem. I can't do all of your work for you, but I can be a short cut to get you started