These exercises build on the Example1 worksheet created in Lessons 1 and 2.
As our first example illustrating the use of an Excel built-in function, we will modify the Example1 worksheet (constructed in Lesson 2). One of the Excel built-in functions is the function SUM which, as its name suggests, computes sums of values in a range of cells.To do this, we delete the formula in cell B10, type an = sign, then select the function selection dialog box (the symbol fx) from the tool bar (see the cursor position in the figure below). The dialog box shown in the figure is then displayed and we can choose from among the many Excel built-in functions. In the figure, we have selected the SUM function from the Most Recently Used function list. Once we make this selection we click to close the function selection dialog box.
When we close the function selection dialog box, a second dialog box appears, as shown below. The purpose of this dialog box is to help us set the arguments for the function we've chosen. In this example, the spreadsheet "guesses" that we wish to sum the cells (starting with the first one from the top with numerical data in it) immediately above the formula cell. In other words it selects for us the range of cells B3:B9. This is almost correct. We probably don't care to include the empty cell B9, so we edit the range to read B3:B8, then click OK.Notice before going on that the dialog box also has a brief explanation of what the function we've selected does and what its arguments stand for. And it even tells us what the result would be for the arguments it has pre-selected for us.
Once we adjust the arguments to be the range B3:B8 and click OK, we get the result shown in the figure below. In a moment we will replicate the SUM function across the row to cells C10, D10, E10. But first, let's see how the SUM calculation differs from the previous calculation we made with the addition formula.
The SUM function is a convenient way to compute sums when we expect the amount of data in our spreadsheet to expand or contract in the future. The reason is that the SUM function automatically accounts for inserting and deleting data within the range of cells in its argument. The formula constructed with the + operator will not adjust for this accommodation.Let's modify our spreadsheet to illustrate. Suppose for a moment we had a special promotion sale at the end of February and we want to now include these sales figures as a separate row in our data. It is easy to add more data in a spreadsheet by inserting rows (or columns) into the worksheet.
The figure below shows how we would add a row after the February sales figure row. First you select the row before which you wish to insert a new row. Do this by clicking the row number at the right of the worksheet -- the entire row will be highlighted. Next you select the Rows choice from the Insert menu as shown in the figure. A new blank row will be inserted before the selected row.
To complete our modification we add appropriate data to new row 5, as shown in the figure below.Now notice (compare totals with those in the previous figure in Step 3) that the total for Region 1 is automatically updated to include the additional $10,000 we just entered in cell B5. However, the total for Region 2 does not include the new amount in cell C5.
The formula in cell C11, as you can see from the figure, changes to keep exactly the same values in the sum as were there before the insertion of the new row. The new value is omitted altogether. The SUM function incorporates the new row as part of its new range.
Hence the SUM function and the + operator are adjusted in different ways for the insertion of new data. We added the new row just for the purpose of making this point. Let's delete it before proceeding. To do this we select row 5 again (remember, click the row number on the left side) and then select Delete from the Edit menu.
As a final adjustment to the worksheet in this step, replicate the SUM function formula in cell B10 to cells C10, D10, and E10 (see Lesson 2 if you need to review this procedure).
Next, we'll add formulas to calculate the average monthly sales for all three regions. The following figure illustrates entering the appropriate formula for computing the Region 1 monthly average. Note that we're employing a built-in function AVERAGE. If we know the function's name and purpose already, we can simply type the formula. Or we could consult the function selection dialog box as we did for the SUM function earlier.In the figure below, we're entering the formula directly. The figure illustrates also that instead of typing in the range B3:B8, we can drag over that range of cells (note the dotted box surrounding the selected range) to indicate the range when the time comes for the function's argument to be entered in the formula. We would complete the formula shown below by typing in the closing right parenthesis and then clicking the green check mark to the left of the formula bar.
Once the average is computed for Region 1, you should replicate the formula to cells C12, D12, and E12 to compute monthly averages for the other regions and for the total monthly sales.
As a final illustration, let's suppose we wish to compute the standard deviation of the averages we just calculated. Mimic the procedure given in Step 5 and enter a formula employing the built-in function STDEV for computing the standard deviation for the Region 1 monthly average. Consult the figure below for help if you need it.Now replicate the standard deviation calculation to cells C13, D13, and E13.
The completed worksheet should have the following figures in it. Check your work against these numbers and locate and correct any errors. Note that the averages and standard deveiations have also been formatted as currency.
In this exercise you will will get a chance to practice employing an Excel built-in function on your own. Suppose you wish to compute the monthly payments you would have on a car you'd like to buy. Suppose the car costs $10,000 and you know you can secure a 10% annual interest rate loan for this amount.You'd like to do an analysis to decide what term (pay-back time) you should try to get. Of course the longer the term, the lower your monthly payments -- but the more interest you pay over the life of the loan. You want the shortest term for which you can still swing the monthly payments.
Set up a worksheet like the one in the figure to calculate the various payments for different terms. Note that you can type a long text string (like the one shown here in C2) and it will overflow to adjacent cells to the right as long as there is no date in those cells.
Excel has a built-in function for computing loan payments. It is called PMT and can be found in the Financial function list as shown below.
The arguments for the PMT function hold the interest rate (Rate) for the period in which you're interested -- a month in this case, the number of periods (Nper) you expect to pay, the present value (Pv) or amount of the loan, the future value (Fv) of the loan (this will be 0 if you intend to pay the loan off), and the type (Type) of payment to be made (this is 0 if your payments begin at the end of the month you get the loan; 1 if the payments begin immediately when you get the loan -- 0 is the most common option here).Convince yourself that the arguments for your function should be those shown below.
The completed worksheet should have the following figures in it. Check your work against these numbers and correct any errors. The parentheses and red type face indicates that these numbers represent a payment (negative).
*** End of Lesson 3 ***
