Microsoft Office Tutorials and References
In Depth Information
Using the AND Function to Check for Two or More Conditions
Note
Mathematicians would correctly note that in both the second and
third arguments of the formula =IF(H2>=50%,0.025*F2,0.015*F2), you
are multiplying by F2. Therefore, you could simplify the formula by
using =IF(H2>=50%,0.025,0.015)*F2.
When you read an IF function, you should think of the first comma as the
word then and the second comma as the word otherwise. For example,
=IF(A2>10,25,0) would be read as If A2>10, then 25; otherwise, 0.
Figure 12.1 calculates a sales commission. The commission rate is 1.5 percent
of revenue. However, if the gross profit percentage is 50% or higher, the com-
mission rate is 2.5 percent of revenue.
Figure 12.1.
Figure 12.1. In rows 2, 4, and 5, the commission is 1.5%. In rows 3 and 6 through
In rows 2, 4, and 5, the commission is 1.5%. In rows 3 and 6 through
9 the commission is 2.5%.
9 the commission is 2.5%.
In this case, the logical test is H2>=50%. The formula for whether that test
is true is 0.025*F2. Otherwise, the formula is 0.015*F2. You could build the
formula as =IF(H2>=50%,0.025*F2,0.015*F2).
Using the
Using the AND
Function to Check for Two or More Conditions
The previous example had one simple condition: If the value in column H was
greater than or equal to 50%, the commission rate changed.
However, in many cases you might need to test for two or more conditions. For
example, suppose that a retail store manager offers a \$25 bonus for every
AND Function to Check for Two or More Conditions
Search JabSto ::

Custom Search