IF statements and nested IFs in Excel

The IF function allows us to specify the values a cell will take depending on whether a condition is true or false.

Let’s say that you 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.

Nested IFs

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:

=IF(D2<1999,”Pre-99″,IF(D2=1999,”99″,IF(D2>1999,”Post-99″)))

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, see the module Looking up Information in a Table and the subsequent modules of the unit Lookup and Reference.

AAT members have free access to this and many other Excel Tips through their MyAAT account. To continue exploring more Excel tips please click here.

Paolo Lenotti is the Head of Marketing & PR at Filtered.com.