Secrets of the Office Masters: Nesting Functions Within Functions

In some cases, it's necessary to use one function as the argument for another. Nesting functions within functions this way is common with logical functions such as IF, for example. In a sales worksheet that you use to calculate commissions at a regular rate of 5%, you might want to pass along an extra 2% bonus to salespersons who beat their quota in every quarter and pay no commission to those who averaged 10% or less than their target number for the year. If the quarterly quota for the first salesperson is in cell B3 and the actual sales for each quarter are in B4:B7, enter the following formula to perform the full calculation in a single step:

=IF(MIN(B4:B7)>B3,SUM(B4:B7)*7%,IF(AVERAGE(B4:B7)<B3,0,SUM(B4:B7)*5%))