Using Nested IF Functions Effectively in Excel

Nested IF functions are a simple way to do a complex conditional formula. Nested IF functions allow you to use up to seven IF functions to impose conditions.

Essentially, the way a nested if statement would work is like this:

=IF("if this condition stated here is true", then enter "this value, else if("if this condition stated here is true", then enter "this value, else enter"this value"))

It looks quite long and imposing, but its really quite simple once you understand the concept. To illustrate, let's say you have a spreadsheet that you use to keep track of your sales force. The rate of commission each sales person receives is based on the amount of sales they have generated for that month. For example:

From $1 to $10 earns 10% commission
From $11 to $100 earns 15% commission
Anything over $100 earns 20% commission

Assuming the amount of sales is in column B, starting at row 4, and that the column containing the commission is formated for percentages, this is what the nested IF function would look like:

=IF(B4<=10,"10", if(b4<=100, "15", "20"))

This nested IF function says that if the cell B4 is less than or equal to 10, then put "10" in this cell (the commission), if the cell B4 is greater than 10, but less than or equal to 100, then put 15 in this cell. If the number in cell B4 is greater than 100, then put 20 in this cell.

This simplifies data entry for the spreadsheet as you now only need enter the amount of sales and the commission percentage is worked out for you. It also means there is less chance of making errors on the commission when entering the data.