Excel Guide for Applied Calculus, GSGIS



Excel Guide for Mathematical Applications for Business

Robert L. Andrews

Introduction and Overview

In this class we will be using Excel as a computational tool. Excel is a spreadsheet and information is arranged in a matrix or two-dimensional arrangement of cells. The columns are labeled with letters (A, B, C, …, X, Y, Z, AA, AB, AC, etc. up to IV) and the rows are labeled with numbers (1, 2, 3, etc up to 65536). An individual cell can be referred to by using a letter followed by a number (A1, D10 or IV65536). Individual cells may contain either a number, text, or formula. Excel saves information in a workbook format with the possibility of several sheets being contained in a workbook. As with any book, you can only see the sheet or pages to which the book is opened. The sheets are labeled with tabs at the bottom of each. You may turn to the appropriate sheet by clicking on that sheet with the left mouse button. The buttons at the lower left with triangle shapes on them allow you to move through the sheets of the workbook and see tabs for other sheets than those currently being displayed. You can change the name of a sheet by double-clicking on the tab and then typing in a new name for the sheet.

Excel is a very powerful tool and we will only focus of a portion of its capabilities in this class. The instructors in this class do not purport to be Excel experts and welcome any hints that you may be able to share with the class. The instruction is not designed to demonstrate the knowledge of the instructor, but to provide information, exercises and activities to enhance the learning and knowledge level of those involved. This document assumes a minimum knowledge of using a mouse and working in Windows.

Graphic objects such as lines, graphs, text boxes and equations can be placed on a sheet but they are not contained in the cells. Positioning one of them over a cell does not affect the contents of the cell. As an example, equations, a graph and an arrow have been placed on the sheet in the insert on the next page for a file named DEMO1.XLS.

At the top of the screen there is a line of words that begins with File and ends with Help. This is referred to as the menu bar. By using the mouse and positioning cursor arrow on one these and clicking on the left mouse button, a drop-down menu appears with several options. Items listed in this menu in dark type can be selected with the mouse.

Under the menu bar there are several buttons with symbols on them. This is the toolbar area and clicking on the buttons will initiate different operations. These buttons can be changed by the user and may differ from machine to machine depending on whether a user has customized them. By positioning the cursor arrow on the button and holding it there, a box should appear with a brief description of what operation the button performs.

The arrows that appear to the right of the screen at the top and bottom of the column allow moving up and down on the sheet. The square that is between the two arrows indicates where the screen window is relative to the top and bottom of the sheet. Similarly, the arrows to the right and left on the lower right side of the screen allow for movement to the right or left. Positioning the cursor arrow on one of these arrows and clicking the left mouse button moves the screen up or down one row or right or left one column. Positioning the cursor arrow above or below the square that is on the right side and clicking the left mouse button moves the screen up or down to the next set of rows. Similarly, positioning the cursor arrow to the right or left of the square that is on the bottom of the screen and clicking the left mouse button moves the screen to the right or left to the next set of columns.

[pic]

When the cursor is moved onto the sheet in an area where a cell or a column or row of cells may be selected the cursor will change from an arrow to a cross, . Clicking the left mouse button on a cell allows information to be entered into a cell by typing on the keyboard. When you click on a cell, the letter and number indicating the selected cell appear to left on the line immediately above the row of letters that designate the columns for the spreadsheet, see the A15 above. Excel refers to this as the Name Box and immediately to the right of the name box is a triangle pointing down. Using the left mouse button click on this triangle and you can assign a name to the cell by typing in a name that can be up to 255 characters long. Remember that Excel does not case sensitive. This means that Excel cannot tell the difference between upper case and lower case letters. Hence for Excel, DONNA is the same as donna. The first character of a name must be a letter or an underscore character. Remaining characters in the name can be letters, numbers, periods, and underscore characters. Once you have clicked on a cell then if you move the cursor to the = to the right of the triangle, it becomes the Edit Formula button. Clicking on this button with the left mouse button allows you to select a function from the menu of Excel functions. The most recently used function will appear in the Name Box and by clicking on the triangle next to it you get an expanded menu of previously used functions. By clicking on More Functions in this menu you get access to an expanded menu containing all functions available in Excel. Also there are two additional symbols that appear once you click on the = or once you begin to type information into a cell. The first is an X in red that is a Cancel button that can be used to cancel what has been typed or selected. The second is a in green that is an Enter button that can be used to enter or record the typed or selected information into the selected cell. This can also be accomplished by hitting the Enter key on the keyboard.

