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.
More Formulas and Functions in Excel 97/2000/2002XP/2003/2007
What Is An IS Function?
Ever wondered how to use the IS functions
in Excel? This article explains how to use IS and IF functions.
Excel Printing
Find out how to prepare your excel worksheet for printing and how to print large spreadsheets in Excel.
Merging Cells in Excel
See how to make your worksheets more "readable" by merging cells.
Excel 97/2000/2002XP/2003/2007 Full Tutorials
If you are interested in more advanced formulas and functionalities of Excel 97/2000/2002XP/2003/2007 Click on the link above
More Excel Functions
| If Function | AutoFill Formulas | IS functions |
Suggested Reading
Home | About Us | Our Services | Software | Spreadsheets Library | Excel Tutorials | Useful Links | Site Map | Contact Us
Unless otherwise stated, all images and text
Spreadsheet123
