**Introduction**

**The ability to perform calculations is one of the purposes of using a spreadsheet application.**

**Some examples of the types of calculations that can be done are:**

**ˆ totals**

**ˆ subtotals**

**ˆ average**

**ˆ standard deviation**

**In Excel,the calculation can be specified using either a formula or a function.**

**ˆ Formulas are self-defined instructions for performing calculations.**

**ˆ In contrast, functions are pre-defined formulas that come with Excel.**

**In either case, all formulas and functions are entered in a cell and must begin with an equal sign ’=’.**

**Entering Formulas**

**After the equal sign, a formula includes the addresses of the cells whose values will be manipulated with**

**appropriate operands placed in between. The operands are the standard arithmetic operators:**

**! Practice Exercise: Enter the following information into a blank worksheet (ignore any formatting) in**

**columns A, B, and C, and in rows 1 through 6. Then calculate the Total Cost for the the Fall semester using**

**a formula to add up the individual Costs (Tuition, Housing, etc.).**

**ˆ The most logical solution would be to type the formula ’=B2+B3+B4+B5’ into cell B6.**

**You can also enter formulas by using the point mode, where you either click on a cell with your left mouse**

**button or you use the arrow keys.**

**ˆ To enter ’=B2+B3+B4+B5’ into cell B6 using the point:**

**– Left click on cell B6 to make it active.**

**– Type ’=’.**

**– Use the up arrow key to move to cell B5, or left click on cell B5.**

**– Type ’+’.**

**– Use the up arrow or mouse to add cells B4, B3, and B2 in the same fashion.**

**– Press Enter when you are finished entering the formula.**

**You’ll notice that the calculation executes immediately after the formula is typed into the cell and entered**

**by pressing either the Enter or Tab key or by clicking the check mark in the formula bar.**

**ˆ The result of the calculation is displayed in the cell (i.e., in B6).**

**ˆ The formula itself is now visible in the formula bar.**

**What happens if you enter ’B2+B3+B4+B5’ without the leading equal sign ’=’ into cell B6?**

**ˆ**

**REMEMBER: All formulas and functions must begin with a ’=’.**

**ˆ The text of the formula or function will be displayed in the cell if you do not use an ’=’ and the**

**calculation will not be executed.**

**IMPORTANT: Why use cell references (i.e., cell addresses; e.g., B2 or C5) in formulas instead of the**

**actual values of the cells (e.g., 10000 or 700)? The answer: automatic calculation. Let’s illustrate the concept**

**of automatic calculation with a practice exercise:**

**! Practice Exercise: Let’s make a change to the Costs for the Fall and Spring semester spreadsheet and note**

**how Excel automatically recalculates the formulas and functions we have already entered.**

**ˆ Change the amount entered in cell C2 from ’10000’ to ’15000’.**

**ˆ Notice how all the calculations referencing cell C2 automatically changed.**

**To reiterate the use of cell addresses in formulas and functions, imagine we had constructed our formulas**

**and functions by typing the actual numbers contained in the cells instead of the cell addresses. That**

**is, to calculate the Total Cost for the Fall semester we had entered the formula ’=10000+5000+1000+1500’.**

**What would happen if we changed the amount entered in cell C2 from ’10000’ to ’15000’ as suggested above?**

**Obviously, our calculation defined by our formula would not automatically change, and we would have to edit**

**the formula by hand. Imagine if the cell we modified was referenced by ten separate formulas or functions.**

**That would be a lot of editing.**

**In summary, automatic calculation is activated, Excel automatically recalculates the result of any formulas**

**as cell entries change. Automatic calculation also applies to functions, which we’ll be covering in the next**

**section.**

**Side note: You can check to make sure automatic calculation is turned on:**

**ˆ Select Tools from the Options drop menu.**

**ˆ Click on the Calculation tab.****ˆ Select Automatic if it is not already selected.****ˆ Click OK.**

**Entering Functions**

**Functions differ from regular formulas in that, after the equal sign, you supply the cell addresses but not**

**the arithmetic operators. Functions perform calculations by using specific values, called arguments, in a**

**particular order called syntax. When using a function, remember the following:**

**ˆ Use an equal sign to begin the function.**

**ˆ Specify the function name.**

**ˆ Enclose all of the function’s arguments within parentheses.**

**ˆ Use a comma to separate the function’s individual arguments.**

**! Practice Exercise: In the Costs for the Fall and Spring semester spreadsheet, calculate the Average Cost**

**of Tuition for the Fall and Spring semesters using the AVERAGE function.**

**ˆ The most logical solution would be to type ’=AVERAGE(B2, C2)’ into cell D2.**

**– The equal sign begins the function.**

**– AVERAGE is the name of the function.**

**– B2, and C2 are the arguments.**

**– Parentheses enclose all of the arguments.**

**– Commas separate each of the arguments.**

**Reference Operators**