If all of the characters entered into a cell are numbers then calculations may be performed on the number. If the width of the number exceeds the width of the cell then a series of symbols will appear on the sheet in this cell. The actual number is correctly entered in the cell and calculations using that cell will be correct even though the number is not displayed. The cells in a column can be widened by moving the cursor to the column headings and when the cursor is on the dividing line between the columns the symbol changes to have a double headed arrow, except there is a vertical line in the middle. If you do this on the rows then the arrows are vertical and the line is horizontal, . When this symbol appears you can change the width of the column or the height of the row by holding down the left mouse button and moving to make the cell larger or smaller in that direction. Once the button is released the entire row or column of cells will take on the indicated size. You may restore or undo what you last did by selecting Edit in the menu bar and then Undo in the drop-down menu. This is important to learn because it can be used to undo entries or adjustments you make to a sheet if you want to return it to its previous state.

If any of the characters entered into a cell are letters or symbols then the information will be entered as text and no calculations may be performed on the contents of this cell. The exception is when the first character entered is an equal to sign, =. This allows the entering of formulas or functions. If the formula or function produces a number then calculations may be performed on that cell.

Clicking and Dragging

Many things are made easy in Excel by clicking and dragging with the mouse. A cell can be selected by placing the cursor on the cell and clicking. If you hold down on the left mouse button and drag the cursor then a rectangular array of cells can be selected by releasing the left mouse button. The cells A1 through A6 have been selected in the figure to the right. These cells may be moved to another location on the sheet by moving the mouse along the edge of the selected area until the cursor changes to a white arrow, . When this arrow appears, hold down the left mouse button and drag the cells to the desired location and release the button to fix the cells in this new location on the sheet. The menu appearing beside the highlighted cells is obtained by placing the cursor in the selected area and clicking on the right mouse button. The cursor can then be moved to an item in the menu. Clicking either the right or left mouse button will select that item.

Notice the small square in the lower right corner of the selected area that is being pointed to by the arrow. By moving the cursor to this square the symbol will become a dark cross, , rather than a larger white one or a white arrow. When the dark cross appears, the selected area can be copied by clicking the left mouse button and dragging. For example, if the mouse is clicked on the square shown and drug to the right then numbers in column A would be copied into column B or to multiple columns as so desired. If rather than being drug over, the mouse is drug down to row 7 and released then column A would have the same numbers as column F. If there is a sequence or series of numerical values with equal distance between the values in a direction then Excel extends the series as the mouse is drug in that direction. Selecting cells D1 and E1 and dragging down through row 7 created columns D and E. A single number does not constitute a series and the value 1 was copied into all the cells in that column. Mon is one of several words that Excel recognizes as being in a natural sequence. Consequently, Tue through Sun appear in rows 2 through 7. If Monday were typed in rather than Mon, then Tuesday through Sunday would be the result of clicking and dragging. Excel will do the same for the names of the months and their abbreviations, such as January and Jan. To use this feature to list the years from 1980 to 1995, the first two years must be entered to establish a series of numbers. This aspect of Excel is not limited to dragging down or to the right; it can be up or to the left.

Typing in the values in the first two columns created columns F and G; they were selected and the mouse was drug down through row 7. Notice how Excel recognized that X1 and X2 initiated a sequence, but that A and B did not. For this reason A and B were repeatedly copied down the column.

If the distance between numerical values in a sequence is not the same then Excel may give you some unusual results. This can be demonstrated by entering 1, 4 and 9, which are the squares of the integers 1, 2 and 3. The examples discussed had an interval of one between values but it can be 101 or .05.

Mathematical Operators

To perform mathematical operations Excel has a set of symbols that are used for these operations. Addition is indicated by + and subtraction is indicated by -. By entering = a1+b1-c1 into a cell Excel will add the contents of A1 and B1 and then subtract the contents of C1 and place the result into the cell. Multiplication is indicated by * and division is indicated by /. Exponents are indicated by ^. Entering =a1^b1 will take the contents of A1 and use the contents of B1 as an exponent. Excel is not sensitive to whether the letters are entered in upper or lower case. The order of mathematical operations is 1) exponents, 2) multiplication and division and 3) addition and subtraction. For example, entering =-4*2+3^2/2-1 would yield -4.5.

Help Function

On screen help most easily accessed in one of two ways. 1. Clicking on Help in the menu bar gives a drop-down menu that provides several choices: Microsoft Excel Help, Contents and Index, What's This?, Microsoft on the Web and Lotus 1-2-3 Help… 2. Pressing the F1 function key enters Microsoft Excel Help directly. These different options provide different types of assistance with Excel

Absolute and Relative Addressing of Cells

