Using IF Statements in Calculators

You're able to add IF statements in your formulas, these are logical comparisons that allow you to show one of two results based on whether the results is TRUE or FALSE.

For example, if you've created a pricing calculator you may want to apply a discount for those willing to spend more, you can create an IF statement to apply a percentage discount:

IF(Q_3180>500,Q_3180-(Q_3180*0.1),Q_3180)

Create IF Statements In Web Calculators

The formula translates as this: IF this question returns a value greater than 500, then the result is question value -10%, IF ELSE return the question value.

So, if the question returned a value of $708 then the result would be $637.20 (due to the 10% discount), where as if the question returned $300 the result would be less than 500 and so no discount would be applied.

Create IF Statements In The Simple Formula Builder

How to create an IF statement

Let's break down creating an IF statement into the basic steps.

1. Start your formula with IF.

IF(Q_3180>500,Q_3180-(Q_3180*0.1),Q_3180)

2. Define the first part of the IF statement

IF( Q_3180>500,Q_3180-(Q_3180*0.1),Q_3180)

The above example defines this as IF the value given by the first question is greater than 500.

3. Add a comma, then define the THEN result

IF(Q_3180>500 ,Q_3180-(Q_3180*0.1),Q_3180)

Now you need to define what happens if the result is TRUE, i.e. IF the value is greater than 500, what happens?

The first comma and following equitation define the result that will be shown in the above case.

4. Add a comma, then define the ELSE result

IF(Q_3180>500,Q_3180-(Q_3180*0.1) ,Q_3180)

Finally, you need to define what happens if the result is FALSE, i.e. IF the value is not greater than 500.

The second comma and following equitation define the ELSE result.

IF Statements with AND

IF statements with AND allow you to show one of two results depending on two or more values.

For example, if we keep the example of a pricing calculator from the last section, we can easily add the AND statement. Previously our pricing was only dependent on a single question, now we've added a second question to the scenario.

IF(AND(Q_3180>500,Q_3187>100),Q_3180+Q_3187-(Q_3180+Q_3187)*0.1, Q_3180+Q_3187)

Use IF AND Statements In Calculators

We understand this looks a little complicated, but we can easily break it down below.

How to create an IF statement with AND

Let's break down creating an IF statement with AND into basic steps.

As an example ,our formula is that of a pricing calculator where there are two factors: the cost of the plan and the cost of additional seats a person may want to add to that plan.

We want to build a formula where IF the cost of the plan is above $500 and the total cost of additional seats is above $100, then we want to apply a 10% discount on the total cost.

