Logical Function OR, AND, IFERROR

Logical function help to analyze the data by applying condition in earlier post we learned IF & AND Statement and how to build nested functions.

OR Functions - To check or satisfy the multiple conditions or any one of the condition out of multiple conditions that we have to analyze the report or data. 

Let us take OR function to work upon.

 Above example demonstrates calculating the incentives where Sales Rep achieving the minimum target sales of $9000, $7000 & $4000 would be awarded the incentives as the conditions mentioned, let us now elaborate the formula

=IF(OR(D5>9000,E5>9000),E5*10%,
IF(OR(D5>7000,E5>7000),E5*7.5%,
IF(OR(D5>4000,E5>4000),E5*5%,0)))

If sales rep 
"Gross Sales or Net Sales" is Greater than $9000 then the Incentive is calculated the Net Sales * 10%, if this condition does not match then look for other alternative

"Gross Sales or Net Sales" is Greater than $7000 then the Incentive is calculated the Net Sales * 7.5%.

"Gross Sales or Net Sales" is Greater than $4000 then the Incentive is calculated the Net Sales * 5%.

No Incentives for Sales Rep who performed the sales below $4000.

Look using Two logical function (IF, OR) we can get the desired result. 

Even we can use the AND function instead of OR in above example when we need to satisfy both  conditions.


=IF(AND(D5>9000,E5>9000),E5*10%,IF(AND(D5>7000,E5>7000),E5*7.5%,
IF(AND(D5>4000,E5>4000),E5*5%,0)))

IFERROR Function - IF ERROR function used to display specific value or character when formula throws result as #N/A, #DIV/0!, #VALUE! error when we apply the formula. For example

If we use VLOOKUP function to look for matching value and get the relevant value, if look up value not found in lookup range, vlookup throws #N/A error as result. And to get rid of such error we can add IFERROR Function. Let us study this function on above example.


=IFERROR(IF(AND(D5>9000,E5>9000),E5*10%,IF(AND(D5>7000,E5>7000),E5*7.5%,IF(AND(D5>4000,E5>4000),E5*5%,0))),"No Incentive")