The Dataset: Travel Claims
To demonstrate the IF, AND, OR formulas we’ll use a simple travel claim dataset shown below:
Let’s dive into how to build formulas that classify and evaluate these claims efficiently.
Basic IF Formulas
The IF function syntax is:
IF(logical_test, [value_if_true], [value_if_false])
Logical Test: this is a logical test/condition that must evaluate to TRUE or FALSE
Value if True: the value, text or formula you want returned it the logical test returns true.
Value if False: the value, text or formula you want returned if the logical test returns false.
Let’s start simple.
We want to classify claims greater than or equal to $4,000 as “High Value”, and everything else as “Standard”. Based on the example data table,
here’s the formula:
=IF(D6>=4000, "High Value", "Standard")
How it works:
- D6>=4000: This is the logical test. It checks if the claim amount is at least $4,000.
- If TRUE, return the text "High Value".
- If FALSE, return the text "Standard".
The result can be seen in column G of the image below:
Tip: Text values in IF formulas must be in double quotes.
Combining IF with AND
The IF function on its own can only handle one condition, if you need to layer in more conditions which all must be true, you can use the AND function.
For example, let’s say we want to auto-approve a claim if:
- The amount is less than $4,000, AND
- A medical report was provided.
First, let’s understand how the AND function works on its own.
The AND function enables you to evaluate multiple logical tests or conditions, which must evaluate to TRUE or FALSE.
The syntax:
AND(logical1,
[logical2], [logical3]…)
All logical tests must return TRUE for the AND function to return TRUE. If one or more logical tests return FALSE, the AND function will return FALSE.
Our two conditions in the AND formula look like this:
=AND(D10<4000, E10="Yes")
Remember, AND returns TRUE only if both conditions are met, as you can see in column G below:
Now let’s use AND with the IF function, notice below we simply insert the AND formula in the IF function’s logical test argument:
=IF( AND(D10<4000, E10="Yes"), "Auto-Approve", "Review")
Copy this formula down the column, and you’ll instantly know which claims to review and which ones to fast-track.
Using IF with OR
What if we want to apply logic where either of two conditions being true is enough?
Let’s say we want to offer a 70% early settlement for claims that are:
- A first-time claim, OR
- The person was abroad for fewer than 10 days.
That’s where the OR function comes in handy. The OR function enables you to evaluate multiple logical tests or conditions which must evaluate to TRUE or FALSE.
The
syntax:
OR(logical1, [logical2], [logical3]…)
Any one logical test must return TRUE for the OR function to return TRUE. Only when no logical tests return TRUE will the OR function return FALSE.
Here’s the formula:
=IF( OR(C10="Yes", F10<10), D10*0.7, "")
What it does:
- If
either condition is TRUE, it calculates 70% of the claim.
- If not, it returns a blank ("").
Note: Since we’re returning a number and not a text
string, we don’t wrap D10*0.7 in quotes.
Tips for Writing Better IF Formulas
- Use the function icon (fx) left of the formula bar for argument help.
- Both AND and OR can take up to 255
conditions, though if you have more than 7, there’s probably a more efficient solution.
- Excel supports a variety of comparison operators you can use in your conditions enabling you to flexible logic checks, including these:
- = Equal to
- <> Not equal to
- > Greater than
- < Less than
- >= Greater than or equal to
- <= Less than or equal to
IF Formula Builder
If you’re in a hurry and just need an IF formula written, try our IF Formula Builder. It does the hard work of creating IF formulas for you.
Just enter a few pieces of information, and the workbook creates the formula for you. Get it free here: IF Formula Builder.
Learn With Us