Excel if-then-else: how the IF function works

  • Whatsapp

A conditional formula is a formula that will perform an operation only if the criteria or condition(s) is met. The IF function is a conditional formula that is written in the form If..else.., where if the condition(s) is met then the action will be executed, but if one condition is not met then another action will be carried out.

Common conditional sentence are:

If (condition1)

{

Do something (value_if_true);

}

Else

{

Do something (value_if_false);

}

Conditional sentence can also be contained in other conditional sentence (if nested).

The nested if form, is:

If (condition1)

{

If (condition1.1)

{

Do something (value_if_true);

}

Else

{

Do something (value_if_false);

}

}

Else

{

If (condition2.1)

{

Do something (value_if_true);

}

Else

{

Do something (value_if_false);

}

}

If you want to use excel formulas with specific condition(s), you can use the IF function in Excel. In general, the IF function in Excel also has the same logical form as in the IF explanation above;

How to write the IF function in Excel is as follows:

= IF (condition, value_if_true, value_if_false)

The description of the above function is as follows:

condition: It is a requirement to use logical/mathematical operators (condition can be true or false). Mathematical operators that can be used are: <,>, <>, =,> =, <=.

value_if_true: The value to return if the condition is true

value_if_false: Value to return if the condition is false

Examples of using IF in Excel:

Example of a Simple IF Formula

In the Status1 column, if the average score of Exam1 and Exam2 is greater or equal to 6 then the status will be Pass, otherwise Fail.

The formula written in G3 is: = IF (AVERAGE (C3: D3)> = 6, “Pass”, “Fail”)

How the IF function in Excel

Example of an IF formula using the AND operator with one Condition or More

In column H, if the average value is greater than 6 and the height is more than 170 cm, then the status is Pass, otherwise Fail.

The formula written in H3 is: = IF (AND (AVERAGE (C3: D3)> = 6, E3> 170), “Pass”, “Fail”)

IF function in Excel

Example of an IF formula using the OR operator with one Condition or More

Furthermore, the Status3 example, results a Pass if either Exam1 or Exam2 are value more than 5, otherwise it is Fail. This is as shown in the formula in column I.

The formula written in I3 is: = IF (OR (C3> 5, D3> 5), “Pass”, “Fail”)

How the IF function in Excel

Examples of IF Formulas in IF (Nested IF)

Furthermore, nested IF examples are in Status4 and Status5.

In the example Status4 or column J the value of Pass or Fail is generated from the following formula:

J4 = IF (AVERAGE (C3: D3)> = 6, “Pass”, IF (D3 = 10, “Pass”, “Fail”))

How the IF function in Excel

Whereas in the example Status5 or column K the Pass or Fail value is generated from the following formula:

K3 = IF (AVERAGE (C4: D4)> = 6, IF (ROUNDDOWN (YEARFRAC (F4, TODAY (), 1), 0)> 20, “Pass”, “Fail”), IF (D4 = 10, ” Pass “,” Fail “))

How the IF function in Excel

IFS Formulas (Excel 2019 or Microsoft 365)

In general, IFS is formulated as follows:

= IFS ([Something is True1, Value if True1, Something is True2, Value if True2, Something is True3, Value if True3 … up to 127 different conditions)

An example of using the IFS function, I took from Microsoft’s site:

How the IFS function in Excel
How the IF function in Excel
How the IF function in Excel

Thus the use of conditional formula in Excel, hopefully, this will be useful for those of you who are learning the use of conditional formula in Excel…

Gravatar Image

Related posts

Leave a Reply

Your email address will not be published. Required fields are marked *