Functions are predefined formulas that perform calculations by using specific values, called arguments, in a particular order, or structure. Functions can be used to perform simple or complex calculations. For example, the ROUND function rounds off a number in cell A10.
Structure of a function
Structure. The structure of a function begins with an equal sign (=), followed by the function name, an opening parenthesis, the arguments for the function separated by commas, and a closing parenthesis.
Function name. For a list of available functions, click a cell and press SHIFT+F3.
Arguments. Arguments can be numbers, text, logical values such as TRUE or FALSE, arrays (array: Used to build single formulas that produce multiple results or that operate on a group of arguments that are arranged in rows and columns. An array range shares a common formula; an array constant is a group of constants used as an argument.), error values such as #N/A, or cell references (cell reference: The set of coordinates that a cell occupies on a worksheet. For example, the reference of the cell that appears at the intersection of column B and row 3 is B3.). The argument you designate must produce a valid value for that argument. Arguments can also be constants (constant: A value that is not calculated and, therefore, does not change. For example, the number 210, and the text "Quarterly Earnings" are constants. An expression, or a value resulting from an expression, is not a constant.), formulas, or other functions.
Argument tooltip. A tooltip with the syntax and arguments appears as you type the function. For example, type =ROUND( and the tooltip appears. Tooltips only appear for built-in functions.
Entering formulas When you create a formula that contains a function, the Insert Function dialog box helps you enter worksheet functions. As you enter a function into the formula, the Insert Function dialog box displays the name of the function, each of its arguments, a description of the function and each argument, the current result of the function, and the current result of the entire formula.
In certain cases, you may need to use a function as one of the arguments (argument: The values that a function uses to perform operations or calculations. The type of argument a function uses is specific to the function. Common arguments that are used within functions include numbers, text, cell references, and names.) of another function. For example, the following formula uses a nested AVERAGE function and compares the result with the value 50.
Valid returns When a nested function is used as an argument, 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 it doesn't, Microsoft Excel displays a #VALUE! error value.
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. A function nested within the AVERAGE function would be a third-level function, and so on.