If 1 + 1 10, then 1 + 3

Excel If Then Function

Aside from our reference article, the Excel If Then Function (or simply called an If Formula) is currently the most popular topic here on Learn Excel.

However, the function repeatedly causes difficulties for many Excel users. If then consists of three parts:

IF -> THEN -> ELSE

If then formula construction

IF: Condition / Check - Use these conditional characters:

  • Equal to = or not equal to <>
  • Smaller
  • Less than or equal to <= and greater than or equal to> =

THEN:If the condition is correct, what should be displayed

OTHERWISE: If the condition Not correct, what should be displayed

Written as a formula it looks like this: = IF (condition; then; else)

Excel If Then Formula Example # 1

Task:

You have a company and give a 3% discount if you pay within 30 days. After 30 days the regular price applies.

solution with if then function:

The problem is easy to solve with the If Then function. Simply enter the following formula in cells D6 - D9: = IF (C6 <= 30,3 %,0%)

  1. In the first section (C6 <= 30) the function checks whether C6 (in this case the number of days after which the payment was made) is less than or equal to 30.
  2. In the second step (; 3%;) the value is specified that should come out in the event that C6 is less than or equal to 30.
  3. In the third and last step (; 0%) you specify what in the event that C6 Not is less than or equal to 30, should be output

Excel If Then Formula Example # 2

Task:

The price for a flight ticket from Frankfurt to New York is staggered according to age groups. Children up to and including 10 years of age pay 300 euros, young people and students up to and including 25 years of age pay 400 euros and adults pay 500 euros. How can we use MS Excel to determine the respective price?

solution with if then function:

Similar to before, only that this time we have to examine several scenarios. You can solve this with a nested If Then function. Simply enter the following formula in cells D6 - D9:= IF (C6 <= 10,300, IF (C6 <= 25,400,500))

  1. In the first section (C6 <= 10) the function checks whether C6 (the age of the person) is less than or equal to 10.
  2. In the second step (; 300;) the value is specified that should come out in the event that C6 is less than or equal to 10.
  3. In the third step (IF (C6 <= 25) enter the 'If' again in order for the case that C6 Not is less than or equal to 10, a further test can take place. This checks whether C6 is less than or equal to 25.
  4. In the fourth step (; 400) you specify what should be output in the event that C6 is less than or equal to 25 (but not less than or equal to 10).
  5. In the fifth and last step (; 500) you specify what in the event that C6 Not is less than or equal to 25 (and also not less than or equal to 10), output so

Excel If Then Formula Example # 3 (Cell Reference)

In the following example we will replace the values ​​to be checked with variable cells. This allows you to quickly and easily adapt your Wenn Dann formulas and design dynamic Excel models.

The result has not changed, but the new formula is flexible and dynamic:= IF (C8 <= $ C $ 13; $ D $ 13; IF (C8 <= $ C $ 15; $ D $ 15; $ D $ 17))

The new cells (in the red box) are now your input cells, i.e. you can control the ticket price and the age levels directly via these new cells and do not have to change the original formula. Also notice that we applied the absolute cell reference for this formula. This allows you to simply copy the formula from cell D6 to D10 (here is an introduction to the subject of Excel cell references).

You can download the corresponding Excel exercise here.

176 Comments