IF(QA > 50, QB * 2, "N/A")
IF(QA > QB, QA, QB)
IF(logical_expression, value_if_true, value_if_false)
logical_expression- An expression or reference to a cell containing an expression that represents some logical value, i.e. TRUE or FALSE.
value_if_true- The value the function returns if logical_expression is
value_if_false- [ OPTIONAL - blank by default ] - The value the function returns if logical_expression is
- Insure that valueiftrue and valueiffalse are provided to the function in the correct order - this is the single most common source of problems with IF.
The power of the IF
ConvertCalculator becomes really powerful when adding IF to a formula. Let's explain if by using an example:
I ask the user of two things:
QA. Desired finish of the product (Silver or Gold) QB. Amount of products they need
If the user wants more then 5 products I want to give a 20% discount. A silver product costs $ 100,- and a gold one $ 200,-.
To calculate the total price we need to do the following:
QA * QB
The problem is, if I need 10 products, the discount of 20% is not calculated. In order to apply the discount we need to change the formula to the following:
IF(QB > 5, QA * QB * 0.8, QA * QB)
The IF statement works like this:
IF(logical_test, value_if_true, value_if_false)
We can also chain IF statements together. Let's build on top of the previous example. We want to calculate the total weight of the products given the fact that silver products are 1 kg and gold products are 2 kg. We can do this a couple of ways. Let's use IF chaining to accomplish this:
IF(QA = 100, 1 * QB, "") & IF(QB = 200, 2 * QB)
You can read the above as this: "If the product is silver, return Product Amount _ 1kg, otherwise return "". If the product is gold, return Product Amount _ 2kg".
For this example it's maybe a bit overkill to using IF chaining, but there are a lot of use-cases for this.
Using AND or OR
You can use AND or OR functions in a formula to make it even more powerful. Let's take the above example but add an extra finish to QA (platinum). The discount we want to take of (when you buy more then 5 products) is not valuable on platinum. We need to use both AND and OR to accomplish this:
IF(AND(QB > 5, OR(QA = 100, QA = 200)), QA * QB * 0.8, QA * QB)
The above formula does this: If Product Amount is greater then 5 and product finish is either silver or gold, apply the discount, else don't apply the discount.