Mathematical functions may be specified using the contents of other cells as discussed above and were used to obtain the values in the figure on page 2. Suppose that the A column contains a set of ten values for a variable in A1 through A10 and the total is in A11. Entering =A1^2 into cell B1 will give the square of the value in A1. Clicking on this formula and dragging it down through B10 will give the square of each corresponding value in the A column. For example, B7 will contain the formula of =A7^2. Excel automatically changes the row number. If the dragging is to the right or left then the letter indicating the column is changed. Dragging B1 to the right will place =B1^2 in C3 and as a result C3 will contain the contents of A1 squared twice or A1 to the fourth power. The same results can be obtained through the use of the copy and paste functions.

In some situations it is not desirable to have Excel to automatically change the row or the column as cells are copied. Suppose it is desired to have the percentage of the total for each amount in column A where the total is in A11. Entering =100*A1/A11 into cell B1 will give that percentage. If this cell is copied into B2 then it becomes =100*A2/A12 which will not divide by the total which is in A11. If A12 is blank the result will show #DIV/0!. This can be avoided by changing the row portion of the address of the cell from relative to absolute. This is done by placing a $ in front of the row number, =100*A1/A$11. When this cell is copied to B2 it becomes =100*A2/A$11. This means that the row address for the denominator is absolute and does not change relative to the cell. Placing a $ in front of the column indicator, $E1, makes it absolute and means that it will not be changed by copying. Both the column and the row can be absolute, $C$9. The $ symbol can be typed in or if a cell address has just been entered then the F4 function key at the top of the keyboard can be used to obtain the desired address. For example by successively pressing the F4 key the following sequence of addressed will appear: A1, $A$1, A$1, $A1, A1. You should stop when the desired address is shown.

Cut [pic], Copy [pic] & Paste [pic] Commands

To either cut or copy text or graphics, they must first be selected. Clicking on either the cut or copy button in the toolbar or choosing either Cut or Copy from the Edit menu puts selected text and graphics on the Clipboard. The Clipboard is a temporary holding area for data that have been sent to it by either the Cut or Copy commands. The data remain there until they are replaced with something new. Choosing the Paste command pastes the contents and formats of copied or cut cells from the Clipboard into the selected area. If the paste area is a single cell, this cell is the upper-left corner of the paste area; the rest of the copied data is pasted below and to the right of this cell. If the data were sent to the Clipboard with the Cut command, the paste area must be the same size and shape as the cut area.

Printing [pic]

Selecting Print from the File menu or selecting the print button [pic] from the toolbar gives the drop-down Print menu. The sheets, pages and number of copies to be printed can be selected from this menu. It is also possible to preview the pages in the form they will be printed prior to printing.

Saving [pic], Opening [pic] and Closing Workbook Files

Information in a new workbook can be saved by selecting Save or Save As... from the File menu or by clicking on the save button in the toolbar. The first time a workbook is saved, the Save As menu appears so you can change the name, such as book1.xls, that Excel has assigned. After a file has been saved once, the Save command saves the current workbook with the same name in the same location. Either can be changed by selecting Save As from the File menu. To close a workbook select the File menu and choose Close from it. If there are unsaved changes, Excel will ask if the changes are to be saved, otherwise the workbook file is closed. A workbook that has been previously saved and closed can be opened by first either selecting Open from the File menu or the open button on the toolbar. This causes the Open menu to appear and the location and file name can be selected in it.

Formatting Cells

The font and the size can be changed by highlighting cells then either by using the buttons and menus in the toolbar if they are displayed or by clicking on Format and selecting Cells... from the drop-down menu. The font, font style, color and font size can all be changed for the selected cells by clicking on the Font tab in the Format Cells menu that appears. The way numbers are displayed in the cells can be changed by clicking on the Number tab in this same menu. The color or patterns used for the selected cells can be changed under the Patterns tab. A border can be placed around selected cells from the menu under the Border tab, or form the borders menu in the toolbar, if it is shown. Clicking on the Alignment tab allows one to change the location of the numbers or text in selected cells. To change horizontal alignment from the toolbar click on the [pic] button to left justify the contents of the selected cells, the [pic] button to center the contents of the selected cells, and the [pic] button to right justify the contents of the selected cells.

The Goal Seek Function for Solving Equations

