Setting up conditions to change outcome/IF function

To change the outcome or result of data according to certain conditions like ‘If ○○, then A, if not ○○, B’, we use the following function.

=IF(logical_test, [value_if_true], [value_if_false])

[logical_test] = Input formula describing the condition.
[value_if_true] = Input result when the [logical_test] is ‘TRUE’.
[value_if_false] = Input result when the [logical_test] is ‘FALSE’.

Definition of function A function that changes the outcome according to the [logical_test]
EXCEL version Excel2003 ・ Excel2007 ・ Excel2010 ・ Excel2013・ Excel2016

 

Example of use 1) Select a condition for empty spaces and characters

The following list shows the snow making situation in ski resorts located in New York.
If the snow making situation is good, the word ‘Yes’ appears in the column ‘Ski condition’ (whether it is possible to ski) but if short, nothing appears (empty space).
We will use the IF function to create formulas for the 2 conditions below:

① If the snow making condition is ‘Good’, display the word ‘Yes’ in the ski condition column (column D).
② If there is an empty space in the ski condition column, display the word ‘Closed’ in column E.

① If the snow making process is ‘Good’, display the word ‘Yes’ in the ski condition.

(1) [logical_test] : The condition is “if the snow making process (=cell C4) is ‘Good'”, so we input the following. → C4=”Good”
(2) [value_if_true] : If the [logical_test] is true (i.e. if cell C4 is ‘Good’) we display the word ‘Yes’, so we surround the word ‘Yes’ with “(double quotations) as follows. → “Yes”
(3) [value_if_false] : If the [logical_test] is not true, then we keep the cell empty (“”). → “”
=IF(C4=”Good”,”Yes”,””)

② If the ski condition column is blank, display the word ‘Closed’ in the resort situation column.

(1) [logical_test] :  The condition is “if the Ski condition (=cell D4) is empty”, so we input the following.
→ D4=””
(2)[value_if_true] : If the [logical_test] is true (i.e. if cell D4 is ’empty’) we display the word ‘Closed’, so we surround the word ‘Closed’ with ” (double quotations) as follows. → “Closed”
(3)[value_if_false] : If the [logical_test] is not true, then we keep the cell empty (“”). → “”
=IF(D4=””,”Closed”,””)

 

Example of use 2) Select a word range that 【includes ~】/Using the wildcard character

To set up a condition like ‘if it includes the word ○○…’, we can combine the IF function and the COUNTIF function.
→For more information over the COUNTIF function, click here

The following is the employee list for a company. The company has decided to provide a summer bonus for the sales team only. We will use the IF function in column D and mark a ‘○’ to show which employees qualify for the bonus.

① If the word ‘Sales’ is included in the Department name, mark a ‘○’, and if not, mark a ‘×’.

(1) [logical_test] : As we cannot use wildcard characters in this part, we first set up a condition with the COUNTIF function that “counts when cell B4 is ‘Sales ~’“.

COUNTIF(B4,“Sales*”)

If if results to being ‘bigger than “0” (zero)’ …in other words if B4 can be counted, then it shows that the employee does work in the ‘Sales’ department.

COUNTIF(B4,”Sales*”)>0

(2) [value_if_true] : If the  [logical_test] is true, (i.e. if B4 is bigger than ‘0’), we mark a ‘○’, so we input the following. → “○”
(3) [value_if_false] : If the [logical_test] is not true, we mark a ‘×’. → “×”

=IF(COUNTIF(B4,”Sales*”)>0,”○”,”×”)

By inserting other functions in a function (this is called nesting), we can expand our selection of [logical_tests], [value_if_true] and [value_if_false] in many ways.

We will look at nested functions with more detail below.

 

Setting up multiple conditions/Nested functions

Example of use 1) To set up multiple conditions

The table below shows the results of a national examination. On the right side, we have the ‘Average’ and ‘Total’ scores which help us decide their overall results (either a pass or a fail).
We will use the IF function to extract the overall results (J column). It will be a ‘Pass’ when both the conditions below are met, and if not, a ‘Fail’.

