IF Formula

This article explains how to use IF formulas

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)

Chaining IF's

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.