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 ﬁeld 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 modiﬁed 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 speciﬁc per-

sonnel the authority to make payments in speciﬁc 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 ﬁnd 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 speciﬁed 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