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 <>
- 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
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%)
- 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.
- 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.
- 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
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))
- In the first section (C6 <= 10) the function checks whether C6 (the age of the person) is less than or equal to 10.
- 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.
- 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.
- 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).
- 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.
- Why should I buy neodymium magnets
- What are the current health problems
- What are you going to choose passion or skills
- How is London
- Why is the ocean color blue
- How do I find a turtle
- Typhoid spreads through sex or kissing
- What does vitamin D do
- What is beetle juice
- How does amoeba multiply
- Eating frogs spiders
- What is multi-label marketing
- How do volcanoes erupt?
- Why do we need supermarkets
- How about your perfect day
- The prostate MRI shows cancer
- What are the symbols of justice
- How do you define creativity creatively
- Why is the word uppity offensive
- Tee brand Nerf darts are good
- How long have you been reading Quora?
- Can i blog with godaddy?
- What operating system is used by data scientists
- How should I prepare for CLAT