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