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