Nested functions

While excel has a lot of built-in formulas ready for your use, sometimes they may not fully fit the task at hand. In this kind of cases excel lets you use several formulas together to accomplish whatever task you are trying to accomplish. Using more than one function in one formula is called nested functions.

Lets say you have user inputted  hour value in A1 cell in number format. You want to display this hour value in cell C1 in hour format (for example 3 as 3 am).

For this task you need to determine two things: whether any value is inputted and is it am or pm. Here is the formula that does the job:

=if(A1<>””;if(A1<=12;A1&”am”;A1&”pm”);”please input hour in cell A1″)

Lets break up this formula and explain in english:

To write in C1 cell, check if(A1 is not equal to “empty”,if so if(A1 is smaller or equal to 12, then write A1am, else write A1pm), else write please input hour in cell A1)

Note that there is an if function nested in another if function (underlined). So this will check cell A1, if there is any value in it, than it will again check whether it is smaller or equal to 12. If so it will write “value”am in cell C1. If not, it will write “value”pm in cell C1. If A1 cell is empty, it will write please input hour in cell A1 in cell C1.

This technique is very useful when you have more than one condition to check or check&do kind of requirements. But note that you can nest up to a maximum of 7 functions.

Another example for nested functions is time checks. If you write =now() into any cell it will result as current time shown in your regional settings. If you write =month(“date”) into any cell it will show the number of month for that date (for 14/04/2013 it will result 4). If you want to know which month we are currently in, type =month(now()) in any cell and you’ll get number of the month of current date.

 

You may also like...

Leave a Reply

Your email address will not be published. Required fields are marked *