Excel has a function called Goal Seek that can to solve for the value of X that will give Y a specified value where Y = f(X). To use this function a value for X is entered into a cell on the spreadsheet and the function is entered into another cell. Suppose the function is Y=X2. The value of 5 can be entered into the cell A1 and the formula =A1^2 entered into the cell B1. Since 52 = 25, the value in B1 is 25. I want to find the value of X that will make Y=4. To use Goal Seek to accomplish this place the cursor on the cell B1, left click on Tools at the top and a menu will appear. Select Goal Seek from that menu and the Goal Seek box to the right will appear. Move the cursor to the To value: input area or box and enter the number 4. Next move the cursor to the By changing cell: input area or box and either left click on the cell A1 or enter A1 in this area. Hit the Enter key or click on OK. A box like the one here to the left will appear with a solution. To keep the solution click on OK and the value of X is in the cell A1 and the corresponding value of Y is in B1. To return to the original values in both A1 and B1 click on Cancel. The correct value for Y is exactly 4 and a value of exactly 2 for X will make Y=4 exactly. Goal seek may not find exact solution values because it uses an intelligent trial and error type procedure to find a solution and reports a solution when it decides that the answer is close enough to the specified value. For Y=X2, there are two values of X, 2 and –2, that make will Y=4. Goal Seek will use the current value of X, which was 5 in this case, and find the solution closest to it. If the value of X is –5 then Goal Seek will give a value of X close to –2. This is a powerful tool that will solve for X even if Y is a complicated function of X.

Graphing [pic] Functions

Graphs such as the one in the figure on page two can be obtained by using the ChartWizard button similar to the one shown in the title above. The first step is to select the cells containing the information needed to generate the chart. For example in the figure on page 2 the cells A2 through D11 were selected. Once the cells have been selected then click on the ChartWizard. A menu of chart types will appear. For the desired chart select XY (Scatter). Five Chart sub-types will appear on the right side of the menu. Select the one in the lower right corner to duplicate the figure on page 2. If you want the points to be visible select the one in the lower left corner. Click on Next > to move to the next step that shows an example of the chart. On the page with the Data Range tab you can change the data range and whether the data are in columns or rows. On the page with the Series tab you can the X values or the Y values. Clicking on Next > moves to step 3. On the page with the Titles tab you can add a chart title, an x axis title or a y axis title. The page with the Axes tab allows you turn off labeling for an axis. The page with the Gridlines tab allows you turn off gridlines on the chart. The page with the Legend tab allows you turn off displaying of the legend or select where it is placed. The page with the Data Labels allows the values or labels for points to be displayed on the chart. Clicking on Next > moves to step 4. This menu allows you to choose between placing the chart in a specified sheet or on a separate sheet as a chart. Once everything is OK, click on Finish.

After a chart has been placed it can be edited. To edit the size or location of a chart on a sheet click on the chart to get eight black squares, one at each corner and one at the middle of each side. Holding down the left mouse button can change the location of the chart on the sheet while the cursor is on the chart. The cursor will change into a cross shape with arrows pointing up, down, left and right. With the button held down, move the chart to the desired location and release the button. To change the size of the chart move the cursor to the black square in the direction you want to change. By holding down on the left mouse button you may either expand or contract the chart in the desired direction. By selecting a corner you may move either up, down, right or left. By selecting a square on the side of the chart you may only move in one direction. A good guideline for changing other things on a chart is to move the cursor to that part and then double click (click twice) on it. Most often a menu will appear that will allow you to make changes in that part of the chart.

Excel is a very powerful tool for calculations and creating graphical displays. This handout has only touched on some of the basics but it will give you a start. The only way to learn is by trying. If you want to try something new on information you do not want to lose it is always wise to save before trying something. If you mess it up then you can retrieve what you have saved. In fact, a good rule is to save frequently. That way if there is a power outage or something that would cause you to lose what has been entered you can go back and retrieve what you have saved.

Hints for Graphing Functions

In setting up a sheet to create a table of values and graphs that may be generated from the table it is best to place all values at the top of the page that you may want to change. For example you want to place the values at the top of the page that will allow you to move the window in which you are observing a function. I think it is best to use the CENTER of the window and the WIDTH of the window as the as controlling parameters rather than using the minimum and maximum values for X. You can determine the minimum value for X with Xmin=CENTER-WIDTH/2 and correspondingly Xmax=CENTER-WIDTH/2 would be the maximum value of X. The increment to used to determine the distance between values of X would be INCREMENT = WIDTH/(number of X intervals). Using 20 intervals gives reasonable definition of the curve for most graphs; this can be increased to 50 to achieve better definition.

Pasting Mathematical or Financial Functions into a Cell

By clicking on the Paste Function Button shown above a menu of functions to the right will appear. The SQRT function highlighted in this box will find the square root of a number or of the value in a cell. The box above will appear by clicking on OK. The number or cell can be entered in the input area or box. If the cell A1 is entered and the Enter key is hit or OK clicked then the formula =SQRT(A1) is entered into the cell and it will contain the value of the number in cell A1 if that number is a nonnegative number. Excel has an extensive library of functions that can be used in similar fashion. If a function is to calculate the values for an array of cells or matix then the user must hold down simultaneously the Ctrl and Shift keys while either hitting the Enter key or clicking on OK. An example of such a function is the MMULT function which performs matrix multiplication.

................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download