If-then function in Excel: Application Examples +

The if-then function in Excel is used to perform certain actions in cells when a self-defined condition is true. How to use them, I tell you here with examples.

If-then function in Excel: Application Examples +

The if-, or if-then-function of Excel is used as the formula to individual cells, columns or rows. The general structure is as follows:

= IF (CONDITION; THEN; OTHERWISE)

The rules for the if-then function in Excel are:

  • So always starts with the equal sign.
  • Then follows uppercase IF.
  • After that come the opening and closing parenthesis, the actual condition is between those for arithmetic.
  • The parts inside the parentheses are separated by a semicolon.

If Then function in Excel: Data output

I start with a simple formula. For this purpose we imagine even a very simple case. In a list of students each note is entered.

The if-then function in Excel will then issue behind it if the student was transferred or not. The limit for displacing the note. 4 Everything worse (ie larger) than 4 remains seated.

So the formula is in the plain text about:

If the value in cell X is less than 5, give off YES, otherwise give off No

excel if then formulaWhich then function in Excel If, as a result perform calculations or text output

it is now only the student's name, then the note, as it is automatically in column 3 if the pupil is promoted or not. of Note 1 to 4 is there Yes, at 5 and 6 is there No. And the formula would be structured as follows. Which I assume times that the note is entered in column E. This formula can be copied and transferred to other cells.

= IF (E3 <5;&# 8220; Yes&# 8221 ;;&# 8220; No&# 8221;)

So here you see the three components of the formula. The condition in red, What happens in green and what else happened in Orange. Text to be output, in order for it does not lead to errors and misinterpreted, will always be quoted. This can also be a complete set. Everything within the quotation marks is one of this formula section. What emerges, we see in the picture above.

For testing, we can use a variety of conditions. There would be:

  • = Equal or unequal <>
  • Smaller < or greater >
  • Less than or equal <Or = Greater or equal >=

mostly to get in different ways to the goal. In the above formula, your check whether a value is less than 5 (where we can incidentally also use figures with digits of the fraction) and in the case the THEN result occurs. One might have also reverse that. So check if the value is greater than 4, and in this case as THEN value &# 8220; No&# 8221; put. The result is theoretically the same. But if you'll notice the following exactly hinguckt you: In the first example you will also offset with a 4.9. In the second example Figure 4 is a hard limit. This is not something you should think about before!

If Then function in Excel to apply to a range

If we would like to apply a formula equal to an input area, then it must be the only defined in the formula. In the case, it is in the condition of the region as the start and end cell, separated by a colon:

= IF (E3: E6<5;&# 8221; Yes&# 8221 ;;&# 8221; No&# 8221;)

It's worth it when you know exactly the source data area before. Otherwise Can you first of all enter calm the data and then copy the formula from the first result cell to cell in a row.

If Then nested function in Excel

Here you take time to another case. You live a cash book and have to manage the entry. By the age of 12, the entrance fee is 3.00 euros. From 12 to 20, the entrance fee is 5.00 euros. All about pay 8.00 euros. Simply right?

So how do you check? With only two conditions, with a limit of 12 that would be easy. The first condition would &# 8220; all less than or equal to 12 pays 3&# 8243 ;. The OTHERWISE would be: &# 8220; All others pay 8 euros&# 8221 ;. So something like this:

= IF (B2<= 12; 3; 8)

But you must yes Check two conditions before you come to a OTHERWISE. Technically, this happens like this: First, you check the first condition and determines what happens.

So &# 8220; B2<= 12; 3&# 8243 ;. But behind your moves a further examination and puts it in the place that is otherwise intended for the third part of the formula. This looks like this:

= IF (B2<= 12; 3; IF (B3<= 20; 5; 8))

If you want to this if-then translate function of Excel plain text, can be expressed this way:

  • If the value in the cell is less than or equal to 12, then set 3 a.
  • but if he is also still less than or equal 20, put a fifth
  • Otherwise, set 8 a.

There, the first condition is therefore only tested. Thereafter, it is checked whether the value is below a second condition falls (all 13 to 20). If so, then there is a second issue. And if neither of these conditions is true, then only the OTHERWISE case occurs.

excel if then nested formulaThe If Then function in Excel performs calculations even with multiple conditions

This was just a brief introduction and a suggestion of what you could do with this formula function. It is of course much more possible and also the nesting is expandable. Begin once with calculations that can not even understand it at a glance.

14.11.2017

Posted In: software

map