Page 157 - Lean six sigma demystified

136 Lean Six Sigma DemystifieD

representatives are using different words, phrases, or acronyms. For sample
data, look in the QI Macros Test Data for crosstab.xls and click on the
Word Count sheet.
Native Excel has a function called COUNTIF, which tallies cells if they
match certain criteria. The formula for the COUNTIF function is

=COUNTIF(CellRange, “criteria”)

The CellRange specifies the range of cells to be counted. If there is only a
single column of imported text, this might be $A$3:$A$2154. Or it could
include multiple columns if the text fields are longer than 255:
$A$3:$C$2154.
Once you’ve specified the range, the trick is to create criteria consisting of
keywords and phrases that match the cells. To do this, you’ll need to use
Excel’s wildcard character, the asterisk (*). To match a cell that contains a
keyword, the criteria portion of the COUNTIF statement will need to look
for any leading stream of characters (*), the keyword, and any trailing stream
of characters (*). The simple way of expressing this in the COUNTIF state-
ment would be

=COUNTIF(CellRange,“=*keyword*”)
To make this easy to change, we might consider putting the keyword in one
cell by itself and including it into the formula. The formula would be
=COUNTIF($A$1:$A$2154,“=*”&B1&“*”)
This would take the keyword from the cell above it, making it easier to
change and test various keywords. Getting the keyword right can make the
resulting data more accurate.

Consider the following example. Repair and help personnel are busy, so
they develop abbreviations for many common words and phrases. In phone
companies, LD means “long distance.” Credit can be abbreviated crdt. You may
need to scan some of the remarks to understand the most common abbrevia-
tions used.
Note in this example, that ld is also embedded in words like would or cold.
Simply counting ld would lead to inaccurate counts; so we can resolve this by
putting a leading space or blank in front of the abbreviation ld.
Similarly, notice that some words may be spelled out and some words may be
abbreviated like the word credit (cr*d*t). We can use the wildcard character to
handle these kinds of keywords, since the wildcard will match zero or more
characters.