Nesting One Function Inside Another Function
When you first start using functions, you'll probably use a single function in a cell. But as you become more experienced with functions, you'll find you sometimes need to use multiple functions to perform the calculations you want.
When you need to use two or more functions together, you can nest one function inside another function. For example, if you need to return the current hour, you can nest the NOW() function (which returns the current date and time) in the HOUR() function (which returns the hour from a given time in 24-hour format; change the cell format if you need the hour in AM/PM format):
=HOUR(NOW())
As with formulas, you can nest functions many layers deep if you need to. Here's an example with three layers of nesting:
=INT(AVERAGE(ROUND(SUM(C1:C6),2),ROUND(SUM(D1:D6),2)))Here's what happens:
- SUM(C1:C6). This SUM function adds the values in the range C1:C6. Similarly, SUM(D1:D6) adds the values in the range D1:D6.
- ROUND(SUM(C1:C6),2). This ROUND function rounds the result of the SUM(C1:C6) formula to two decimal places. Similarly, ROUND(SUM(D1:D6),2) rounds the result of the SUM(D1:D6) formula to two decimal places.
- AVERAGE. This function returns the average of the results returned by the two ROUND functions.
- INT.This function returns the integer portion of the result from the AVERAGE function.
TIP: Nesting functions makes for compact spreadsheets and can impress your colleagues. But when you're working out which functions to use, you may find it easier to put each function in a separate cell so that you can see the result each returns—and perhaps add comments explaining what each step does. Once you've got the functions working in separate cells, create the nested version in a single cell, and make sure it gives the same result as the step-by-step version. Again, you may want to add a comment explaining how the nested formula works.