**Reference operators refer to a cell or a group of cells. There are two main types of reference operators:**

**1. Range:**

**ˆ Refers to all of the cells between and including the reference.**

**ˆ Consists of two cell addresses separated by a colon.**

**ˆ EXAMPLE: ’A1:A3’ includes cells A1, A2, and A3.**

**ˆ EXAMPLE: ’A1:C3’ includes cells A1, A2, A3, B1, B2, B3, C1, C2, and C3.**

**2. Union:**

**ˆ Includes two or more references.**

**ˆ Consists of two or more cell addresses separated by a comma.**

**ˆ EXAMPLE: ’A7, B8, C9’ refers to cells A7, B8, and C9.**

**ˆ EXAMPLE: ’A7, B8:D9, E4’ refers to cells A7, B8, B9, C8, C9, D8, D9 and E4.**

**In the function practice exercise, we used a union reference when we typed ’=AVERAGE(B2, C2)’ in cell**

**D2, but we could have used a range reference instead: ’=AVERAGE(B2:C2)’.**

**Autosum**

**You can use the Autosum icon on the standard toolbar, which automatically adds the contents of a cluster**

**of adjacent cells.**

**ˆ Select the cell that the sum will appear in that is outside the cluster of cells whose values will be added.**

**ˆ Click the Autosum button (Greek letter sigma, ).**

**ˆ Highlight the group of cells that will be summed.**

**ˆ Press the Enter key on the keyboard or click the green check mark on the formula bar.**

**! Practice Exercise: In the Costs for the Fall and Spring semester spreadsheet, calculate the Total Cost**

**for the the Spring semester using the Autosum icon.**

**ˆ Click on cell C6 to activate it.**

**ˆ Click the Autosum button.**

**ˆ Highlight cells C2 through C5.**

**ˆ Press Enter.**

**Function Wizard**

**You can access all of the available functions in Excel using the Function Wizard.**

**ˆ Select the cell where the function will be placed and click the Function Wizard button on the standard**

**toolbar.**

**ˆ Other ways of starting the Function Wizard are:**

**– Select Function from the Insert drop menu.**

**– Click on the drop down arrow next to the Autosum icon button.**

*** You will first see the commonly used functions in Excel, and at the bottom of the menu, the**

**More Functions option.**

*** Clicking on More Functions will give you an alphabetical and categorical listing of all available**

**functions in Excel.**

**From the Paste Function dialog box, browse through the functions by clicking in the Function category**

**menu on the left and select from the Function name choices on the right.**

**ˆ As each function name is highlighted a description and example of use is provided below the two boxes.**

**Clicking OK to select the function, opens the Function Arguments dialog box, which allows you to choose the**

**cells that will be included in the function.**

**ˆ As in the last previous function, we can type the cell addresses in the necessary argument boxes.**

**ˆ We can also enter the cells using the point mode (i.e., the left mouse button).**

**– Click and drag across a group of cells to enter a range of cells addresses.**

**– Use the Ctrl key and mouse to enter a union of cells addresses.**

**ˆ Excel may automatically select cells for you, but you can delete those selected cells from the argument**

**box and enter the desired cell addresses.**

**Click OK when all the cells for the function have been entered into the necessary argument boxes.**

**! Practice Exercise: In the Costs for the Fall and Spring semester spreadsheet, calculate the average Housing**

**cost for the Fall and Spring semesters using the Function Wizard and the AVERAGE function.**

**ˆ Click on cell D3 to activate it.**

**ˆ Activate the Function Wizard.**

**ˆ Choose the AVERAGE function from the ’Most Recently Used’ Function category.**

**ˆ Using the mouse, highlight cells B3 and C3, or type ’B3:C3’ in the Number1 argument box.**

**ˆ Click OK to execute the calculation.**

**All other functions can be entered and executed in a similar fashion.**

**A Function instead of a Formula**

**Functions can be a more efficient way of performing mathematical operations than formulas. Specifically, in**

**many cases, a function will simplify formulas that you can type in manually, such as average or sum.**

**ˆ EXAMPLE: If you wanted to add the values of cells D1 through D10, you could type the formula**

**’=D1+D2+D3+D4+D5+D6+D7+D8+D9+D10’, or a shorter way would be to use the SUM function**

**and simply type ’=SUM(D1:D10)’.**

**The following table contains the definitions and examples of several other available functions:**

**Function Example Description**

**SUM =SUM(A1:A100) Finds the sum of cells A1 through A100.**

**AVERAGE =AVERAGE(B1:B10) Finds the average of the cells B1 through B10.**

**MAX =MAX(C1:C100) Returns the highest number from cells C1 through C100.**

**MIN =MIN(D1:D100) Returns the lowest number from cells D1 through D100.**

**SQRT =SQRT(D10) Finds the square root of the value in D10.**

**TODAY =TODAY() Returns the current date (leave the parentheses empty).**

## 0 comments:

## Post a Comment