» Common Entering Nesting and Example for Functions

♦Functions are predefined formulas that perform calculations by using specific values, called arguments, in a particular order, called the syntax or structure . For example, the SUM function adds values or ranges of cells, and the PMT function calculates the loan payments based on an interest rate, the length of the loan, and the principal amount of the loan.

♦Arguments can be numbers, text, logical values such as TRUE or FALSE, or cell references. The argument you designate must produce a valid value for that argument. Arguments can also be constants, formulas, or other functions.

♦The syntax of a function begins with the function name, followed by an opening parenthesis, the arguments for the function separated by commas, and a closing parenthesis. If the function starts a formula, type an equal sign (=) before the function name. As you create a formula that contains a function, the Formula Palette will assist you.

♦In the following illustration, SUM is the function name, F4 and F10 are the arguments separated by the colon which is the reference operator indicating a range of cells. Note the syntax: the nction name comes first, then the opening parenthesis, the arguments, and finally the closing parenthesis.

Common Functions►

Example and Description	Function
SUM(F4:F10)  The sum of the values in the specified range.	SUM
COUNT(B4:B10) The number of items in the specified range.	COUNT
AVERAGE(F4:F10) The average of the values in the specified range.	AVERAGE
MAX(F4:F10) The largest value in the specified range.	MAX
MIN(F4:F10) The smallest value in the specified range.	MIN
STDEV(F4:F10) An estimate of the standard deviation of the population (your selection), where the sample is all of the data to be summarized.	STDEV
VAR(F4:F10) An estimate of the variance of the population (your selection), where the sample is all of the data to be summarized.	VAR
TODAY( ) Displays the current date.	TODAY
NOW( ) Displays the current date and time.	NOW
ABS(A2) Returns the absolute value of the number in cell A2.	ABS
ROUND(A2,2) Rounds the number in A2 to 2 decimals.	ROUND
SQRT(A2) Returns a positive square root.	SQRT

 »Entering Functions

Assume you want to enter the SUM function in cell F11 as in the previous example. There are different ways to do that:

1- Select the range F4:F11  (make sure to select the cell that will host the sum value). 

 2- Click the AutoSum button    on the standard toolbar.

 

►OR you can do the same through the following procedure:

1- Click the cell in which you want to enter the formula (cell F11 in this case).

2- To start the formula with the function, click in the formula bar, or simply type an equal sign.

3- Click the down arrow next to the Functions box.

4-Select the function you want to use. If the function does not appear in the list, click More Functions for a list of additional functions.

5- Enter the arguments.

6- When you complete the formula, press ENTER.

  

►OR you can also do as follows:

1­-Click the cell in which you want to enter the formula (cell F11 in this case).

2- Click the Insert Function button   ,The Insert Function box will appear:

3-Select the function you want to use. Excel will suggest arguments for the function. You

can either accept or change these arguments:

4-Press OK

 

»Nesting Functions

 Functions can be used as arguments for other functions. When a function is used as an argument, or nested, it must return the same type of value that the argument uses, for example, if the argument returns a TRUE or FALSE value, then the nested function must return a TRUE or FALSE. If a nested function does not return the correct type of value,

Microsoft Excel will display a #VALUE! error value. The following is an example of  a nested function: 

 

 

►Nesting level limits:  A formula can contain up to seven levels of nested functions. When Function B is used as an argument in Function A, Function B is a second-level function. For instance, the AVERAGE function and the SUM function are both second-level functions because they are arguments of the IF function. If Function B contains Function C as an argument, Function C would be a third-level function.