Microsoft Office Tutorials and References
In Depth Information
Exhibit 5.2 Exact function data entry comparison
of H2:I4 displays the results of all 9 comparisons. The comparison determines two
disagreements in data entry. Of course we are not in a position to suggest which
entry is in error, but we are aware that the entry resulting in BAD for both tables
must be investigated.
Regardless of the size of the data sets, the IF function can be written once and
copied to a range equivalent in size and dimensions to the data entry ranges. Thus,
if Otto’s data entry occurs in range A1:H98, then the data entry range for Maribel
could be A101:H198, and the comparison area could be in A201:H298. Our only
restriction is that the dimension of the ranges containing data must be similar; that
is, the number of rows and columns in the entry ranges must be the same.
A more direct approach to a comparison of data elements is the use of the Excel
cell function EXACT(text1,text2) . As the title implies, the function compares two
text data elements, and if an exact match is found, it returns TRUE in the cell, else it
returns FA L S E . Exhibit 5.2 compares the 6 data items and performs error checking
similar to that in Exhibit 5.1. Note that the third data element of the ﬁrst column and
ﬁrst of the second column are different (as before) and return a cell value result of
FALSE . All other cells result in a value of TRUE .
It is also wise to test data for values outside the range of those that are anticipated.
This is particularly true of numeric values. In order to perform statistical analysis,
we must convert qualitative variables (good, bad, male, female, etc.) to numeric
values; thus, if the numeric values are incorrect, the analysis will also be incorrect.
For example, it is easy to make a transcription error for data that must be converted
from a text value (e.g. gender) to a numeric value (male
Consider the data table shown in Exhibit 5.3, consisting of values that are antic-
ipated to be in the range of 1 to 6. We can use the logical IF function to test the
values occurring in the range of 1 to 6. But rather than testing for each speciﬁc
value (1, 2, 3,
, 6) by nesting multiple IF conditions and testing if the value is 1,