Microsoft Office Tutorials and References
In Depth Information
Exhibit 5.3 Out of range data check
2, 3
etc., we can employ another logical function, the OR function, to capture the
entire range and test whether a value is in or out of the range. The OR function is
used in Boolean logic, as are AND , NOT , FALSE, and TRUE . The combination of
these functions can be used for a test of the data entries in an arbitrary cell location,
say E4, by using the following logical conditions:
IF a value in cell B4 is less than 1 OR is greater than 6
then return the text “ OUT” to cell E4
else return the text “IN” to cell E4
Note that the results in Exhibit 5.3 cells E4 and F3 are OUT , since the cell values
...
B4
7 are outside the required range. Assuming the cell location B4
contains the data of interest in Exhibit 5.3, the IF function used to perform the
comparison in E4 is written as:
=
0 and C3
=
IF (OR(B4>6, B4<1),“OUT”, “IN”). Of course,
we could also replace the values 1 and 6 in the cell formula with cell references D6
and D7, respectively. This permits us to change the range of values in the future, if
the need arises, without having to change cell formulas.
What happens when we are anticipating integer values from the data entry pro-
cess and we instead encounter decimal values? The test above will not indicate
that the value 5.6 is an incorrect entry. We can, though, use another Excel Math
and Trig cell function, the MOD (number, divisor) function, to logically test for
a non-integer value. The MOD function returns only the remainder (also called the
modulus ) of the division of the number by the divisor —e.g. if the function argument
number is 5.6 and the divisor is 1, the function will return the value 0.6. We can then
include MOD as one of the tests in our IF function, just as we did with OR . It will test
for integer values, while the other OR conditions test for values in the range of 1 to 6.
The resulting function is now:
=
IF(OR(MOD(B4,1)>0,B4>6,B4<1),“OUT”,
“IN”). The first OR condition, MOD(B4,1)>0 , divides B4 by 1, and returns the
=
Search JabSto ::




Custom Search