Page 138 - Lean six sigma demystified

Chapter 4 exCeL Power TooLS for Lean Six Sigm a 117
? still struggling

Download and install the QI Macros from www.qimacros.com/demystified.html.
In My Documents, you will find QI Macros Test data loaded with examples from
manufacturing, healthcare, services, etc. Look for test data that is similar to your
own company’s data and use it as a template for your data.

Mistake-Proof Data Collection

People use Excel to create forms for all kinds of data collection: time sheets,
scorecards, even mini-databases. Unfortunately, when they try to analyze it with
pivot tables, they soon discover that humans are very creative spellers. One hos-
pital system abbreviated Medicare with various acronyms: MDCR, Medcr, Med-
icr, and so on. This makes it difficult to do any data analysis or mining without a
lot of cleanup. Excel’s Data Validation function can eliminate the confusion.

Data Validation with Excel

The travel department of a major company asked its travel agents to track
flights using dates, route codes (e.g., DEN-LAX-DEN), and destination cit-
ies. Travel agents found creative ways to
make the analysis difficult: incorrect dates,
swapping routes with destinations, misspell-
ing destinations, leaving the hyphen out of

the route (Fig. 4-10), and so on.
How can the travel group ensure that FIGURE 4-10 • Excel air travel log.
travel agents enter the data correctly? Data
validation. Simply select the cells in the col-
umn and then specify a format and content
for those cells.
In Excel 2000–2003, click on Data Valida-
tion (Fig. 4-11). In Excel 2007–2010, click
on the Data tab and choose Data Validation.
Excel will pop up a menu with various
choices: integers, decimals, dates, times, text,
list, or custom (Fig. 4-12). FIGURE 4-11 • Excel data validation.