Home

Introduction to Functions
 

Functions Fundamentals
 

Introduction

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.

The Structure of a Function

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.

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.

Fundamental Built-In Functions
 

The Sum Function

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

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.

Practical Learning: Using AutoSum
  1. Start Microsoft Excel
  2. Open the CPAR Accounting Records2.xlsx workbook and, if necessary, click the Transaction Analysis sheet
  3. Based on the descriptions of the transactions we saw in Lesson 9, enter the values as follows.
    Click Cell C6 and type +18,000
  4. Click Cell M6 and type +18,000
  5. Click Cell C7 and type +745
  6. Click Cell M7 and type +745
  7. Click Cell I8 and type +1,250
  8. Click Cell K8 and type +1,250
  9. Click Cell C9 and type (1,850)
  10. Click Cell I9 and type 1,850
  11. Click Cell K10 and type 120
  12. Click Cell M10 and type (120)
  13. Click Cell C11 and type 1,150
  14. Click Cell M11 and type 1,150
  15. Click Cell C12 and type 650
  16. Click Cell E12 and type 400
  17. Click Cell M12 and type 1,050
  18. Click Cell C13 and type (350)
  19. Click Cell M13 and type (350)
  20. Click Cell C14 and type (320)
  21. Click Cell G14 and type 320
  22. Click Cell C15 and type (120)
  23. Click Cell K15 and type (120)
  24. Click Cell C16 and type 400
  25. Click Cell E16 and type (400)
  26. Click Cell C17 and type (2,820)
  27. Click Cell M17 and type (2,250)
  28. Click Cell M18 and type (350)
  29. Click Cell M19 and type (220) and press Ctrl + Home
     
    Functions
  30. Click Cell C20 to give it focus
  31. On the Ribbon, click Home if necessary.
    In the Editing section, click the AutoSum button AutoSum  
  32. Select Cells C6:C19
     
    Function
  33. On the Formula Bar, click the Enter button Enter
  34. Click Cell E20
  35. On the Ribbon, click Formulas
  36. In the Function Library section, click the AutoSum button
  37. Select Cells E6:E19 and press Enter
  38. Right-click Cell C20 and click Copy
  39. Click Cell G20 and press Enter
  40. Click Cell I20
  41. In the Function Library section of the Ribbon, click the Insert Function button
  42. In the Insert Function dialog box, in the top text box, type Add cells values and click Go
  43. In the list of sought functions, click SUM
     
    Insert Function
  44. Click OK
  45. In the Function Arguments dialog box, delete the content of the first box and click the selection button
  46. On the worksheet, click Cell I8
  47. In the Function Arguments dialog box, click the stop selection button
  48. In the Function Arguments dialog box, click the second box and type I9
  49. In the Function Arguments dialog box, click OK
  50. Click Cell K20
  51. In the Function Library section of the Ribbon, click the Math & Trig button
  52. Scroll down in the list of functions and click SUM
  53. In the Function Arguments dialog box, delete the content of the top text box
  54. On the worksheet, click Cell K8
  55. In the Function Arguments dialog box, click the second text box
  56. On the worksheet, click Cell K10
  57. In the Function Arguments dialog box, click the third text box
  58. On the worksheet, click Cell K15
     
    Function
  59. In the Function Arguments dialog box, click OK
  60. Click Cell M20
  61. In the Function Library section of the Ribbon, click the Recently Used button and click SUM
  62. In the Function Arguments dialog box, click the selection button Selection
  63. On the worksheet, select Cells M6:M19
     
    Function
  64. On the Function Arguments dialog box, click the stop selection button Stop Selection
  65. In the Function Arguments dialog box, click OK
  66. Click Cell F22 and type =SUM
     
    Functions
  67. Add the opening parenthesis (
     
    Function
  68. On the worksheet, click Cell C20 and type ,
  69. On the worksheet, click Cell E20 and type ,
  70. On the worksheet, click Cell G20 and type ,
  71. On the worksheet, click Cell I20
     
    Function
  72. Press Enter
  73. On the worksheet, click Cell L22 and =SUM(
     
  74. On the worksheet, click Cell K20
  75. Press and hold Ctrl
  76. Click Cell M20 and release Ctrl
  77. Type ) and press Enter
     
    Function
  78. Save the work book

The Absolute Value

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:

  • If the argument is a positive number, the function returns it
  • If the argument is zero, the function returns 0
  • If the argument is a negative number, the function is returns its equivalent positive value

Practical Learning: Using the Absolute Value
  1. The CPAR - Accounting Records2.xlsx workbook should still be opened.
    Click the Income Statement tab
  2. Click Cell J7 and type =
  3. Click the Transaction Analysis tab
  4. Click Cell M11 and type +
  5. Still in the Transaction Analysis tab, click Cell M12 and press Enter
  6. Back in the Income Statement sheet, click Cell H9 and type =ABS(
  7. Click the Transaction Analysis tab
  8. Click Cell M17 and press Enter
  9. Back in the Income Statement sheet, click Cell H10 and type =ABS(
  10. Click the Transaction Analysis tab
  11. Click Cell M18 and press Enter
  12. Back in the Income Statement sheet, click Cell H11 and type =ABS(
  13. Click the Transaction Analysis tab
  14. Click Cell M19 and press Enter
  15. Back in the Income Statement sheet, click Cell H12 and type =ABS(
  16. Click the Transaction Analysis tab
  17. Click Cell M10 and press Enter
  18. Back in the Income Statement sheet
  19. Click Cell J13 and type =SUM(
  20. Select Cells H9:H12 and press Enter
  21. Click Cell J14 and type =
  22. Click Cell J7 and type -
  23. Click Cell J13 and, on the Formula Bar, click the Enter button Enter
  24. Click Cell C14, type Net Loss and press Enter
     
    Net Loss
  25. Save the workbook
 
Previous Copyright © 2002-2008 FunctionX Next