Microsoft Office Tutorials and References
In Depth Information
Yes
Assign A
<= $250
No
Yes
Assign B
<= $1000
No
Yes
Assign C
<= $2000
No
Yes
Assign D
<= $5000
No
Value out of
Range
Exhibit 4.6
IF logic for payments categories
Exhibit 4.6 shows the logic necessary to designate each range with an alphabetic
value, A–E . Note that if we do not nest the IF’s in successively increasing value,
we run the risk of prematurely assigning an alphabetic value that is indeed true,
but not as accurate as it could be in assigning categories. For example, if we make
the second comparison <
$1000, a value of $856 would
be assigned a value at the second condition rather than the third. Clearly the third
condition is preferred, but unavailable due to the structure of the conditions. Note
in Exhibit 4.6 that the last test can lead to a Value out of Range condition. This
condition is equivalent to E .
So how do we convert the logic in Exhibit 4.6 to a logical IF ?Wedosobynesting
each condition in the value if false argument of the IF’s . Assuming the cell value we
are testing is in a cell location such as D2, the cell formula will appear as follows:
=
$2000 and the third <
=
=
IF (D2 <
=
250, “A”, IF (D2 <
=
1000, “B”, IF (D2 <
=
2000, “C”, IF
(D2 <
=
5000, “D” “E”))))
The cell H2 in Exhibit 4.7 shows the IF function that performs the comparisons,
and returns an appropriate alphabetic value—A, B, etc. By reading the IF function
 
Search JabSto ::




Custom Search