Part II: Working with Formulas and Functions
Checking if Condition1 OR Condition2 are met
In Figure 13.6, we applied a discount to certain products based on their item number. In
this example, we want to expand the number of products eligible for the discount. As
before, only final assembly products will get the discount, but the departments will be
expanded to include both department 202 and department 203. Figure 13.7 shows the
inventory list and the new discount schedule.
=IF(AND(OR(LEFT(B3,3)="202",LEFT(B3,3)="203"),MID(B3,5,3)="
FIN"),10%,0%)
FIGURE 13.7
A revised discount scheme
We’ve expanded the conditional argument to the IF function to account for the changes in
the discount scheme. The AND function is restrictive because all of the arguments must be
TRUE for AND to return TRUE. Conversely, the OR function is inclusive. With OR, if any one
of the arguments is TRUE, the entire function returns TRUE. In this example, we’ve nested
an OR function inside the AND function—we’ve made it one of the arguments. Table 13.2
shows a truth table for how our nested functions work.
Cell D9 in Figure 13.7 shows a previously undiscounted product that receives a discount
under the new scheme. The OR section, OR(LEFT(B9,3)="202",LEFT(B9,3)="203"),
returns TRUE because one of its arguments returns TRUE.
