Logical formulas – IF()

IF():

This formula allows for a choice of outcomes depending on whether a particular condition (or test)  is satisfied.  The formula has the following structure:

IF something is True, then do something, otherwise do something else.    We make these decisions in our everyday life e.g. if it is raining stay indoors otherwise go for a walk

Translating this into Excel speak we get

IF(Logical test is true, show value A, otherwise show value B) e.g. IF($A$15=”Yes”, B15, B15*2)

Taking these parts in turn:

The logical test in its simplest form is does this cell = something e.g. $A$15=”Yes”.  As with all things in excel this can be made more sophisticated. Examples are:

• Checking if one cell is equal to another cell: $A15= $B$2
• Checking if one cell is greater than another cell: $A15> $B$2
• Checking if one cell is greater than another cell: $A15< $B$2
• If two cells added together are greater than another cell: ($A15+$B15>$B$2
• Using with the logic AND formula: And(A15=”yes”, B15=”Yes”)=true
• Using with the logic OR formula: OR(A15=”yes”, B15=”Yes”)=true

Value A: Excel will do this if the logical test is true.  It can a text response (nb must be enclosed in double quote marks), a number or a formula.  You can nest other formulas into here including another if statement.

Value B: Excel will do this if the logical test is not true i.e. false.  It can a text response (nb must be enclosed in double quote marks), a number or a formula.  You can nest other formulas into here including another if statement.

EXAMPLE 1

In this example I have obtained some data which shows names and ages of a group of people. I needed to identify which of them were classed as a child (i.e. less than the age of 18) and which were adults (i.e. aged 18 and over).

To do this I used the formula highlighted in the red circle: =IF(C4<18,”Child”,”Adult”).

The C4<18 part is a logical test to see if the age (in column C) is less than 18

In this example I have added data on the gender of the people in my list (In Column D) and I wish to identify the people from my list which are both Female and an adult.

To do this I used the formula =IF(AND(D5=”F”,E5=”Adult”)=TRUE,”Yes”,”-“) shown in the formula bar.

In this case the logical test uses an AND formula AND(D5=”F”,E5=”Adult”)=TRUE. This checks that two conditions are both true i.e that the individual is both female and an adult