Hands on: Using Excel's nested IF function

Nigel Halliday has an Excel spreadsheet listing members of a junior cricket club including a column for date of birth.

Another column shows the age group they play in: under 13; 13 and 14; 15 and 16; and 17 and over.

Calculating the age group based on date of birth is easy; however, the applicable age group needs to be based on the age as of 1 September 2005. So how can you calculate each player’s appropriate age group?

One way is to use nested IF functions. Lay out your table with each player’s name in column A, date of birth in column B and the parameters of the groupings in the range: F4 to F7. Column E is left blank so Excel will recognise columns A: D as a table.

Enter the significant date, in this case 1/9/2005, in cell B1.

On the Tools menu, choose Add-ins and check the Analysis Toolpak option. This makes extra functions available including YEARFRAC. In cell C4 enter:

=YEARFRAC(B4,$B$1,1)

and drag this formula down column C. Displaying the age of each player to three decimal places in this way offers a check so you can confirm that each player is in the right group.

In cell D4 enter:

=IF(C4>=17,$F$7,IF(C4>=15,$F$6,IF(C4>=13,$F$5,IF(C4<13,$F$4))))

This formula means that if the age in cell C4 equals or is more than 17, display the contents of F7, ‘17 and over’.

If it equals or is more than 15, display the contents of F6, ‘15 and 16’. If it equals or is more than 13, display what is in F5, ‘13 and 14’.

Finally, if the age is less than 13, display the contents of F4, ‘Under 13’.

The IF function formula chugs down through the groups, filtering out the oldest cricketers at each step, and classifying the remainder. The names of the players have been entered alphabetically here.

Excel can rearrange the players into age groups and count how many are in each group.

Click anywhere in the table and, on the Data menu, choose Sort. In the dialogue box, choose DOB Descending.

Having sorted the players into chronological order, choose Subtotals on the Data menu and make entries in the dialogue box so it reads: At each change in Group, Use function Count, Add subtotal to Group, Replace current subtotals, and Summary below data. Format to taste.

Now you can produce summaries. Click in the table again and choose Group and Outline, Auto Outline on the Data menu and you’ll have a presentable and printable result.

At the top left you’ll have a choice of views: 1, 2 and 3. View 1 just shows the grand total. View 2 shows the subtotals of each group. View 3 displays the whole table.