Excel 2016 functions
This section describes the most commonly used Excel functions. It is a complement to the Excel 2016 Course.
The exercises in this section will show you how to use the Excel functions.
We will start by showing you the general rules for functions.
Elements of an Excel function
The elements of a function are :
- a name
- arguments: The arguments are written in parentheses after the function name and are separated by a comma.
- a result
Let's take the example of the function SUM :
- the name is SUM
- the arguments are one or more ranges of cells for which we want to calculate the sum of the values.
For example, we write SUM(A1:A5;C3:E5). A1:A5 and C3:E5 are the arguments in this case.
- The result is the sum of the values of the ranges given as arguments. We can say that the arguments are the input information and the result is the output information.
Note. Some functions can take a variable number of arguments. This is the case of the SUM function which can take as argument a single range of cells "SUM(A1:A5)" or several ranges of cells "SUM(A1:A5, C3:E5, G4:K4)".
A formula can contain only the call of a function. For example "=SUM(A1:A5, C3:E5)". In this case, the cell with this formula will have the result of the function as its value, i.e. the sum of the values of the cells in the ranges A1:A5 and C3:E5.
But, a formula can also contain the call of a function used as operand in an operation. For example "=SUM(A1:A5,C3:E5)+G4". In this case, the result of the SUM function is determined, and then it is added to the value of cell G4.
We can also make a nested function call. For example "=MID(K2,1,FIND(" ",K2)-1)". Here the MID function is called with 3 arguments:
- FIND(" ",K2)-1
How did I find them? They are written between () after the function name and are separated by a ",".
So you can see that the 3rd argument contains a call to the FIND function. Excel will have to evaluate the result of the FIND function in order to use it as an argument to the MID function and be able to evaluate the global result.
How does Excel help us when writing a function?
I click on a cell, for example cell A2. The Name Box shows that cell A2 is selected.
I enter "=" using the keyboard. The Name Box shows the name of a function. And a small arrow that opens a menu with a serie of functions. These are the most recently used functions.
If the function I am looking for is in this list, then I just click on it to open the Function Arguments dialog box. If not, I click on More Functions. The Insert Function dialog box is opened.
The Select a function list in this dialog box displays all Excel functions. By clicking on a function, a description of the function is displayed at the bottom.
If you select a category from the Or select a category list, then the Select a function list displays only the names of the functions in the selected category.
You can also enter a search criterion in the Search for a function field and click the Go button, to get a list matching the search criterion.
Click on the function you want and validate by clicking on the OK button. The Function Arguments dialog box is opened.
This dialog box shows you the names of the arguments of the selected function. By clicking on the field related to each argument, a description of the argument is displayed at the bottom of the dialog box.
Notice also that to the right of each field, it is mentioned what type of data it is for that argument. If, for example, you find the mention "= number", and you enter an alphanumeric value, then Excel will immediately tell you that there is an error.
You can fill a field related to an argument with a value or the address of a cell or range of cells. If you click on a field in the dialog box and then select a cell or range of cells in the worksheet, then Excel will automatically add the corresponding address.
Case of a function taking a variable number of arguments
For example, in the case of the function SUM, Excel initially displays the fields for two arguments in the Function Arguments dialog box. Excel adds a third field when the second one is filled, then a fourth one when the third one is filled and so on.