In last week’s tip I shown you how to create and use a simple IF Function Tips – Using the IF function in Excel. Today I shall explain the ‘Nested Function’.
Nested means putting several together. So you can use more than one function in a formula.
Firstly, I need to establish a scenario:- Here we have a list of employees and as part of our job we need to establish how many days’ holiday they get dependent upon how many years they have worked for the company. And you know that as soon as they have reached each criteria they will demand that extra day or so to be credited straight away. Therefore, using the IF function is a great idea so as the calculation is done for you. Don’t forget you will need to use the =Today() or the =Now() within your function so that the calculation happens continually. (you won’t have to change any dates so that the calculation works).
I said that they will receive different amounts of holidays dependent upon their service length so we now need to have a breakdown of criteria.
- On a separate sheet create this table of information. A good point here would be to name this region. I shall be explaining this feature shortly.
This list shows you that people who have worked for the company as little as 1 day and up to 5 years get 10 days holiday per year. As soon as they have worked 1 day over the 5 years up until they reach 10 they get 15 days holiday. So then anyone who has worked over 10 years gets 22 days.
Our formula will need the <, > or = symbols. But where do we start. I like to start at the end. In other words, what is the most difficult to attain. The 10 years. If we were to do this in our heads we would naturally look at the length of service of the first person and ask ourselves if they have reached the most difficult criteria and if they have not, we would naturally then go down the list stopping at the one they have reached.
- Back on the first sheet In the cell K1 type ‘Length of Service’.
- In K1 type the formula =(TODAY()-G2)/365.25
This gives you the number of years they have served and part of the year (to the base 10 NOT Base 12 ). Autofill this formula down the sheet.
- In cell L1 type ‘Holidays’
In the cell L2 we are going to use the Insert Function feature to help us create a nested If function.
Select the button on the formula bar to access this.
- In the first line select the cell which has the length of service for the first person. Type in greater or equal to as I have done. Then select the cell that has the longest years of service on sheet 2.
- Move to the second line and select the second sheet again and select the cell one to the right of the longest years of service. Your dialog box should look like this.
Since we will autofill the final result in this cell down the sheet we need to use Absolute Referencing for two parts that won’t change in the formula. Refer to my earlier Tip Tips- Understanding referencing Excel
Your dialog box should look like this.
As you can see to the right of the formula in the first line of this dialog box we have a result of FALSE. So we know we have to look further.
- Select the third line in the dialog box and IMMEDIATELY select the IF in the function box to the left of the formula bar on the left hand side. This sometimes is a Name box but when creating formulae this changes to being a function box.
N.B. DO NOT select the drop down arrow as this will spoil your formula. Only select the word itself.
By selecting the IF in the function box your dialog box has been cleared. But look at the formula in the formula bar and you will see that the formula is continuing.
So we sort of start again, but this time selecting the middle of our three criteria. Your dialog box should look like this.
- Autofill down the sheet to see your results.
Excel suggests that you can have up to seven IFs in your nested formula. This means there are 8 possible answers. I would say that three is more than enough to work with if there are any errors. So what do you do if you do find that this formula has not worked. My suggestion is to delete the result and go for a cup of coffee then start from scratch. 99% of the time it will be fine the second time around. You will also not have wasted a month of Sundays looking for the tiny error in your formula.