Microsoft Excel 2007/2008: (Win/Mac) Calculating with Functions

Calculating with Functions

Basic worksheets in Excel often require you to use formulas and functions, which are calculations based on designated values, cell references, and commands. Functions are pre-written commands provided by Excel, while formulas are written entirely by the user. While both methods are useful, functions often save time and energy when working with complex but common tasks (such as finding the sum or average of a group of numbers) by allowing you to customize a pre-created calculation instead of typing it yourself.
HINT: For more information on formulas, refer to Performing Calculations with Formulas (Win | Mac).

• Parts of a Function
• Inserting Functions
• Inserting Functions with the Point and Click Method

Parts of a Function

Functions have two basic parts which you should be aware of:

• An equation, which is provided by Excel when you select the desired function
• Values or cell references to be used in the equation, which you will provide

Functions that are inserted using the Insert Function (Windows) / Formula Builder (Macintosh) dialog or the Point and Click method provide empty equations, you must provide the values which will be used in the calculation. Depending on the calculation, you may choose to use several types of operands.
NOTE: While typing cell references, keep in mind that the calculations will be done using the values present in the particular cells entered, not with the cell references themselves.

Operand	Example	Value
Cell Reference	A1	Calculates the function using the value(s) present in a specified cell. References can be relative or absolute.
Cell Range	A1:A3	Calculates the function using the values present in all cells specified. References can be relative or absolute.
Named Cell Range	Quiz Scores	Calculates the function using a specific group of cells that you have previously named. If the function is copied or moved, it will still use the values present in the specified range.
Value	5	Calculates the function using a specific value provided by you.

Inserting Functions

There are multiple ways to create a function. You can insert functions manually (by typing them), or you can use the Insert Function dialog box in Windows and the Formula Builder dialog box in Macintosh. The dialog box option eliminates the possibility of a typing error, so it is the recommended method.

Inserting Functions: Dialog Box Option

The dialog box option makes it easy to determine what functions are available, which function you should be using, and what you need to include in the function. It displays a listing of all functions or categories of functions available with Excel. As you select a function, a sample of the function appears at the bottom of the dialog box. As you make your selection, the dialog box will request certain types of information; you will simply need to select the cells where that information is located.
Windows:

1. Select the cell where the function should be added
2. From the Ribbon, select the Formulas command tab
3. In the Function Library group, click FUNCTION WIZARD
   The Insert Function dialog box appears.
   
4. From the Or select a category pull-down list, select the appropriate function category
   OR
   Select All
5. From the Select a function scroll box, select the desired function
   HINT: A description of the selected function appears beneath the Select a function scroll box.
6. Click OK
   The Function Arguments dialog box appears.
   NOTES:
   The appearance and options available in the Function Arguments dialog box will differ depending on which function has been chosen.
   A function's arguments are the value(s) that the function is being performed upon.
   
7. In the text boxes, type the data to be used in the function
   OR
   To select cell ranges
   1. Click COLLAPSE DIALOG
   2. Click and drag the mouse to select the desired cells
   3. Click RESTORE DIALOG
8. Click OK
   The results of the function appear in the selected cell.

Macintosh:

1. Select the cell where the function should be added
2. From the Insert menu, select Function...The Formula Builder dialog box appears.
   
3. In the formula scroll-box, double-click the desired function
   HINT: In the Description section, a description of the selected function appears.
   NOTES:
   The Arguments section of the Formula Builder expands.
   The appearance and options available in the Arguments section will differ depending on which function has been chosen.
   A function's arguments are the value(s) that the function is being performed upon.
   
4. Complete the text boxes.
   HINT: Type the data to be used in the function or click and drag the mouse to select the desired cells
5. Press [return]

Inserting Functions: Ribbon Option

Windows only:
Excel provides a multitude of functions for your use. While this ensures that functions exist for most of your needs, it can also make it very difficult to find a particular function. To make functions easier to find, they are divided into categories (e.g., math and trig functions, date and time functions, logic functions, etc.). If you are looking for a function that belongs in a particular category, you can access the Function Arguments dialog box from that category.

1. Select the cell where the function should be added
2. From the Ribbon, select the Formulas command tab
3. In the Function Library group, click the correct category » select the desired function
   The Function Arguments dialog box appears.
   NOTES:
   The appearance and options available in the Function Arguments dialog box will differ depending on which function has been chosen.
   A function's arguments are the value(s) that the function is being performed upon.
   
4. In the text boxes, type the data to be used in the function
   OR
   To select cell ranges
   1. Click COLLAPSE DIALOG
   2. Click and drag the mouse to select the desired cells
   3. Click RESTORE DIALOG
5. Click OK
   The results of the function appear in the selected cell.

About the Function Arguments Dialog Box

The Function Arguments dialog box helps you to create functions. As you type information about the function, the Function Arguments dialog box displays the name of the function, the function arguments (i.e., the values that the function is being performed upon), a description of the function and its logic, and the result of the function. Once you have entered a function, you can further edit it using the Function Arguments dialog box.

To access the Function Arguments dialog box:

Windows only:

1. Select a cell containing a function
2. On the Formula bar, click FUNCTION WIZARDThe Function Arguments dialog box appears.

Inserting Functions with the Point and Click Method

Functions based on cell references can be created by clicking the cells rather than typing the cell entries. This "point and click" method can help reduce the chance of error in the functions and may be easier for some users.
The key to the point and click method is to click the cells to be included and type the operators where appropriate.
NOTE: All functions that can be accessed from the Insert Function (Windows) / Formula Builder (Macintosh) dialog box can be typed with a text-based command. If you choose to type your function into a cell, however, be sure that you know precisely how to enter information for the function, especially if you are working with a complex function.
The following examples provide step-by-step instructions for a simple addition of two cells and for adding a range of cells.

Adding Cells Together

1. Select the cell where the results should be displayed
2. To start the function, press [=]
3. Click the first cell to be added
4. Press [+]
5. Click the next cell to be added
6. Repeat steps 4–5 as necessary
7. Windows: Press [Enter]
   Macintosh: Press [return]
   The sum appears in the selected cell.

Adding a Range of Cells with the SUM Function

1. Select the cell where the results should be displayed
2. To start the function, press [=]
3. Type SUM(
4. Click and drag the mouse over the range of cells to be added
   OR
   1. Click the first cell in the range to be added
   2. Press [:]
   3. Click the last cell in the range to be added
5. Type )
6. Windows: Press [Enter]
   Macintosh: Press [return]
   The sum appears in the selected cell.

Make sure to let me know in the comments below or on our Facebook page how you've got with it or Do you have any questions