Microsoft Office Tutorials and References
In Depth Information
4.3.3 Data Conversion with the Logical IF
Next, we deal with the conversion of a field value from one form of an alpha-numeric
value to another. Why convert? Often data is entered in a particular form that appears
to be useful, but later the data must be changed or modified to suit new circum-
stances. Thus, this makes data conversion necessary. For example, we often collect
and enter data in the greatest detail possible (although this may seem excessive),
anticipating we might need less detail later. How data will be used later is uncer-
tain, so generally we err on the side of collecting data in the greatest detail. This
could be the case for the quantitative data in the payment example in Table 4.1.
This data is needed for accounting purposes, but we may need far less detail for
other purposes. We could categorize the payment transactions into various ranges,
for example $0–$250. Later these categories could be used to provide specific per-
sonnel the authority to make payments in specific ranges. For example, Mary is
allowed to make payments up to $1000 and Naomi is allowed to make payments up
to $5000. Setting rules for payment authority of this type is quite common.
To help us with this conversion, I introduce one of Excel’s truly useful cell func-
tions, the logical IF . I guarantee that you will find hundreds of applications for the
logical IF cell function. As the name implies, a logical IF asks a question or exam-
ines a condition. If the question is answered positively (the condition is true ) then
a particular action is taken; otherwise (the condition is false ), an alternative action
is taken. Thus, an IF function has a dichotomous outcome: either the condition is
met and action A is taken, or it is not met and action B is taken. For example,
what if we would like to know the number of observations in the payment data
in Table 4.1 that correspond to four categorical ranges which include: $0–$250;
$251–$1000; $1001–$2000; $2001–$5000. As we suggested above, this informa-
tion might be important to assign individuals with authority to execute payment for
particular payment categories. Thus, payment authority in the $2000–$5000 range
may be limited to a relatively few individuals, while authority for the range $0–$250
might be totally unrestricted.
The basic structure of a logical IF cell function is: IF (logical test, value if true,
value if false) . This structure will permit us to identify two categories of authority
only; for example, if a cell’s value is between 0 and 500 return Authority 1 , otherwise
return Authority 2 . So how do we use a logical IF to distinguish between more than
two categorical ranges? The answer to this question is to insert another IF for the
value if false argument. Each IF inserted in this manner results in identifying an
additional condition. This procedure is known as nested IF’s . Unfortunately, there
is a limit of 7 IF functions that can be nested, which will provide 8 conditions that
can be tested.
Let us consider an example of a nested IF for the payment ranges above. Since
there are 5 distinct ranges (including the out of range values greater than or equal
to $5001), we will need 4 (one less than the number of conditions) IF’s to test for
the values of the 5 categorical ranges. The logic we will use will test if a cell value,
$ Amount , is below a value specified in the IF function. Thus, we will successively,
and in ascending order, compare the cell value to the upper limit of each range.
Search JabSto ::

Custom Search