What better tool than Excel to help us make decisions that are based on solid foundations?
And how about being able to improve both speed and accuracy of our work? That’s where IF and IS functions come in, helping us make good decisions using Excel’s logic.
Allow us to specify the values a cell will take depending on whether a condition is true or false. They are likely to come up a lot in Excel, as they allow us to test whether a condition is true or false, and perform a task based on this result, without the need for multiple functions.
Check the type of value of a cell and returns TRUE or FALSE depending on the outcome. A simple example of this type of function is ISNUMBER, which returns TRUE if the cell referred to is a number; otherwise it returns FALSE. You may use this to check that none of the numbers in your data are stored as text, which could cause problems when performing calculations.
Let’s say that we want to add a column to the top grossing films table indicating that a film grossed $2,000,000,000 or over by labelling it ‘Over’, and under two billion by labelling it ‘Under’. The formula would look like this:
=IF(C2>=2000000000, “Over”, “Under”)
The syntax of this function is: =IF(condition, [result if condition true], [result if condition false]), which results in:
Now Column E displays whether sales were more or less than $2,000,000,000 for each film.
Whereas IF() is used when the outcome includes only two possibilities – one value appearing if the condition tested is true, the other if false – nested IFs are used to choose between more than two results.
Continuing with the film table example, suppose you want to label all films released prior to 1999 as ‘Pre-99’; those released in 1999 as ‘99’; and those that came after as ‘Post-99’. Then the formula in E2 would look like this:
A word of caution
Nesting to more than three levels can be confusing, and there might be a better way to achieve what you want by possibly using a LOOKUP function instead. For more information, check the Excel course by Filtered – in particular the module Looking up Information in a Table and the subsequent modules of the unit Lookup and Reference.
ISNUMBER tests whether a cell contains a number or not.
Returns FALSE below because that cell contains text:
The function ISTEXT works the same way:
Next time you need to apply more logic to your spreadsheets, try these functions. They will save you time and ensure more accuracy.
The units Using Logic To Apply Conditions and Advanced Logical Tests from the Excel course by Filtered also include videos and interactive quizzes on this topic, helping you test your knowledge in real time.
Paolo Lenotti is the Head of Marketing & PR at Filtered.com.