Create a standard Formula using a Nested Function in Excel 2010


In the previous post, we guided you on how to create a simple formula using a function. In this post, we will guide you on how to create a formula using a Nested Function. Formulas created using Nested Functions includes a particular function as one of the argument of an outside function. Below, you will find a tutorial that will explain you how to use a Nested function for creating a formula with an illustration in Microsoft excel 2010 Application.

In our illustration, the formula created using a Nested Function will return the Average of two numbers present in cells B2 and B3 if and only if the Sum of two numbers present in cells B2 and B3 is more than 99, otherwise the formula will return a value ‘0’ in the cell. Here is the formula that we have used;

=IF(SUM(B2,B3)>99,AVERAGE(B2,B3),0)

Where, B2 contains a value ‘45’, B3 contains a value ‘55’ and the cell B3 contains a value ‘100’ in our illustration. When this formula successfully completes the calculation, a result ‘50’ should be returned according to the numbers taken in our illustration.


1) Click on the cell where you intend to create a formula using a Nested function. In our illustration we have selected the cell D2.

2) Then click on the ‘Insert Function’ icon present on the ‘Formulas’ tab, under the ‘Function Library’ group.


Figure 1



3) Once you click on the Insert Function, the excel application will insert an Equal (=) sign in the cell you selected and opens an ‘Insert Function’ dialogue box.


Figure 2



4) In the ‘Insert Function’ dialogue box, you have to enter the above mentioned formula. To enter the ‘IF’ function, click on the drop down arrow of ‘Or Select a category’ box and then choose ‘Logical’ option from the drop down list.


Figure 3



5) Then select the ‘IF’ function from the populated list and click on ‘OK’.


Figure 4



6) Now in the ‘Function Arguments’ dialogue box thus opened, type SUM(B2,B3)>99 in the ‘Logical Test’ box, type AVERAGE(B2,B3) in the ‘value_if_true’ box and then type 0 in the ‘value_if_False’ box and click on the ‘OK’ button.


Figure 5



7) Now you can observe that, the result of the formula that you entered in the previous step will be displayed in the cell D2. As mentioned above the result ‘50’ will be displayed in the cell D2 and the formula that we used to obtain the result will be displayed in the ‘Formula bar’ of the excel program.


Figure 6



So, this is how you can create a simple formula using a Nested Function in Microsoft Excel 2010 Application.


See also How to create a simple Formula using a function in Excel 2010 Application.



Read Other Applications