1. Start your formula with IF(AND

IF(AND(Q_3180>500,Q_3187>100),Q_3180+Q_3187-(Q_3180+Q_3187)*0.1, Q_3180+Q_3187)

2. Define the first part of your IF statement

IF(AND(Q_3180>500,Q_3187>100),Q_3180+Q_3187-(Q_3180+Q_3187)*0.1, Q_3180+Q_3187)

The above defines this as IF the value given by the first question is greater than 500.
So, in our example; IF the cost of the plan is above $500.

3. Add a comma, then define the AND statement

IF(AND(Q_3180>500,Q_3187>100),Q_3180+Q_3187-(Q_3180+Q_3187)*0.1, Q_3180+Q_3187)

This second part of the equation defines what other variable needs to be met for our discount to be applied.
In our case, the total cost of the additional seats needs to be above $100.
4. Add a comma, then define what result to show if both the IF & AND are true
IF(AND(Q_3180>500,Q_3187>100),Q_3180+Q_3187-(Q_3180+Q_3187)*0.1, Q_3180+Q_3187)
This part of the equation defines what result will show if both results are true, i.e. question 1 returns a value higher than 500 and question 2 returns a value higher than 100.
In our case this is a simple percentage calculation, which is essentially: Plan Cost + Seat Cost - (10%).
5. Add a comma, then define what happens if either the IF or AND are false

IF(AND(Q_3180>500,Q_3187>100),Q_3180+Q_3187-(Q_3180+Q_3187)*0.1,Q_3180+Q_3187)

Finally, you need to define what happens if the result is FALSE, i.e. IF the value of question 1 is not greater than 500 or the value of question 2 if lower than 100.
Tip: Don't forget to add the final closed bracket ) to the formula or it will generate an error.

IF Statements with OR

IF statements with OR allow you to show one of two results where only one variable needs to be met.

Again, if we keep the example of a pricing calculator from the previous sections, we can apply our discount based on whether they want to spend over $500 on the plan OR over $100 on the additional seats.

The formula for this would be:

IF(OR(Q_3180>500,Q_3187>100),Q_3180+Q_3187-(Q_3180+Q_3187)*0.1, Q_3180+Q_3187)

How to create an IF statement with OR

Let's break down creating an IF statement with OR into basic steps.

Continuing the previous example ,our formula is that of a pricing calculator where there are two factors: the cost of the plan and the cost of additional seats a person may want to add to that plan.

We want to build a formula where IF the cost of the plan is above $500 OR the total cost of additional seats is above $100, then we want to apply a 10% discount to the total cost.

1. Start your formula with IF(OR

IF(OR(Q_3180>500,Q_3187>100),Q_3180+Q_3187-(Q_3180+Q_3187)*0.1, Q_3180+Q_3187)

2. Define the first part of your IF statement

IF(OR(Q_3180>500,Q_3187>100),Q_3180+Q_3187-(Q_3180+Q_3187)*0.1, Q_3180+Q_3187)

The above defines this as IF the value given by the first question is greater than 500.
So, in our example; IF the cost of the plan is above $500.

3. Add a comma, then define the OR statement

IF(OR(Q_3180>500,Q_3187>100),Q_3180+Q_3187-(Q_3180+Q_3187)*0.1, Q_3180+Q_3187)

This second part of the equation defines what alternative variable can be met for our discount to be applied.
In our case, the total cost of the additional seats needs to be above $100.
4. Add a comma, then define what result to show if either results are true
IF(OR(Q_3180>500,Q_3187>100),Q_3180+Q_3187-(Q_3180+Q_3187)*0.1, Q_3180+Q_3187)
This part of the equation defines what result will show if either of results are true, i.e. question 1 returns a value higher than 500 OR question 2 returns a value higher than 100.
5. Add a comma, then define what happens if both variables are false

IF(OR(Q_3180>500,Q_3187>100),Q_3180+Q_3187-(Q_3180+Q_3187)*0.1,Q_3180+Q_3187)

Finally, you need to define what happens if the result is FALSE, i.e. IF the value of question 1 is not greater than 500 AND the value of question 2 if lower than 100.
In the case of our example, the cost would remain the same (no discount is applied).
Tip: Don't forget to add the final closed bracket ) to the formula or it will generate an error.

IF NOT Statements

IF NOT statements allow you to show one of two results depending on whether the value returned is TRUE or FALSE.

Following on with our pricing calculator example, we want to only give a discount if someone spends more than $1000.

So, if the values returned for both questions in our calculator do not return a value of 1000 or more, then no discount is applied.

For this the formula for this would be:

IF(NOT(Q_3180+Q_3187>=1000), Q_3180+Q_3187,Q_3180+Q_3187-(Q_3180+Q_3187)*0.1)

How to create an IF statement with OR

Let's break down creating an IF statement with OR into basic steps.

Continuing the previous example ,our formula is that of a pricing calculator where there are two factors: the cost of the plan and the cost of additional seats a person may want to add to that plan.

We want to build a formula where IF the cost of the plan is above $500 OR the total cost of additional seats is above $100, then we want to apply a 10% discount to the total cost.

1. Start your formula with IF(NOT

IF(NOT(Q_3180+Q_3187>=1000), Q_3180+Q_3187,Q_3180+Q_3187-(Q_3180+Q_3187)*0.1)

2. Define the first part of your IF statement

IF(NOT(Q_3180+Q_3187>=1000), Q_3180+Q_3187,Q_3180+Q_3187-(Q_3180+Q_3187)*0.1)

This is the part of the formula that states that the combined value of questions 1 and 2 need to be greater than or equal to 1000.

3. Add a comma, then define the THEN statement

IF(NOT(Q_3180+Q_3187>=1000), Q_3180+Q_3187,Q_3180+Q_3187-(Q_3180+Q_3187)*0.1)

This second part of the equation defines that the full cost should be shown if the result is NOT greater than or equal to 1000.
4. Add a comma, then define what happens if both variables are false
IF(NOT(Q_3180+Q_3187>=1000), Q_3180+Q_3187,Q_3180+Q_3187-(Q_3180+Q_3187)*0.1)
The final part of the equation defines what formula should be applied if the value returned IS greater than or equal to 1000. 
In the case of our example, a 10% discount would be applied to the price.
Tip: Don't forget to add the final closed bracket ) to the formula or it will generate an error.