Analyzing Business Data with Excel

Chapter 5. Measuring Quality

In the last two chapters we looked at measuring quantity. Now we turn to quality. Sampling and statistical analysis have been used to measure quality in manufacturing since the 1920s. Years ago it relied on small samples and simple calculations, but today we can do more. With modern technology we can look at thousands of pieces of work and the complexity of calculations is no longer an issue.

In this chapter we use Statistical Process Control techniques to build an application to measure quality. We will go through the techniques and calculations, and then use them to build an application. We will also look at the data requirements for this kind of project.

In earlier projects we used VBA to make the application interact with the user, but Excel can interact without VBA. This project uses controls to bring the application to life. Controls are easy to use and we will include four of them in the project.

The basic problem is always the same. We start with data that contains the answer to a question. We find the answer and give it to the user.

This chapter uses the following Excel functions and features, shown in Tables 5-1 and 5-2.

Table 5-1. Excel functions used in this chapter

INDEX( ) INDIRECT( ) ADDRESS( ) INT( )

ROW( ) MAX( ) STDEV( ) SUM( )

IF( ) AVERAGE( ) LN( ) EXP( )

The INDEX, INDIRECT, and ADDRESS functions are vital to understanding the application, and they are explained in Chapter 1.

Table 5-2. Excel features used in this chapter

Controls