The “IF-THEN” function is a powerful tool in Excel which can help you to build dynamic spreadsheets which automatically sort your data for you or flag up issues.
It is a decision making function which tests your inputted data against different conditions and takes actions accordingly. For example, you could trigger rows of data to show a text message once they meet the desired criteria. This can help you to process and analyse your data more quickly and effectively in future, it may also help you create more visual spreadsheets which are easily understood by your whole team.
The IF-THEN Syntax
The syntax for an IF-THEN function looks like this:
=IF(logic test, value if true,value if false)
The formula starts by showing Excel you want it to perform an IF-THEN function. Inside the brackets, you start with the condition you want to test for. The next two parts of the syntax inside the brackets tell the programme what you want it to do if the condition does exist in the selected cells, and what to do if it doesn’t exist.
A Simple Example
=IF(E8<200,”GOOD”,”TOO LARGE”)
This basic form of IF-THEN argues the following:
“E8<200,” Tests whether the cell E8 has a value smaller than 200 or not.
“”GOOD”” Is the true value. It indicates that if E8 is smaller than 200, the text should read GOOD.
“”TOO LARGE”” Is the false value. means that if E8 is not smaller than 200, the text should read TOO LARGE.
Other considerations
If the text you want to return is either “TRUE” or “FALSE”, Excel will automatically recognise this. However, for any other text you want to display (as seen in the example above), you should use quotation marks on either side of it.
The condition you test for needs to be a comparison between two values. However, these values don’t need to be fixed numbers. They could be formats including dates, strings or numbers, times, or functions which ultimately display any of these.
There are also a range of comparisons you can use outside of “<” and “>”. Using “=” in the comparison is the next most common, but you could also use symbols such as “<=” to indicate “smaller than OR equal to”, or “<>” to mean “not equal”.
Using Calculations in IF-THEN functions
In cases where different calculations need to be performed depending on the value, you can also insert calculations into IF-THEN functions. To alter the previous example, this could look something like this
=IF(E8<200,E8*5%,E8*10%)
This would calculate 5% of the value of E8 if it’s less than 200, and 10% of it’s value if this is more than 200.
Nesting IF-THEN functions
Once you’re feeling confident with IF-THEN, you can move onto the more advanced formulas. These can nest several (up to 64!) functions inside each other to create more than two outputs and engage other formatting tools.
This is often used to assign grades, as shown below:
=IF(C6<90,”C”,IF(C6<95,”B”,”A”))
You can see here that instead of having one set outcome, the false value is an IF-THEN in itself, enabling three different outcomes.
Alternatively, you could look into using one of the newer Excel functions, such as XLOOKUP or IFS.
XLOOKUP can be a more flexible feature, as it searches through an array of cells for a specific item. With this tool there are also different search modes you can choose to use if, for example, you want to start the search in a specific position in the array.
IFS can be used as a simpler alternative to multiple nested IF statements. They allow you to set multiple outcomes for different conditions within the same statement and are often easier to input, read, and edit.
Interested in Knowing more?
IF-THEN functions can also be used with AND, OR, NOT, statements and much more. Our Microsoft Excel Training courses cover these and many other Formulae Control Structures. There’s a variety of levels and speciality courses to choose from to focus on where you and your team are at on your journey to mastering excel. Learn more here.
