IF Function

This article explains how to use IF functions

Sample Usage

IF(QA > 50, QB * 2, "N/A")


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 TRUE.
  • value_if_false - [ OPTIONAL - blank by default ] - The value the function returns if logical_expression is FALSE.


  • 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:


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.

    ConvertCalculator is a powerful calculator form builder for your website