Introduction to Functions
In the expressions we have created so far, we were using operators, constants, and values we knew already. In some complex expressions, just the known operators and the values in cells will not be enough. An alternative is to use a function.
A function is a small assignment that is performed to produce a result that can be reliably used. There are two types of functions you can use: those you create and those that are already available. In our lessons, we will not create our own functions. We will only use those that are already installed in Microsoft Excel. The already available functions are referred to as built-in functions.
The built-in functions were created by Microsoft and they are available from the time you finish installing Microsoft Excel. You can reliably use them without being concerned with how they were created or how they work.
It is like when you pick up a TV remote control and press a button to change the channel. You donít care how the remote control works and you donít spend any time finding out why the channel changed.
As in real world where we use various functions on cars, TV, food eating, etc, in the computer world, various functions are made available so you can simply use them to do your job. As a spreadsheet application, Microsoft Excel is equipped with various functions that can solve different types of calculations.
In order to use a function more effectively, you should first know whether it is available and what you need to do to make it work. If you were creating a function, you would start its structure as follows:
Function End Function
The area between Function and End Function is referred to as the body of the function. That's where you would perform the necessary assignment of the 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:
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:
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:
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:
On the Ribbon, the AutoSum function holds a list of the most common algebraic functions:
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:
This would display the Insert Function dialog box:
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.
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:
If the function is taking one argument, after the opening parenthesis, you can type its value:
If the value is held in a cell, you can click the cell that holds that value:
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:
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 on its right side. If you click that button, the Function Arguments dialog box would be minimized to give you access to the worksheet:
You can then select the necessary cell or the group of cells. After making the selection, click the stop selection button . 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.
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:
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):
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.
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.
The SUM function is used to get the addition of various numbers or the contents of various cells. The result can be displayed in another cell or used in an expression.
On the Ribbon, in the Home tab, the Editing section is equipped with a button called the AutoSum
There are two most primary ways of using the AutoSum. You can click an empty contiguous cell, and then click the AutoSum button . Before performing the SUM function, the computer will ask whether it found the right cells that you want to get the sum of. If the computer found the right cells, you can press Enter; otherwise use your mouse or your keyboard to select the cells you want to consider. You can also select the cells involved in a sum plus an empty cell that will be used to display the result, and then click the AUTOSUM button.
The decimal numeric system counts from minus infinity (-∞) to infinity (+∞). This means that a number can be usually negative or positive, depending on its position from 0, which is considered as neutral. In some operations, the number considered will need to be only positive even if it is provided in a negative format.
The absolute value of a number x is x if the number is (already) positive. If the number is negative, then its absolute value is its positive equivalent. For example, the absolute value of 12 is 12, while the absolute value of Ė12 is 12.
To get the absolute value of a number, you can use one of the ABS() function. Its syntax is:
Function ABS(number) As Number
This function takes one argument. The argument must be a number or an expression convertible to a number:
|Previous||Copyright © 2002-2009, FunctionX||Next|