Using A Built In Function

Education is not limited to just classrooms. It can be gained anytime, anywhere... - Ravi Ranjan (M.Tech-NIT)

Using a Built-In Function

The Name of a Function

A function must have a name. Following our formula, you would specify the name after Function:

Function Name

End Function

As mentioned already, in our lessons, we will use only the existing functions that were installed with Microsoft Excel. To start using a function, you would click the cell where you want to see the result. If you know the name of the function you want to use, after clicking the cell, type = followed the name of the function. After you type the first character of a function, Microsoft Excel would display an alphabetical list of the functions that start with that character:

Function

You can keep typing the name of the function and as you type, Microsoft Excel would narrow the list of names that match the first characters you had type. Otherwise, if you see the name of the function you want in the list, you can double-click it. The function would be selected and written in the cell.

If you don't know or don't remember the name of the function that would do what you want, Microsoft Excel provides all the necessary tools and functionality to assist you.

To see a list of the available functions, on the Ribbon, click Formulas:

Formulas

The functions are listed by category. To see the list of functions in a category, click the Financial, the Logical, the Text, the Date & Time, the Lookup & Reference, or the Math & Trig button. When you click, a list would appear. Here is an example:

A List of Functions in a Category

After clicking one of those buttons, if you see the function you want to use, click it. If the function does not appear, you can click the More Functions button. This buttons holds four other categories of functions. After clicking the button, it displays a menu. You can position the mouse on one to view its list:

More Functions

On the Ribbon, the AutoSum function holds a list of the most common algebraic functions:

AutoSum

While the buttons show the functions in their respective categories, you can see all of the functions in one list. In fact, another way to look for a function is by using the Insert Function dialog box. To access it, in the Function Library section of the Ribbon:

  • Click the Insert Function button Insert Function
  • Click any button and click Insert Function...

This would display the Insert Function dialog box:

Insert Function

As described previous for the Ribbon, the functions are organized in categories in the middle combo box of the Insert Function dialog box. To select a category, click the arrow of that combo box and select. The functions of the selected category would appear in the Select A Function list box. One of the options in the combo box is All. If you select it, all functions would appear in the Select A Function list box. After selecting the desired function, you can click OK.

As its name implies, the Recently Used button holds a list of the functions you most previously used.

Instead of using the Ribbon or the Insert Function dialog box to select a function, if you already know the name of the function you want to use, you can directly type it where appropriate. Although the functions in Microsoft Excel are not case-sensitive, it is a good idea to write them in uppercase.

Introduction to the Arguments of a Function

We saw that, if you were creating a function, you would start it as follows:

Function Name

End Function

We mentioned that the section between the Function Name line and the End Function line is referred to as the body of the function. This is where you would do describe the purpose of the function. Different functions are meant for different purposes. For example, when you press the power button on a TV remote control, the TV gets turned ON or OFF depending on whether it was already ON or OFF. Therefore, the purpose of the power button (that is, its function) is to turn the TV ON or OFF and vice versa.

To carry its assignment, a function may need one ore more external values. This external value is called an argument. While one function can use one argument, another function may need more than one argument. The purpose who creates a function decides how many arguments the function would need, based on what he or she wants the function to do.

We saw already that, if you are working manually, after clicking a cell, you can type = followed by the name of the function. The arguments of a function are provided in parentheses. Therefore, after typing = followed by the name of the function, type an opening parenthesis "(". If the function doesn't take any argument, type the closing parenthesis and click the Enter button or press Enter:

Function

If the function is taking one argument, after the opening parenthesis, you can type its value:

Argument

If the value is held in a cell, you can click the cell that holds that value:

Argument

If the function takes more than one argument, type a comma, followed by the next argument that you can type or select from another cell or a group of cells, depending on the function.

After selection a function from the Ribbon or from the Insert Function dialog box as we described earlier, a dialog box named the would open:

Function Arguments

The purpose of this dialog box is to assist you with specifying the arguments of the function you selected. In the top section, this dialog box displays one or more text box in a group box whose label is the name of the function you selected. Each text box is preceded by a label that displays the name of the argument.

If you know the value of the argument you want to use, you can type it. If you know the name or address of the cell or the group of cells that holds the value you want to use, you can type the name of that cell, the range of the cells, or the name of the group of cells, in the appropriate text box. Otherwise, to assist you with the value of an argument, a text box may display a selection button Select on its right side. If you click that button, the Function Arguments dialog box would be minimized to give you access to the worksheet:

Function Arguments

You can then select the necessary cell or the group of cells. After making the selection, click the stop selection button Stop Selection. This would bring back the Function Arguments dialog box in its full display. If the function takes more than one argument, specify the value in each text box.

Optional Arguments

On a function that takes one argument, the argument may be required. In this case, you must provide it. If you don't, the function will not work (the result would be an error). If a function takes more than one argument, all arguments may be required. In this case, if you fail to provide all of them, the function would not work. In the Function Arguments dialog box, the labels of the required arguments are in bold characters.

In a function, an argument may not be required. In this case, if you don’t provide the argument, the function would use its own value, called a default argument. Another function that takes more than one argument may not require all of them. There are even cases when a function takes many arguments but none of them is required. When an argument is not required, you don’t have to supply it. If you don’t, then the function would use a default value for that particular function.

If you are manually typing a function, if it takes one argument and the argument is optional, leave the parentheses empty. If the function is taking more than one argument and one or more arguments is (are) optional, after the opening parenthesis or the comma that separates it from the left argument, you can leave the placeholder empty, then continue with the rest of the arguments. Here is an example:

=FV(12.75%,-60,325, ,1)

Notice the empty space for the fourth argument.

In the Function Arguments dialog box, the labels of the non-required arguments are in normal characters (not bold):

Function Arguments

The person who creates a function also decides on the number of its arguments, whether the argument(s) is/are required and, if the function takes more than one argument, which ones are required, whether all of them are required or none of them is required.

After specifying the arguments, click OK.

The Return Value of a Function

We mentioned that you could directly type the name of a function and its arguments or you could click OK after using the Function Arguments dialog box. If everything went alright, you should see the result in the spreadsheet. If something went wrong, an error message would let you know.

The result that displays is called the return value of the function. Of course, since there are various types of functions, different functions produce different types of results. For example, while one function would produce a string, another function can produce a number.