・Average score is greater than or equal to 50
・Total score is greater than or equal to 350

 

① Display the word ‘Pass’ if average score is greater than or equal to 50 and the total score is greater than or equal to 350, and ‘Fail’ for all others.

(1) [logical_test] : Set up a formula that shows that the “average score (cell H5) is greater than or equal to 50″ → H5>=50
(2) [value_if_true] : If the [logical_test] is true, the next condition we would create would be “if the total score (cell I5) is greater than or equal to 350, display the word ‘Pass’, but if not, a ‘Fail’“.

IF(I5>=350,“Pass”,“Fail”)

(3) [value_if_false] : If the [logical_test] is false, display the word ‘Fail’. → “Fail”

=IF(H5>=50,IF(I5>=350,”Pass”,”Fail”),”Fail”)

The formula seems complicated and long, but using nesting like above helps us to set up multiple conditions.

Image for reference)

 

Example of use 2) Select number range/set up an “or” condition

Sometimes we might want to set up a condition of “either one or the other…” like ‘greater or equal to 60 points, or, smaller or equal to 90 points’.
In this case, we use the OR function, which allows us to set up multiple conditions.

=OR(logical1, logical2, [logical3]…)

[logical1] = Set up the 1st conditional formula.
[logical2] = Set up the 2nd conditional formula.
[logical3…] = Not obligatory. Set up the 3rd conditional formula.

Definition of function A function to set up multiple conditional [logical] tests
EXCEL version Excel2003 ・ Excel2007 ・ Excel2010 ・ Excel2013・ Excel2016

 

We will use the same table to work out the following. This time, a ‘Pass’ will be according to the conditions below.

The average score is greater than or equal to 50
or,
the total score is greater than or equal to 250

 

① If the average score is greater than or equal to 50, or, if the total score is greater than or equal to 250, display the word ‘Pass’. If neither of the conditions are met, display the word ‘Fail’.

(1) [logical_test] : Using the OR function, set up the 2 conditions.

OR(H5>=50,I5>=250)

(2) [value_if_true] :  If the [logical_test] is true, display ‘Pass’. → “Pass”
(3) [value_if_false] :  If the [logical_test] is false, display ‘Fail’. → “Fail”

=IF(OR(H5>=50,I5>=250),”Pass”,”Fail”)

 

Example of use 3) Select number range/set up an “and” condition

To set up a condition that meets “both conditions” like ‘greater or equal to 60, and smaller than 90’, we use the AND function.

=AND(logical1, logical2, [logical3]…

[logical1] = Set up the 1st conditional formula.
[logical2] = Set up the 2nd conditional formula.
[logical3…] = Not obligatory. Set up the 3rd conditional formula.

Definition of formula A function to set up multiple conditional [logical] tests
EXCEL version Excel2003 ・ Excel2007 ・ Excel2010 ・ Excel2013・ Excel2016

 

We will use the same table again to find out what will happen when the conditions to pass are changed to the following.

The average score is greater than or equal to 50
and,
the average score is greater than or equal to 350

① If the average score is greater than or equal to 50, and if the total score is greater than or equal to 350, display the word ‘Pass’. If the 2 conditions are not met, display the word ‘Fail’.

(1) [logical_test] : Using the AND function, set up the 2 conditions.

AND(H5>=50,I5>=350)

(2) [value_if_true] : If the [logical_test] is true, display ‘Pass’. → “Pass”
(3) [value_if_false] : If the [logical_test] is false, display ‘Fail’. → “Fail”

=IF(AND(H5>=50,I5>=350),”Pass”,”Fail”)

 

POINT!

The AND function and the OR function are called ‘logical functions’ in Excel, and cannot be used independently. ‘Logical functions’ helps us to explore data with more detail by combining them with functions that “can set up conditions“, like the IF function.