This is the classical concurrent condition: if all is true then proceed. You do it with an Excel IF function combined with AND function.
Imagine the following example: you will give a 6% bonus to each salesman who reaches three conditions (see graphic 1):
- Product dealer is HP
- Sales amount greater than 10,000
- Contract year is 2005
Well, let’s start building the formula…
The IF function syntax is this one: IF(logical_test,value_if_true,value_if_false)
In brief, the “logical_test” argument is based on three conditions (AND retrieves TRUE if each one is true).
In your own words, the formula would be:IF(n conditions are met with AND, do this, if not do this other thing).
In brief; the formula could be something like this (see results in column E, graphic 1):
You can build this logical formula by nesting IF functions in each “value_if_true” argument but this is the hardest way. See example below:
=IF(C2=”HP”,IF(B2>10000,IF(B2<100000,IF(YEAR(D2)=2005,6%,”Not applicable”),”Not applicable”),”Not applicable”),”Not applicable”)