Excel:The syntax of functions : YOUR OBJECTIVE
You probably already know how to carry out calculations using formulas, but now you’d like to perform some more complex calculations. Excel’s many functions permit you to do just that. In addition, you want Excel to automatically decide, depending on certain values, whether or how to carry out specific actions. Excel is equipped with more than 200 powerful functions that you can use for such purposes.
This learning module “ Excel: Syntax of functions ” will show you the general principles of using Excel’s functions.
Excel:The syntax of functions :HOW DOES IT WORK?
(1) Excel:The syntax of functions :How functions are structured
First of all, Functions have a very specific structure that you should understand in order to work with them. In the learning module – The Function Wizard - you learned about a tool that assists you in using functions. Nevertheless, if you want to modify a function or correct some error, you will need to know a bit more about them. And once you’ve become better acquainted with functions, it is possible you will want to enter them without help from the Function Wizard.
Each function always returns a single value. To determine this value, a function requires arguments. Arguments are values or cell references that you enter in a function. Arguments provide functions with the information they need to carry out their intended purpose. While some functions require up to five arguments, others require none at all.
Without exception, every function is constructed with the following elements:
|=||An equal sign introduces a function, just as it introduces a formula.|
|NAME||The function’s name is entered in uppercase letters; if you use lowercase letters, Excel will convert them to uppercase.|
|Arguments||Arguments for this function; multiple arguments are separated by commas.|
For instance, appearing in the formula bar, a function might look like this:
If you click on the function name in the formula bar, the displayed function will change its appearance:
Arguments, separated by commas, will be highlighted with different colors. Cells that correspond to arguments will be framed in the same color. Just below the formula bar, the function is displayed schematically with its arguments, which are shown with meaningful names. If you click on one of the arguments in the schematic function, the corresponding argument will be selected in the formula bar:
In this way, you can easily see which part of the function in the formula bar corresponds to which argument.
Here are some examples of functions. You are going to work with some of them in this chapter:
|=TODAY()||Returns the current date. Because the date is taken from your computer system, it requires no argument.|
|=MONTH(B12)||Extracts the month of a particular date (in this case, from B12).|
|=ROUND(D6/4,1)||Rounds the result of the first argument’s formula to one place to the right of the decimal point.|
|=IF(E19>100,100,E19)||Changes the contents of the current cell to another value, depending on the current value in E19.|
|=VLOOKUP(B5,O1:R5,2,0)||Searches a column of a cell matrix, and then, depending on the value in B5, returns a value from an associated cell in the matrix.|
You may have noticed from these examples that there are several kinds of arguments:
- cell ranges
- functions (no example)
You will also have noticed that some functions are very simple in structure. Maybe you are already familiar with simple functions such as SUM or MAX. Other functions, though, require more particular knowledge of their arguments and the order in which they appear. More complex functions should perhaps be created with the Function Wizard.
(2) Excel:The syntax of functions: Entering functions manually
If you frequently work with a particular function, however, and are therefore very familiar with its use, you may find it expedient to enter it directly into the formula bar without using the Function Wizard. Still, Excel offers some help when you do this.
First click on the cell in which you want to enter the function. Then click in the formula bar. As soon as you type the “=” sign and the first letter of the function’s name, a selection list will appear below the formula bar. All functions with the same first letter are listed there. The more letters of the function’s name that you enter, the shorter the list becomes.
Depending on which function in the list is currently selected, a tooltip will appear next to it with a brief explanation of the function (see illustration below). To select one of the functions in the list, just click it with your mouse. If you like, you can use your up- or down-arrow keys to move the selection indicator along the list.
In either case, when you press [Tab], the function is copied into the formula bar. A tooltip that indicates the arguments the function requires will then appear beneath the formula bar.
To enter an argument, you can either type it in (possibly a value, possibly a cell reference) or when a cell reference is required, you can alternatively use your mouse to select the cell or cells. Be sure to enter a comma after each argument. The next argument that must be specified will automatically be highlighted in bold in the tooltip.
After you’ve entered the function with all its arguments, finish by pressing [Enter]. The result will appear in the target cell immediately. If Excel determines that a function contains one or more errors, it will indicate that in an information box or in the target cell itself.