Using Basic Formulas and Functions

Using Basic Formulas and Functions Lesson 8 Objectives Software Orientation: Formulas Tab • In this Lesson, you’ll use command groups on the Formulas tab, as shown in the figure. These commands are your tools for building formulas and using functions in Excel. • Use this illustration as a reference throughout this lesson as you become familiar with the command groups on the Formulas tab and use them to create formulas. Building Basic Formulas • The real strength of Excel is its ability to perform common and complex calculations. • A formula is an equation that performs calculations, such as addition, subtraction, multiplication, and division, on values in a worksheet. • When you enter a formula in a cell, the formula is stored internally and the results are displayed in the cell. • Formulas give results and solutions that help you assess and analyze data. Creating a Formula that Performs Addition • A formula consists of two elements: operands and mathematical operators. – Operands identify the values to be used in the calculation. An operand can be a constant value, a cell reference, a range of cells, or another formula. A constant is a number or text value that is entered directly into a formula. – Mathematical operators specify the calculations to be performed. To allow Excel to distinguish formulas from data, all formulas begin with an equal sign (=). • In the next exercise, you will learn how to create basic formulas that perform mathematical computations and apply the formulas using various methods. Creating a Formula that Performs Addition • When you build a formula, it appears in the formula bar and in the cell itself. • When you complete the formula and press Enter, the value displays in the cell and the formula displays in the formula bar. • You can edit a formula in the cell or in the formula bar the same way you can edit any data entry. Step-by-Step: Create a Formula for Addition • Before you begin these steps, LAUNCH Microsoft Excel and OPEN a blank workbook. 1. Select A1 and key =25+15. Press Tab. Excel calculates the value in A1, and displays the sum of 40 in the cell. 2. In B1, key +18+35. Press Tab. The sum of the two numbers, 53, appears in the cell. NOTE: Formulas should be keyed without spaces, but if you key spaces, Excel eliminates them when you press Enter. Step-by-Step: Create a Formula for Addition 3. Select B1 to display the formula for that cell in the formula bar. As illustrated in the figure below, although you entered + to begin the formula, when you pressed Enter, Excel replaced the + with = as the beginning mathematical operator. This is the Excel formula auto correct feature. Step-by-Step: Create a Formula for Addition 4. Select A3. Click the formula bar and key =94+89+35. Press Enter. The sum of the three numbers, 218, appears in the cell. 5. Select A3 and click the formula bar. Select 89 and key 98. Press Enter. Notice that your sum changes to 227. • LEAVE the workbook open to use in the next exercise. Creating a Formula that Performs Subtraction • The same methods you used to create a formula to perform addition can be used to create a formula to perform subtraction. • When you create a subtraction formula, enter = followed by the positive number and then enter a minus sign to indicate subtraction. When you create a subtraction formula, the minus sign must precede the number to be subtracted. • In the next exercise, you practice creating a formula that performs subtraction. Creating a Formula that Performs Subtraction • When you enter a formula to subtract 125 from 189, you could enter =189−125 or = −125+189. Either formula yields a positive 64. If the positive number is entered first, it is not necessary to enter a plus sign. • If you find that you’ve made a mistake in your formula (such as returning the negative number mentioned earlier), you can select the cell with the erroneous function, press F2 to take you to the formula bar, and edit your function. Once you’ve made your corrections, press Enter to revise. Step-by-Step: Create a Formula for Subtraction • USE the workbook from the previous exercise. 1. Select A5. Key =456−98. Press Enter. The value in A5, 358, appears in the cell. 2. Select A6 and key =545−13−8. Press Enter. The value in A6 should be 524. 3. In A8, create a formula to subtract 125 from 189. The value in A8 should be 64. • LEAVE the workbook open to use in the next exercise. Creating a Formula that Performs Multiplication • The formula to multiply 33 by 6 is =33*6. • If a formula contains two or more operators, operations are not necessarily performed in the order in which you read the formula. • The order is determined by the rules of mathematics, but you can override standard operator priorities by using parentheses. • Operations contained in parentheses are completed before those outside parentheses. • In the next exercise, you learn to create formulas that perform multiplication. Step-by-Step: Create a Formula for Multiplication • USE the workbook from the previous exercise. 1. Select D1. Key =125*4 and press Enter. The value that appears in D1 is 500. 2. Select D3 and key =2*7.50*2. Press Enter. The value in D3 is 30. 3. Select D5 and key =5*3. Press Enter. The value in D5 is 15. 4. Select D7 and key =5+2*8. The value in D7 is 21. 5. Select D9 and key =(5+2)*8. The value in D9 is 56. • LEAVE the workbook open to use in the next exercise. Creating a Formula that Performs Division • The forward slash is the mathematical operator for division. • When a calculation includes multiple values, you must use parentheses to indicate the part of the calculation that should be performed first. Creating a Formula that Performs Division • Excel does not necessarily perform the operations in the same order that you enter or read them in a formula, which is left to right. Excel uses the rules of mathematics to determine which operations to perform first when a formula contains multiple operators. This is also known as the order of evaluation in Excel. The order is: – – – – – negative number (−) percent (%) exponentiation (ˆ) multiplication (*) and division (/) addition (+) and subtraction (−) Creating a Formula that Performs Division • For example, consider the following equation: 5 + 6 * 15 / 3 −1 = 34 • Following mathematical operator priorities, the first operation would be 6 multiplied by 15 and that result would be divided by 3. Then 5 would be added and finally, 1 would be subtracted. The figure illustrates the formula entered into Excel. Creating a Formula that Performs Division • When you use parentheses in a formula, you indicate which calculation to perform first, which overrides the standard operator priorities. Therefore, the result of the following equation would be significantly different from the previous one. The figure illustrates the Excel formula. • Here is the mathematical formula: (5 + 6) * 15 / (3 −1) = 82.5 Step-by-Step: Create a Formula for Division • USE the workbook from the previous exercise. 1. Select D7 and create the formula =795/45. Press Enter. Excel returns a value of 17.66667 in D7. 2. Select D7. Excel applied the number format to this cell when it returned the value in step 1. Click the Accounting Number Format ($) button, on the Home tab in the Numbers group, to apply accounting format to cell D7. The number is rounded to $17.67 because two decimal places is the default setting for the accounting format. Step-by-Step: Create a Formula for Division 3. Select D9 and create the formula =65−29*8+97/5. Press Enter. The value in D9 is −147.6. 4. Select D9. Click in the formula bar and place parentheses around 65–29. Press Enter. The value in D9 is 307.4. 5. CLOSE but do not save the workbook. • LEAVE Excel open to use in the next exercise. Using Cell References in Formulas • A cell reference in a formula identifies a cell or a range of cells on a worksheet and tells Excel where to look for the values you want it to calculate in the formula. • Using cell references (cell names; A1, B1, and so on) enables you to re-use the formulas you write, by updating the data in the formulas, rather than rewriting the formulas themselves. • With references, you can use values contained in different parts of a worksheet in one formula or use the value from one cell in several formulas. You can also refer to cells on another worksheet in the same workbook, as well as to other workbooks. • Excel recognizes two types of cell references—relative and absolute. Using Relative Cell References in a Formula • A cell reference identifies a cell’s location in the worksheet, based on its row number and column letter. • When you include a relative cell reference in a formula and copy that formula, Excel changes the reference to match the column or row to which the formula is copied. A relative cell reference is, therefore, one whose references change “relative” to the location where it is copied or moved. • You use relative cell references when you want the reference to automatically adjust when you copy or fill the formula across rows or down columns in ranges of cells. By default, new formulas use relative references. • In the next exercise, you practice creating and using relative cell references in formulas. Using Relative Cell References in a Formula • You are about to learn two methods for creating formulas using relative references: – By keying in an equal sign to mark the entry as a formula and then keying the formula directly into the cell; and – By keying an equal sign and then clicking a cell or cell range included in the formula (rather than keying cell references). • The second method is usually quicker and eliminates the possibility of typing an incorrect cell or range reference. • When you complete the formula and press Enter, the value displays in the cell and the formula displays in the formula bar. Step-by-Step: Use Relative Cell References • LAUNCH Microsoft Excel if it is not already open. 1. OPEN the Personal Budget data file for this lesson. 2. Select B7 and key =sum(B4: (colon). As shown in the figure, cell B4 is outlined in blue, and the reference to B4 in the formula is also blue. The ScreenTip below the formula identifies B4 as the first number in the formula. The reference to B4 is based on its relative position to B7, the cell that contains the formula. Step-by-Step: Use Relative Cell References 3. Key B6 and press Enter. The total of the cells, 3,760, appears in B7. 4. Select B15. Key =sum( and click B10. As shown in the figure, B10 appears in the formula bar and a flashing marquee appears around B10. Excel now knows that you are selecting this cell to be used in the formula. Step-by-Step: Use Relative Cell References 5. Click and drag the flashing marquee to B14. As shown in the figure, the formula bar reveals that values within the B10:B14 range will be summed (added). Note, this step allows you to input a range of cells in the formula by highlighting instead of typing the formula in the cell. Step-by-Step: Use Relative Cell References 6. Press Enter to accept the formula. Select B15. As illustrated in the figure, the value is displayed in B15 and when you click on the cell the formula is displayed in the formula bar. Take note that each cell reference is the cell’s unique name. No matter what numeric value is assigned in the cell, the cell reference (B1, C10, etc.) never changes. Step-by-Step: Use Relative Cell References 7. The goal of this step is to create a simple formula. Select D4 and key =. Click B4 and key −. Click C4 and press Enter. By default, when a subtraction formula yields no difference (a zero answer), Excel enters a hyphen. 8. Select D4 again. Click and drag the fill handle to D7 to select this range of cells. You are now copying the formula from the previous step into a new range of cells. 9. Use the fill handle to copy the formula in B7 to C7. Notice that the amount in D7 changes when the formula is copied. When you copied the formula to C7, the position of the cell containing the formula changed, so the reference in the formula changed to C7 instead of B7. Step-by-Step: Use Relative Cell References 10.Select D7 and click Copy. Select D10:D15 and click Paste. Your formula is copied to the range of cells and Excel has automatically adjusted the cell references accordingly. Note that D7 is still highlighted by the flashing marquee. 11.Select D17:D21 and click Paste. Your formula from D7 is now copied to the second range of cells and the references are adjusted. Note that the flashing marquee is still surrounding D7. You have the ability to copy one formula into multiple locations without having to recopy it. 12.Create a Lesson 8 folder and SAVE your worksheet as Budget. • LEAVE the workbook open to use in the next exercise. Using Absolute Cell References in a Formula • Sometimes you do not want a cell reference to change when you move or copy it. For example, when you review your personal budget, you might want to know what percentage of your income is budgeted for each category of expenses. • Each formula you create to calculate those percentages will refer to the cell that contains the total income amount. • The reference to the total income cell is an absolute cell reference—a reference that does not change when the formula is copied or moved. Using Absolute Cell References in a Formula • Absolute cell references include two dollar signs in the formula. • The absolute cell reference $B$7 in this exercise, for example, will always refer to cell B7 because dollar signs precede both the column (B) and row (7). • When you copy or fill the formula across rows or down columns, the absolute reference will not adjust to the destination cells. • By default, new formulas use relative references, and you must edit them if you want them to be absolute references. Using Absolute Cell References in a Formula • You can also create a mixed reference in which either a column, or a row, is absolute or the other is relative. For example, if the cell reference in a formula were $B7 or B$7, you would have a mixed reference in which one component is absolute and one is relative. • The column is absolute and would remain unchanged in the formula, and the row is relative if the reference is $B7, changing as the mixed reference is copied to $B7, $B8, and so on. Using Absolute Cell References in a Formula • If you copy or fill a formula across rows or down columns, the relative reference automatically adjusts, and the absolute reference does not adjust. For example, if you copied or filled a formula containing the mixed reference $B7 to a cell in column C, the formula in the destination cell would be =$B8. • The column reference would be the same because that portion of the formula is absolute. • The row reference would adjust because it is relative. Step-by-Step: Use Absolute Cell References • USE the workbook from the previous exercise. 1. Select B15. Use the fill handle to the right to copy the formula to C15. You have just extended the formula to cell C7 to calculate the information in the range of cells above C7. 2. Select B21. Key =sum( and select B17:B20. Press Enter. You have just created a formula to calculate the range of cells selected as illustrated in the figure. Note that the formula you copied and applied to D21 was automatically calculated when you pressed Enter. Step-by-Step: Use Absolute Cell References 3. Select B21 and drag the fill handle to C21. You have copied the formula to the adjacent cell. 4. Select E10. Key = and click B10. Key / and click B7. Press Enter. You now have a decimal value of .253 as your formula result. Step-by-Step: Use Absolute Cell References 5. Select E10 again. On the formula bar, click in front of B7 to edit the formula; change B7 (relative cell reference) to $B$7 (absolute cell reference). The edited formula should read =B10/$B$7 as in the figure. Press Enter. An absolute reference should be understood to be a value that you never want to change in your formula. By default, Excel will copy a formula into selected ranges as a relative cell reference unless you instruct it to do otherwise. Once you apply the absolute reference, Excel recognizes it and the program will not try to modify it to a relative reference again. Step-by-Step: Use Absolute Cell References 6. Select E10 and drag the fill handle to E15. You have now applied the formula with the absolute reference $B$7 to each of the cells in the range. 7. With E10:E15 still selected, click the Percent Style button (%) in the Number group on the Home tab. Click Increase Decimal. The values should display with one decimal place and a % (see figure). 8. SAVE your workbook. • LEAVE the workbook open to use in the next exercise. Referring to Data in Another Worksheet • As mentioned earlier, cell references can link to the contents of cells in another worksheet within the same workbook. • You might need to use this strategy, for example, to create a summary of data contained in several worksheets. • The principles for building these formulas are the same as those for building formulas referencing data within a worksheet. • In the next exercise, you practice building and using formulas that contain references to data in other worksheets. You will also learn how to refer to cells and ranges of cells outside of your active worksheet. Step-by-Step: Refer to Data in Another Worksheet • USE the workbook you saved in the previous exercise. 1. Click Sheet2 to make it the active sheet. 2. Select B4. Key = to indicate the beginning of a formula. Click Sheet1 and select B7. Press Enter. The value of cell B7 on Sheet1 is displayed in cell B4 of Sheet2. The formula bar displays =Sheet1!B7. 3. With Sheet2 still the active sheet, select B4 and drag the fill handle to D4. The values from Sheet1 row 4 are copied to Sheet2 row 4. Step-by-Step: Refer to Data in Another Worksheet 4. On the Home tab, click Format and click Rename Sheet. As you recall, you renamed worksheet tabs in previous exercises. 5. Key Summary and press Enter. 6. Make Sheet1 active. Click Format and click Rename Sheet. 7. Key Expenses and press Enter. Both worksheet tabs are now renamed. Step-by-Step: Refer to Data in Another Worksheet 8. Make the Summary sheet active and select B4. The formula bar now shows the formula as =Expenses!B7. See the figure. 9. SAVE your workbook. • LEAVE the workbook open to use in the next exercise. Referencing Data in Another Worksheet • An external reference refers to a cell or range on a worksheet in another Excel workbook, or to a defined name in another workbook. • Although external references are similar to cell references, there are important differences. • You normally use external references when working with large amounts of data and complex formulas that encompass several workbooks. • In the next exercise, you will learn how to refer to data in another workbook. Step-by-Step: Reference Data in Another Worksheet • USE the workbook you saved in the previous exercise. 1. Click the File tab and click Options. 2. On the Options window, click Advanced. 3. Scroll to find Show all windows in the Taskbar, if it isn’t already selected, select it and click OK. See the figure. Step-by-Step: Reference Data in Another Worksheet 4. You are still in the Summary worksheet. In A10, key Other Expenses and press Tab. 5. OPEN the Financial Obligations data file for this lesson. This is the source workbook. The Budget workbook is the destination workbook. 6. Switch to the Budget workbook, and with B10 still active, key = to indicate the beginning of a formula. Change to the Financial Obligations workbook and select B8. A flashing marquee will identify this cell reference. Step-by-Step: Reference Data in Another Worksheet 7. Press Enter to complete the external reference formula. Select B10. Your external reference has now been copied to this cell as illustrated in the figure. The formula bar displays square brackets around the name of the source workbook, indicating that the workbook is open. When the source is open, the external reference encloses the workbook name in square brackets, followed by the worksheet name, an exclamation point (!), and the cell range on which the formula depends. Step-by-Step: Reference Data in Another Worksheet 8. CLOSE the Financial Obligations workbook. When the source workbook is closed, the brackets are removed and the entire file path is shown in the formula. The formula bar in the Budget worksheet now displays the entire path for the source workbook as in the figure because the source file is now closed. 9. SAVE the destination workbook. • LEAVE the workbook open to use in the next exercise. Using Cell Ranges in Formulas • You can simplify formula building by naming ranges of data and using that name in selections and formulas rather than keying or selecting the cell range each time. • In the business environment, you will often use a worksheet that contains data in hundreds of rows and columns. • After you name a range, you can select it from the Name box and then perform a variety of functions, such as cutting and pasting it to a different workbook as well as using it in a formula. • By default, a named range becomes an absolute reference in a formula. Naming a Range • A name is a meaningful and logical identifier that you apply in Excel to make it easier to reference the purpose of a cell reference, cell range, constant, formula, or table. • Naming a range clarifies the purpose of the data within the range of cells. Naming ranges or an individual cell according to the data they contain is a time-saving technique, even though it may not seem so when you work with limited data files in practice exercises. • A good example could be to name a range such as B7:B17 as Total Items so that in future formula construction and reference, you only need to key Total Items and Excel will recognize the range to which you are referring. Naming a Range • You must select the range of cells you want to name before you use the Name box to create a named range. • When you create a name using the Define Name command, you have the opportunity to select the range after you enter the name. This option is not available when you use the Name box. • All names have a scope, either to a specific worksheet or to the entire workbook. The scope of a name is the location within which Excel recognizes the name without qualification. Naming a Range • For example, in step 1 in the next exercise, when you create the name Income_Total for cell B7, the New Name box identifies the scope as part of the workbook. This means the named cell can be used in formulas on the Expenses and the Summary worksheets in this workbook. • In this exercise, you will use three methods to name cells and ranges of cells. You will create the names by: – Clicking Define Name on the Formulas tab and selecting the cell or range to be included in the name. – Selecting a cell or range and entering a name in the Name box next to the formula bar. – Selecting a cell or range that includes a label and clicking the Create from Selection button on the Formulas tab. Step-by-Step: Name a Range • USE the workbook you saved in the previous exercise. 1. Select B7 on the Expenses worksheet and click Define Name in the Name Manager group on the Formulas tab. The New Name dialog box shown in the figure opens with Excel’s suggested name for the range. Step-by-Step: Name a Range 2. Click OK to accept Income_Total as the name for B7. Note that the Income_Total name appears in the name box instead of the default cell reference of B7. See the figure. 3. Select B36. Click Define Name. In the New Name dialog box, with the Name box highlighted for entry, key Total Expenses. Accept the default in the Scope box. 4. Click the Collapse Dialog button and proceed to select the range that makes up total expenses. Step-by-Step: Name a Range 5. With the text already selected by default in the Refers to box, press Ctrl and click B15, B21, B28, and B33, release Ctrl, and then click the Expand Dialog button. You have just selected cells that have the Expenses defined named copied and applied to them as seen in the figure. Click OK to close the New Name dialog box. Some of the selected cells are blank. In the following exercises, you will use the names you just created to fill them. Step-by-Step: Name a Range 6. Select B23:B27 and click in the Name box to the left of the formula bar. Key Transportation and press Enter. 7. Select B30:B32 and click Define Name. Key Entertainment in the Name box on the dialog box. Click OK. 8. Select A15:B15. Click Create from Selection. The left column will be selected. Click OK. The dialog box closes. While naming this range doesn’t change the current worksheet, you will use the range you just named in a later exercise. Your worksheet should resemble the figure. • LEAVE the workbook open to use in the next exercise. Changing the Size of a Range • If you need to change the parameters of a named range, you can easily redefine the range by using the Name Manager on the Formulas tab. • The Name Manager contains all the information about named ranges. It allows you to view summaries of the names you have applied in the worksheet. • In the next exercise, you will edit the range for Home_Total. Step-by-Step: Change the Size of a Range • USE the workbook from the previous exercise. 1. Click Name Manager on the Formulas tab. From the Name Manager window (see the figure), click to select Home_Total and click Edit. The Edit Name dialog box opens. You will change the scope (size) of the range rather than the name. 2. The Home_Total range is identified in the Refers To box at the bottom of the dialog box. Click Collapse Dialog and select B10:B14. Step-by-Step: Change the Size of a Range 3. Click Expand Dialog to view the dialog box as shown in the figure. Click OK to accept your changes and close the dialog box. 4. Click Close to close the Name Manager dialog box. SAVE the workbook. • LEAVE the workbook open to use in the next exercise. Keeping Track of Ranges • Use the Name Manager dialog box to work with all of the defined names in the workbook. From this dialog box you can also add, change, or delete names. • You can use the Name Manager as a convenient way to confirm the value and reference of a named reference or to determine its scope. Step-by-Step: Keep Track of Ranges • USE the workbook from the previous exercise. 1. Click Name Manager on the Defined Names group on the Formulas tab. You will use the Name Manager to modify previously created names and create new ones. 2. Select Income_Total and click Edit. 3. Select _Total in the Name field and press Delete. Click OK to accept your changes and close the dialog box. Step-by-Step: Keep Track of Ranges 4. Click New. Key Short\Over in the Name box. Be sure to use the backslash. You are specifying the name of a new range you will create in the next step. If you accidently key a forward slash, you will get an error dialog box. Click OK and return to the name and fix the error. 5. In the Refers To box, key =Income–Expenses. Click OK. You have now used names to create a formula. 6. Click Close to close the Name Manager dialog box. SAVE the workbook. • LEAVE the workbook open to use in the next exercise. Creating a Formula to Operate on a Named Range • You have created several named ranges in the previous exercises, which you will use in the next exercise to fill cells on the worksheets in your Budget workbook. Step-by-Step: Create a Formula for a Named Range • USE the workbook from the previous exercise. 1. On the Expenses worksheet, select B28. Key =sum(. Click Use in Formula in the Defined Names group on the Formulas tab. The Use in Formula dropdown list appears. It contains all the Defined names that you created as seen in the figure. Step-by-Step: Create a Formula for a Named Range 2. Click Transportation on the drop-down list. Key the closing parenthesis in the formula and press Enter. You have now defined the Transportation name for use in formulas for the selected range. 3. Select B33. Click the formula bar and key the following formula =sum(Entertainment), and press Enter. 4. On the Summary worksheet, select B11. Key the formula =sum( and click Use in Formula. Select Total Expenses from the list of named cells and ranges. Press Enter. SAVE the workbook. • LEAVE the workbook open to use in the next exercise. Summarizing Data with Functions • A function is a predefined formula that performs a calculation. • Excel’s built-in functions are designed to perform all sorts of calculations—from simple to complex. • When you apply a function to specific data, you eliminate the time involved in manually constructing a formula. • Using functions ensures the accuracy of the formula’s results. Summarizing Data with Functions • A function consists of a function name and function arguments and specified syntax. See the table for a list of the most commonly used Excel functions. • The arguments are enclosed in parentheses in the formula. This lets Excel know where the formula begins and where it ends. Summarizing Data with Functions • The arguments are in logical format from the left of the formula to the right in the parenthesis; (argument1, argument2, …) and are performed in that order, from left to right. • Depending on the function, an argument can be a constant value, a single-cell reference, a range of cells, or even another function. • If a function contains multiple arguments, the arguments are separated by commas. Using SUM • Adding a range of cells is one of the most common calculations performed on worksheet data. • You can use the SUM function to easily and accurately select the cells to be included in a calculation. • The AutoSum function makes that even easier, by calculating (by default) the total from the active cell to the first nonnumeric cell. • In previous exercises, you created a formula to perform addition by keying or selecting the cells to include and connected them with the plus sign. Using SUM • Using the SUM or AutoSum function is a much easier way to achieve the same result. • AutoSum is a built-in feature of Excel that recognizes adjacent cells in rows and columns as the logical selection to perform the AutoSum. • In the next exercise, you will use the most commonly used functions, beginning with the SUM function. Step-by-Step: Use SUM • USE the workbook from the previous exercise. 1. On the Expenses worksheet, select C28. Click Insert Function in the Function Library group on the Formulas tab. The Insert Function dialog box shown in the figure opens. 2. SUM is selected by default. Click OK. The Functions Arguments box for SUM opens. Step-by-Step: Use SUM 3. In the Function Arguments box, the default range shown is C26:C27. Click the Collapse Dialog button in the Number1 field and select the cell range C23:C27. This has now applied the SUM function and its arguments to the selected cell range as illustrated in the figure. Step-by-Step: Use SUM 4. Click the Expand Dialog button and click OK. 5. Select C33 and click AutoSum in the Function Library group. 6. Press Enter to accept C30:C32 as the range to sum. SAVE the workbook. • LEAVE the workbook open to use in the next exercise. NOTE: Because it is used so frequently, AutoSum is available on the Formulas tab in the Function Library group and on the Home tab in the Editing group. Using COUNT • Statistical functions, such as SUM and COUNT, are used to compile and classify data to present significant information. • Use the COUNT function to count the number of numeric entries in a range. For example, in a worksheet used to calculate wages, you can apply the COUNT function to determine how many of the employees have worked over 40 hours in a work week. • You will apply the COUNT function in the following exercise. Step-by-Step: Use COUNT • USE the workbook from the previous exercise. 1. On the Expenses worksheet, select A39 and key Expense Categories. Press Tab. 2. Click Insert Function in the Function Library group on the Formulas tab. The Insert Function dialog box opens. 3. On the Insert Function dialog box, key COUNT in the search for function text box and click Go. The function will appear at the top of the function list and be selected by default in the Select a Function window. Click on COUNT and click OK. You want to count only the expenses in each category and not include the category totals. 4. Click the Collapse Dialog button for Value1. Step-by-Step: Use COUNT 5. Select B10:B14 and press Enter. You have selected the range of cells for Value1 and Home_Total is now entered in the Value1 text box instead of the cell range. 6. Click Collapse Dialog for Value2 and select B17:20. Press Enter. B17:B20 now appears in the Value2 text box. You have selected the range of cells for Value2. 7. Collapse the dialog box for Value3. Select B23:B27 and press Enter. The identified range is one you named in a previous exercise. That name (Transportation) appears in the Value3 box rather than the cell range, and the values of the cells in the Transportation and Entertainment named ranges appear to the right of the value boxes. Step-by-Step: Use COUNT 8. In the Value4 box, key Entertainment. You have now manually applied the name Entertainment for Value4. Your entries in the Function Arguments dialog box should look similar to those shown in the figure. 9. Click OK to accept the function arguments. Excel returns a value of 17 in B39. SAVE the workbook. • LEAVE the workbook open to use in the next exercise. Using COUNTA • Use the COUNTA function to count the number of cells in a range that are not empty. • COUNTA counts both text and values in a selected data range. • You can use this formula to count the number of entries in a particular worksheet or range of cells. • You will use this formula in the next exercise. Using AVERAGE • The AVERAGE function adds a range of cells and then divides by the number of cell entries. • It might be interesting to know the average difference between what you budgeted for expenses and the amount you actually spent during the month. • Before you can calculate the average, however, you will need to finish calculating the differences. Step-by-Step: Use AVERAGE • USE the worksheet from the previous exercise. 1. Select D21 and right-click. Click Copy. You are copying the formula in D21 for the next step. 2. Select D23, right-click and click Paste. You have just pasted the formula into cell D23. 3. Use the fill handle in cell D23 to copy the formula to the range D24:D28. 4. Copy the formula in D28 and paste it to D30. 5. Use the fill handle in cell D30 to copy the formula to D31:D33. Step-by-Step: Use AVERAGE 6. In A41, key Average Difference and press Tab. 7. Click Recently Used in the Function Library group and click AVERAGE. If AVERAGE does not appear in your recently used function list, key AVERAGE in the Search for a function box and click Go. The function will appear at the top of the function list and be selected by default. Click OK. You are applying the AVERAGE formula to cell A42. 8. Click Collapse Dialog in Value1. Press Ctrl and select the category totals (D15, D21, D28, and D33). Notice that the arguments are separated by a comma. Step-by-Step: Use AVERAGE 9. Click Expand Dialog. Click OK. Your screen should resemble the screenshot in the figure. There is a $38 average difference between the amount budgeted and the amount you spent in each category. 10.SAVE and CLOSE the Budget workbook. • LEAVE Excel open to use in the next exercise. Using MIN • The MIN formula returns the smallest number in a set of values. For example, a professor would use the MIN function to determine the lowest test score; a sales organization would determine which sales representative earned the lowest commission or which employee earns the lowest salary. • Maximum values are usually calculated for the same set of data. • You will learn to apply the MIN function in the next exercise. Step-by-Step: Use MIN • OPEN the Personnel data file for Lesson 8. 1. Select A22 and key Minimum Salary. Press Tab. 2. Click the Recently Used button in the Function Library group on the Formulas tab. The MIN function is not listed. Key =min in cell B22 and double-click on MIN when it appears on the drop-down list below cell B22. Excel inputs the MIN command and an opening parenthesis is added to your formula. Step-by-Step: Use MIN 3. Select E6:E19 and press Enter. You have now finished creating the formula arguments and applied the MIN function. Excel returns a value of $25,000 as the minimum salary for the personnel. See the figure. 4. SAVE the workbook as Analysis. • LEAVE the workbook open to use in the next exercise. Using MAX • The MAX function returns the largest value in a set of values. • Minimum values are usually calculated for the same set of data. Step-by-Step: Use MAX • USE the worksheet from the previous exercise. • In A23, key Maximum Salary and press Tab. • Click Insert Function in the Function Library group and key MAX in the Search for a function box and click Go. When the MAX function appears, it will be selected by default, click OK. • Click the Collapse Dialog button in Number1 text box and select E6:E19. • Click the Expand Dialog button and click OK. Excel applies and calculates the function on the range and returns the maximum salary value of $89,000 in cell A24. • SAVE and CLOSE the workbook. • LEAVE Excel open to use in the next exercise. Using Formulas to Create Subtotals • You can calculate subtotals using the SUBTOTAL function, but it is generally easier to create a list by using the Subtotal command in the Outline group on the Data tab. • After the subtotal list has been created, you can edit it using the SUBTOTAL function. Selecting Ranges for Subtotaling • Groups are created for subtotaling by sorting the data. Data must be sorted by groups to insert a SUBTOTAL function. • Subtotals are calculated with a summary function, and you can use the SUBTOTAL function to display more than one type of summary function for each column. Step-by-Step: Select Ranges for Subtotaling 1. OPEN the Personnel data file for this lesson. 2. Select A5:F19 (the data range and the column labels). Click Sort in the Sort & Filter group on the Data tab. 3. On the Sort dialog box, select Department as the sort by criterion. Select the My data has headers check box if it is not selected. Click OK. The list is sorted by department. 4. With the data range still selected, click Subtotal in the Outline group on the Data tab. The Subtotal dialog box opens. 5. Select Department in the At each change in box. Sum is the default in the Use function box. Step-by-Step: Select Ranges for Subtotaling 6. Select Salary in the Add subtotal to box. Deselect any other column labels. Select Summary below data if it is not selected. Click OK. Subtotals are inserted below each department with a grand total at the bottom. 7. With the data selected, click Subtotal. On the dialog box, click Average in the Use function box. 8. Click Replace current subtotals to deselect it. Click OK. 9. SAVE the workbook as Dept Subtotals. • LEAVE Excel open to use in the next exercise. Modifying a Range in a Subtotal • You can change the way data is grouped and subtotaled by modifying the subtotal range using the SUBTOTAL function. • This option is not available when you create subtotals from the Data tab commands. Step-by-Step: Modify a Range in a Subtotal • USE the worksheet you saved in the previous exercise. 1. Insert a row above the Grand Total row. 2. Key Sales/Marketing Total in B29. 3. Copy the subtotal formula from E47 to E49. 4. In the Formula bar, change the function 9 (which includes hidden values) to 109 (which ignores hidden values) to exclude the sum and average subtotals for the individual departments within the data range. Otherwise, the formula result will include the average salary and the total salaries as well as the actual salaries for individual employees. Step-by-Step: Modify a Range in a Subtotal 5. Replace the range in the Formula bar with E21:E45 and press Enter. The salaries for the sales and marketing departments combined are $310,000, which are now entered into the cell. 6. SAVE the workbook as Dept Subtotals Revised. CLOSE the workbook. • LEAVE Excel open to use in the next exercise. Building Formulas to Subtotal and Total • In the previous exercise, you copied and modified a formula to create a subtotal for a combined group. • You can accomplish the same result by using the SUBTOTAL function to build a formula and add subtotals to data that you cannot or do not want to sort into one category in order to use the built-in function in the Data tab’s subtotal function. Step-by-Step: Build to Subtotal and Total 1. OPEN the Personnel data file for this lesson. 2. Insert a row above row 11. 3. Select E11 and click Recently Used in the Formula Library group on the Formulas tab. The Recently Used formula drop-down list appears. Note that the SUBTOTAL function is not there. Click on the Insert Function option. Key SUBTOTAL in the Search for a function box and click Go. When the SUBTOTAL function appears, it will be selected by default, click OK. 4. Key 9 in the Function_num box on the Function Arguments dialog box. Step-by-Step: Build to Subtotal and Total 5. Click Collapse Dialog in Ref1 and select E6:E10. You are inputting your first reference. 6. Click Expand Dialog and click OK to accept your changes and close the dialog box. 7. Select B11 and key Support Staff Total. 8. Select B21 and key Sales and Marketing Total. 9. Select E21 and click Recently Used. Click SUBTOTAL. Use the same procedure in step 4 to create a subtotal for the values in E12:E20. You are creating another subtotal formula. Format the subtotal for currency and expand the column to accommodate the data. Step-by-Step: Build to Subtotal and Total 10.Press Ctrl and select row 11 and row 21. Click Bold on the Home tab to emphasize the subtotals. Compare your worksheet to the figure. 11.SAVE the workbook as Combined Depts. • LEAVE the workbook open to use in the next exercise. Controlling the Appearance of Formulas • When you work with extremely large worksheets that contain numerous formulas, you sometimes need to see all formulas to audit the calculations in the worksheet. • You can display and print the worksheet with all formulas visible. Displaying Formulas on the Screen • When you create a formula, the result of the calculation is displayed in the cell and the formula is displayed in the Formula bar. • You may need to see all formulas on the screen in order to audit them. • As you learn in this lesson, you can click the Show Formulas command to display the formula in each cell instead of the resulting value. Step-by-Step: Display Formulas on the Screen • With the workbook Combined Depts already open, perform the following steps: 1. Click Show Formulas in the Formula Auditing group on the Formulas tab. All worksheet formulas are displayed. 2. Click Show Formulas. Values are displayed. 3. SAVE and CLOSE the workbook. When you open the workbook again, it will open values displayed. • LEAVE Excel open to use in the next exercise. Printing Formulas • When you audit the formulas in a large worksheet, you may find it useful to print the worksheet with the formulas displayed. • To gain maximum benefit from the printed copy, print gridlines and row and column headers. • In the next exercise, you will display formulas for printing and adjust the print settings. Step-by-Step: Print Formulas 1. OPEN Budget from your Lesson 8 folder. This is the exercise you saved earlier. 2. Click Show Formulas in the Formula Auditing group on the Formulas tab. The formulas appear in the spreadsheet. 3. Click the Page Layout tab and click Print in Gridlines and Print in Headings in the Sheet Options group. 4. Click Orientation in the Page Setup group and click Landscape. 5. Click the File tab. Click on Print and view the Print Preview. 6. Click the Page Setup link at the bottom of the print settings to open the Page Setup dialog box. Step-by-Step: Print Formulas 7. On the Page tab of the dialog box, click Fit to: and leave the defaults as 1 page wide by 1 tall. 8. Click the Header/Footer tab. Click Custom Header and key your name in the left section. Click OK to accept your changes and close the Page Setup dialog box. Refer to the figure. Step-by-Step: Print Formulas 9. Click the Print button at the top-left corner of the Backstage view window. When prompted, click OK to print the document. 10.SAVE the workbook with the same name. CLOSE the workbook. • CLOSE Excel. Lesson Summary