2. Formulas and Functions
|
|  Yüklə 324 Kb.
|
2.Formulas and FunctionsSo far we’ve used Excel basically as a way to input, arrange and format static information. But a spreadsheet comes alive through the use of formulas and functions. As the name suggests, formulas basically correspond to some instructions that tell Excel how to combine information (numerical or text-based) into a result (e.g., 1 + 1 = 2). Functions use some built-in procedures to apply more complicated transformations (e.g., sqrt(4) = 2). Basic FormulasLet’s say we have information about the sales of a software package for 1998 and 1999, separately for the Eastern and Western sales region. The software has sold for $200 in both years and in both markets (see the following figure and the Software worksheet in the Intro to Excel workbook). 
Figure 2 1 The company would like to know the total sales for each region (over the two years) and for each year (over the two regions). Let’s look at three different ways that this can be done.
Note: Every formula and function in Excel starts with an “=” sign!
Figure 2 2
Figure 2 3 Let’s calculate total revenues for each year.
Oops. Cell E5 shows a total revenue of $0 for 1999. We know that this can’t be true. Since we used relative cell referencing in the copying process, Excel adjust the formula to =B2*D5 and of course there is no information in cell B2. We need to use absolute references to cell B1 to make sure that it won’t be changed once we copy the formula from D4 to D5.
Figure 2 4 Note: You can mix absolute and relative references. For example, since we only changed the row position in the copying process, we just need to make sure that the formula always refers to row 1. The formula =B$1*D4 would have done the trick. Let’s assume that the goal for each year was to exceed $750,000 in total revenue. We’ll use Excel’s =IF() function to alert us whether we did or did not achieve that goal.
The =IF() function takes three arguments: the first one is a condition that either evaluates to be true or false; the second argument gives the result if the condition is true, the third argument gives the result if the condition is false. Since the value in E4 is less than $750,000, the condition is false and Excel will report “Panic” as a result.
Figure 2 5 Using FunctionsLet’s say we wanted to calculate the average sales for each region. Excel has hundreds for built-in functions, ranging from simple arithmetic operations to fairly sophisticated financial and statistical functions. You can access these functions either by choosing Insert Function … or by clicking on the Paste Function button (  ) on next to the formula bar.
Note: Excel offers a brief description of the selected function, including its required arguments. For more help, click on the help link in the lower left-hand corner. 
Figure 2 6
Excel now opens a dialog that allows you to specify the arguments for the AVERAGE function. As you can see in the following Figure, Excel “looked around” in the neighborhood of B7 to find numbers to average. We need to change that since we only want to average the two sales figures in B4 and B5. 
Figure 2 7
Your worksheet now shows the average sales for the Eastern region in cell B7. 
Figure 2 8 ExerciseUse the =PMT(rate, nper, pv, fv, type) function to set up a mortgage payment calculator as shown in the following figure. The =PMT() function is available from the Financial functions and you only need to specify the first three parameters. Use Help if you need more information. 
Figure 2 9 |
) in the Standard toolbar. Excel automatically pastes the SUM function into cell C6 and selects the range C4:C5 as the arguments for this function. Press 
) to minimize the dialog box and get access to your spreadsheet (where you can highlight the range from 
to get back to the dialog box).