Microsoft Office Tutorials and References
In Depth Information
the cost of errors is high, then 100% inspection of the data may be necessary. For
example, data collected in the clinical trial of a drug may require 100% inspection
due to the gravity of the acceptance or rejection of the trail results.
So what capabilities does Excel provide to detect errors? In this section we exam-
ine a number of techniques for verification: (1) do two independent entries of similar
data match and, (2) do data entries satisfy some range of characteristics? We will
begin with a number of cell functions that permit the comparison of one data entry
in a range to an entry in another range. Let us first assume a data collection effort for
which data accuracy is of utmost importance. Thus, you employ two individuals to
simultaneously key the data into two Excel worksheets. The entry is done indepen-
dently and the process is identical. Once the data is keyed into separate worksheets
of a workbook, a third worksheet is used to compare each data entry. We will assume
that if no differences are found in data entries, the data is without error. Of course, it
is possible that two entries, though identical, can both be in error, but such a situation
is likely to be a rare event.
This is an ideal opportunity to use the logical IF cell function to query whether
a cell is identical to another cell. The IF function will be used to test the equality
of entries in two cells, and return the cell value OK if the test results are equal,
or BAD if the comparison is not equal. For simplicity’s sake, assume that we have
three ranges on a single worksheet where data is located—the first is data entry by
Otto, the second is the identical data entry by Maribel, and the third is the test area
to verify data equality . See Exhibit 5.1 for the data entries and resulting test for
equality.
Note Otto’s data entry in cell B4 and the identical data element for Maribel in
E4. The test function will appear in cell H4 as:
E4,“OK”,“BAD”). (Note
that quotation marks must surround all text entries in Excel formulas.) The result of
the error checking is a value of BAD since the entries are not equal. The cell range
=
IF (B4
=
Exhibit 5.1
If function error checking of data
Search JabSto ::




Custom Search