VIEWS: 17 PAGES: 42 CATEGORY: Business POSTED ON: 2/3/2011
Excel Amortization Tables 175 document sample
DATA MANAGEMENT FUNDAMENTALS FOR THE BIOLOGICAL SCIENCES From Data Summarization Through Statistical Analysis To Presentation The Johns Hopkins School of Medicine Department of Physical Medicine and Rehabilitation Keith V. Kuhlemeier, Ph.D., M.P.H. 410-532-4700 410-532-4770 (Fax) kkuhleme@jhmi.edu 1 DATA MANAGEMENT FUNDAMENTALS FOR THE BIOLOGICAL SCIENCES I. Spreadsheets – Microsoft Excel The Johns Hopkins School of Medicine Department of Physical Medicine and Rehabilitation 2 Word\Department\Excel Handout.Doc Layout of a Worksheet Screen Standard Tool Bar Formatting Tool Bar Drawing Tool Bar Menu Bar Formula for Active Cell Active Cell (D10) Columns J, K & L Rows 11, 12 & 13 Range A15:D26 Scroll Bars The formula for cell D5 is =PMT(D3/12,D1,-D2*(1+D4),0,0) Worksheet Tab Truisms in Learning New Software Programs 1. You learn only by doing; you can‘t learn to use software by reading about it any more than you can learn neurosurgery by reading neurosurgery texts. 2. Frustration is part of learning; once you work past that, it starts to be fun and rewarding and gives you additional, very powerful tools. 3. Don‘t try to learn everything at once. Start with the simplest basics and learn new techniques as the need arises. The more you learn, the easier it gets as you begin to see patterns. But push too hard or try to remember too much all at once and you‘ll just frustrate yourself. 3 4. A good manual is indispensable. Go to a bookstore and check out several manuals before buying one. Get a manual with the most detailed instructions you can find with many specific examples. It is not fiscally prudent (or good for your mental health) to save a few dollars on a manual and then waste hours trying to get Excel to do what you want it to do. Don‘t rely on the help that‘s integral to the software – it‘s frequently (perhaps usually) very terse and often more frustrating than helpful. You can usually get better prices on books from places like Amazon.com or e-bay.com than from the local bookstore. 5. Ask others for help. Someone can show you in one minute what might take you an hour to figure out alone. Most people who use software are actually eager to help you (and in the process show how much they know). 6. If you have a seemingly intractable problem, walk away for a day and then try again. Often the solution will come when you‘re not consciously thinking about the problem. 7. Don‘t be afraid to experiment – but make sure you save the file you‘re working on first in case things don‘t work the way you expect. You can probably use ―undo‖ if you fail to save before experimenting – but don‘t count on it. 8. If you have a problem with the computer locking up, turn it off and reboot after saving the file you‘re working on (or attempting to save the file - it may not be possible to successfully save the file). This solves about 98% of all computer lock-up problems. 9. From time to time, click on an unfamiliar feature from the menu bar or one of the tool bars, just to see what options are available. Excel has hundreds, if not thousands, of features, most of which you will never use. Still, it‘s useful to know what‘s there. 10. The topics covered are limited by time constraints and cover a bare minimum of Excel‘s features. If you have questions or would like specific features covered, please ask. 11. The main function of a spreadsheet such as Excel is to perform arithmetic operations. Once you set up the formulas to carry out the operations, it is very easy to copy these formulas to other cells. Example: I have a column of 5,000 body weights and 5,000 corresponding body heights in 5,000 rows in a worksheet. To calculate the body mass index for all 5,000 rows, I need type out the formula for body mass index only once. I can then copy the formula to the remaining 4,999 cells in a few seconds and the calculations will be done almost instantaneously. Definitions Row: The horizontal area to the right of the number on the left margin of a spreadsheet. Example: 1, 2, and 3 are the first three rows on a spreadsheet. Column: The vertical area below a letter on the upper margin of a spreadsheet. Example, A, B, and C are the first three columns of a spreadsheet. Case (upper or lower) is not important in Excel, i.e., column A is the same as column a. Cell: The intersection of a row and column. Example: A1 is the first cell in the upper left corner of a spreadsheet. Cell reference: The location of a cell on a spreadsheet. Example: The cell reference for the cell in the fifth column (E) and the 4th row (4) would be E4 (or e4). Active cell: The cell on which the cursor is located. Example: If the cursor is on the second row and third column, the active cell will be C2. 4 Range: A collection of contiguous cells. Examples: A1:C5 is the area comprising the rectangle of cells with A1 in the upper left corner of the range and C5 in the lower right corner of the range. This range consists of 15 cells (three columns and five rows). Range R21:R40 consists of the 20 cells beginning in column R, row 21 and ending in column R, row 40. References: NOTE: The differences between relative, absolute and mixed references are extremely important. Failure to note the differences can result in grossly wrong calculations. To make the distinctions between these references clear, several examples will be given below to highlight the differences. Relative references: A relative cell reference in a formula, such as A1, is based on the relative position of the cell that contains the formula and the cell the reference refers to. If the position of the cell that contains the formula changes, the reference is changed. If you copy the formula across rows or down columns, the reference automatically adjusts. By default, new formulas use relative references. Example: Suppose you sum the values in values cells A1 and A2 using a formula (=A1+A2, the sum of the contents of the two cells directly above cell A3) and enter the formula into cell A3. If you then copy the formula in cell A3 to cell B3, the formula will automatically be adjusted to sum the values in cells B1 and B2 (=B1+B2, the sum of the contents of the two cells directly above cell B3). When formulas are copied in Excel, by default the formulas are automatically adjusted to correspond to the positions to which they are copied. This automatic adjustment is called relative reference A B C D 1 1 3 5 7 2 2 4 6 8 3 =A1+A2 =B1+B2 =C1+C2 =D1+D2 A B C Shaded cells are those that 1 1 2 =A1+B1 result from default relative 2 3 4 =A2+B2 copying of cell A3 (above) or 3 5 6 =A3+B3 cell C1 (right) 4 7 8 =A4+B4 Absolute references: An absolute cell reference in a formula, such as $A$1, always refer to a cell in a specific location. If the position of the cell that contains the formula changes, the absolute reference remains the same. If you copy the formula across rows or down columns, the absolute reference does not adjust. By default, new formulas use relative references, and you need to switch them to absolute references. For example, if you copy a absolute reference in cell B2 ($B$2) to cell B3, it stays the same in both cells Mixed references: A mixed reference has either an absolute column and relative row, or absolute row and relative column. An absolute column reference takes the form $A1, $B1, and so on. An absolute row reference takes the form A$1, B$1, and so on. If the position of the cell that contains the formula changes, the relative reference is changed, and the absolute reference does not change. If you copy the formula across rows or down columns, the relative reference automatically adjusts, and the absolute reference does not adjust. If you want to lock the column but not the row in copied formulas, put a $ in front of the column location. Example: $C4. If 5 you want to lock the row but not the column in copied formulas, put $ before the row. Example: C$4. If you want to lock both the row and the column in copied formulas, put $ before both the row and the column. Example: $C$4. Consider the following table. The formulas given under Total Sales are calculated from the entries in the cells, i.e., the sales for bread on Monday is the contents of cell B3 multiplied by the contents of cell C3; sales for bread on Tuesday is the contents of cell B3 multiplied by the contents of cell D3 and so on. Note that as we move from cells I2 to N2, ‗B3‘ remains constant for all cells. As we move from cells I3 to N3, ‗B4‘ remains constant for all cells and so on down through row 6. Thus as each row is copied to columns to the right, we want to fix ‗B‘ but not the column that contains the unit sales. To fix column B and write a formula that will copy properly to columns to the right of cell H3, we can use the following: =$B3*C3. Will this formula copy properly for rows 4 through 6? Yes. For row 4 the formula will copy as =$B4*C4 since we have fixed the column with the $ before B (B will not increment) but we have not fixed the row with a $ before the before the 3 in cell I3. Hence as we pass from row 3 to row 4, the row in the formula will increment, even though the column won‘t. A B C D E F G H I J K L M N 1 Unit Sales Total Sales 2 Item Cost Mon Tue Wed Thu Fri Sat Mon Tue Wed Thu Fri Sat Bread 3 (loaf) $3.50 95 80 75 73 80 202 B3*C3 B3*D3 B3*E3 B3*F3 B3*G3 B3*H3 Rolls 4 (dozen) $2.96 24 32 26 40 20 65 B4*C4 B4*D4 B4*E4 B4*F4 B4*G4 B4*H4 Cookies 5 (doz) $1.49 32 30 41 39 35 40 B5*C5 B5*D5 B5*E5 B5*F5 B5*G5 B5*H5 Cakes 6 (each) $5.00 16 18 18 12 19 25 B6*C6 B6*D6 B6*E6 B6*F6 B6*G6 B6*H6 Consider the following range of cells in a more complicated situation. The formula in cell H3 could be =B3*(1+C3)*D3 or the cost of the splint (B3) adjusted for the fractional markup (1+C3) multiplied by the number of splints sold (D3). If we copy the formula in cell H3 to cell I3 using relative reference the formula in cell H4 will be =C3*(1+D3)*E3. This relative formula will not give us the charges for splint 1 for February. A correct formula would be =B3*(1+C3)*E3. Columns B and C should not change but column D should be replaced by column E. Furthermore, row 3 should be replaced by rows 4 through 7 for splints 2 through 5. If we use the formula =$B3*(1+$C3)*D3 in cell H4, we can copy this formula to the remainder of the cells in the range of cells H4 to K7 and get the correct answers. We must use mixed cell references (relative row references, absolute column references) to get the desired result A B C D E F G H I J K 1 Wholesale Fractional Units Sold Retail Charges 2 Splint Cost Markup Jan Feb Mar Apr Jan Feb Mar Apr 3 1 $20 0.1 3 7 2 5 66 154 44 110 4 2 $30 0.2 5 4 1 2 180 144 36 72 5 3 $40 0.15 1 2 2 3 46 92 92 138 6 4 $50 0.4 3 2 6 4 210 140 420 280 7 5 $60 0.33 3 4 2 5 239.4 319 159.6 399 Now consider the same information as in the table above but with the rows and columns interchanged. 6 A B C D E F G 1 Splint 2 1 2 3 4 5 3 Wholesale Cost $20 $30 $40 $50 $60 4 Fractional Markup 0.1 0.2 0.15 0.4 0.33 5 Jan 3 5 1 3 3 Units Sold 6 Feb 7 4 2 2 4 7 Mar 2 1 2 6 2 8 Apr 5 2 3 4 5 Retail Charges 9 Jan 66 180 46 210 239 10 Feb 154 144 92 140 319 11 Mar 44 36 92 420 160 12 Apr 110 72 138 280 399 The retail charges for January (cell C9) can be calculated from the formula =C3*(1+C4)*C5 or $20*1.1*3 = 66. If we copy this formula with a relative reference to cell C10 we get the formula =C4*(1+C5)*C6 which is clearly gives a wrong calculation. A formula that would give the correct calculation is =C3*(1+C4)*C6. But we cannot use copy and paste to give us this formula. A formula that we can use to copy and paste uses absolute references is =C$3*(1+C$4)*C5. This formula when entered into cell C9 can be copied and pasted into the remaining cells of the range C9:G12. We must use mixed cell references (absolute row references, relative column references) to get the desired result. Finally, consider the situation in which the fractional markup is the same for all the splints, say 20%. This fractional markup value is located in cell D1 in the following table. A B C D E F G 1 All Fractional Markups = 0.2 2 Splint 3 1 2 3 4 5 4 Wholesale Cost $20 $30 $40 $50 $60 5 Jan 3 5 1 3 3 Units Sold 6 Feb 7 4 2 2 4 7 Mar 2 1 2 6 2 8 Apr 5 2 3 4 5 Retail Charges 9 Jan $72 $180 $48 $180 $216 10 Feb $168 $144 $96 $120 $288 11 Mar $48 $36 $96 $360 $144 12 Apr $120 $72 $144 $240 $360 A formula for the value in cell C9 could be =C4*(1+D1)*C5. If we copied this formula to cell C10 using relative references, the resulting formula for cell C10 would be =C5*(1+D2)*C6 7 which clearly would give a wrong answer. A formula for cell C9 which could be copied to cell C10 (and the remainder of the cells in the range C9:G12) and would yield the correct answers is =C$4*(1+$D$1)*C5. If we decide to change our fractional markup for all types of splints to 0.25, we need change only the value of cell D1 from 0.2 to 0.25. Excel will automatically and correctly recalculate the values for cells in the range C9:G12 when the value in cell D1 is changed. Again, we must be very careful to use the proper row and column references. NOTE: When copying and pasting formulas, it is crucial to test, at a minimum, the values in the upper left and lower right corners and at one cell in the center of the of the range manually with a calculator to insure the calculations are being done properly. Menu bar: The second horizontal row on the Excel screen. It consists of the choices ―File,‖ ―Edit,‖ ―View,‖ ... ―Help.‖ Placing the cursor on one of these choices results in a pull-down menu that lists a more specific series of choices. Tool bars: One or more rows and/or one or more columns of icons on the Excel screen. There are three default tool bars (standard tool bar, formatting tool bar and drawing tool bar) each consisting of a series of 30 or more icons. The first icon on the standard tool bar is a sheet of paper with the upper right corner turned down. Put the cursor on any icon on any default tool bar and in a second or two a balloon giving a brief description of the function of the icon will appear. Clicking on these icons can open, save and print information on spreadsheets, depending on the icon selected. The icons can also be used for many other functions including changing the cell formats, searching for specific cell entries, etc. If the tool bar is not visible, select ―View‖ on the menu bar then select ―Toolbars‖ and then select the toolbar of interest. By default the Standard, Formatting and Drawing toolbars are visible. The Standard and Formatting toolbars are horizontal and located toward the top of the screen while the Drawing tool bar is vertical and located toward the left part of the screen on the first page of this handout. Work sheet: one page (sheet) of an Excel document or file. By default each new Excel document (file) has three work sheets named ―Sheet1,‖ Sheet2,‖ and ―Sheet3‖ though more sheets can be added if desired. The tabs for work sheets are located toward the lower left of the screen. Clicking on one of these tabs brings that worksheet to the screen. Excel file: Also known as an Excel document. All the work sheets that comprise a collection of data. Example: ―HoursWorked.xls‖ might consist of all work sheets that show the hours worked by each employee of a company. There might be separate work sheets for hourly employees, salaried employees, temporary employees, etc. Excel files always end in the extension ―.xls.‖ Scroll bars: There is a vertical scroll bar located at the extreme right of the Excel screen and a horizontal scroll bar located toward the bottom of the screen. An up-pointing triangle is at the top of the vertical scroll bar and a down-pointing triangle is at the bottom of the vertical scroll bar. Likewise a left-pointing triangle is at the extreme left of the horizontal scroll bar and a right-pointing triangle is at the extreme right of the horizontal scroll bar. The scroll bars are used to move rapidly through very large data sheets. To use a scroll bar, place the cursor on the scroll bar of interest (vertical or horizontal), depress the left mouse button and drag the cursor up or down (vertical scroll bar) and right or left (horizontal scroll bar). Macro: A series of pre-recorded automated commands that can be replayed on demand. Example: When a spreadsheet is opened, the column widths are automatically changed from the default width to a width of 15 characters. 8 Function: A predefined formula: Example: PMT, the required payment to pay off a loan. Using PMT in a cell (along with the required arguments, see below) gives the payment necessary to pay off a given loan. Excel has hundreds of functions ranging from arithmetic to financial to trigonometric to engineering, most of which you will never use. What‘s the last time you had a need for the inverse hyperbolic sine of an angle or a Bessel function (modified or not modified)? The list of functions can be seen by selecting the box with the down-pointing arrow just to the right of AutoSum icon (the ∑ sign on the tool bar). Argument. Necessary information specific to the situation at hand used in functions. For example to calculate the payment required to pay off a loan we need to know: (1) the interest rate of the loan; (2) the number of payments; (3) the amount loaned; (4) the balance at the end of the payments (usually 0 except for loans with balloon payments) and (5) whether the payments are to be made at the beginning or end of the payment period (usually the end). Thus, the function PMT has five arguments or pieces of information needed to calculate the required payment amount for a specific loan. Some functions have no arguments. Example: Now(), which gives the date and time a given spreadsheet was last opened, has no arguments. Moving Around on a Spreadsheet To move one column right: Press the right-pointing arrow key on the keyboard. To move one column left: Press the left-pointing arrow key on the keyboard. To move one row up: Press the up arrow key on the keyboard. To move one row down: Press the down arrow key on the keyboard. To move one screen up: Press the PgUp key on the keyboard. To move one screen down: Press the Pgdn key on the keyboard. To move one screen right: Press the Alt and PgDn keys on the keyboard simultaneously. To move one screen left: Press the Alt and PgUp keys on the keyboard simultaneously. To move to cell A1, press the Ctrl and Home keys simultaneously. To move to column A of the current row of the cursor, press the Home key. To move to the cell at the intersection of the last non-empty row and last non-empty column, press the Ctrl and End keys simultaneously. (This works only if there has never been a value in any row below the last current row nor any column to the right of the last current column. Blame Microsoft!). To move to the last filled column of a series of filled columns in a given row: Press the Ctrl and right arrow keys simultaneously. This is useful in finding the last filled cell (and first neighboring empty cell) at the right end of a long row. 9 To move to the first filled column of a series of filled columns in a given row: Press the Ctrl and left arrow keys simultaneously. This is useful in finding the first filled cell (and first neighboring empty cell) at the top of a long column To move to the last filled row of a series of filled rows in a given column: Press the Ctrl and down arrow keys (not PgDn) simultaneously. This is useful in finding the last filled row (and first neighboring empty cell) in a long column. To move to the first filled row of a series of filled rows in a given column: Press the Ctrl and up arrow keys (not PgUp) simultaneously. This is useful in finding the first filled cell (and first neighboring empty cell) in a long row. You can also use the scroll bars to move around rapidly on a large spreadsheet. Entering Information into an Excel Spreadsheet Entering information directly 1. Open Excel 2. Place the cursor on the cell in which you want to enter information 3. Enter the information 4. To set the format (number, date, text, etc.) for a single cell enter the following series of key presses from the menu bar: Format > Cells. Then select the format of interest (Number, Alignment, Font, Border, Patterns, Protection). For ―Number‖ you will be asked to select a category (General, Number, Currency, Text etc.). It is usually better to avoid ―General‖ – select ―Number,‖ ―Currency,‖ ―Text‖ etc. instead as you will have more control over how the information appears. Example: If you select ―Currency‖ you can select the dollar sign, the Euro sign, the English pound sign or any of several other currency signs. You can also select the number of digits to the right of the decimal to be displayed. You cannot select a currency sign or set the number of digits with ―General.‖ 5. Change the alignment, font size, font color and font appearance (bold, italic, underline) of the information if desired. It is easiest to do this with the tool bar. Normally text is left- justified and numbers are right-justified. You may want to use center-justified with a larger, more distinctive font for headings. 6. It is easier, faster and more reliable to change the format of a range of cells rather than to change the format of each cell individually. To do this, highlight the desired range of cells which should have identical formats, then on the menu bar select Format > Cells and change the format of the range as desired. 7. To center a text heading over two or more columns, highlight the columns to be merged, then Format > Cells > Alignment, then click in the box to the left of ―Merge Cells‖ then select OK. With the cursor anywhere in the group of merged cells, select center alignment from the tool bar. Alternatively, you could select right- or left-justification rather than center-justification but usually you will want to select center-justification. 8. You can direct the cursor to move to the right, to the left, up, or down, whichever is most convenient, after entering a cell‘s contents and pressing the Enter key. To do this select from the Menu bar: Tools > Options > Edit > Move selection after Enter. Select Down, Right, Up or Left from the pull-down list, then select OK. 10 9. To change the width of a single column, place the cursor on the vertical line that borders the right edge of the column whose width you want to change. Depress the left mouse button and drag the vertical line to the right (to increase the width) or left (to decrease the width) as desired. 10. Similarly, to increase the height of a single row, place the cursor on the horizontal line at the bottom margin of the row whose height you want to change. Depress the left mouse button and drag the horizontal line down (to increase the height of the row) or up (to decrease the height of the row). 11. To increase the width and/or height of more than one row or column on the worksheet simultaneously, use the mouse to highlight the rows (or columns) you want to make higher (or, for columns, wider). Changing the height of one row will change the height of all highlighted rows by an equal amount. Changing the width of one column will change the width of all highlighted columns by an equal amount. 12. To increase the width and/or height of all cells on the worksheet simultaneously, place the cursor in the square above the first row (labeled ‗1‘) and to the left of the first column (labeled ‗A‘) and depress the left mouse button. All columns and all rows on the worksheet will be highlighted. Changing the height of one row will change the height of all rows by an equal amount. Changing the width of one column will change the width of all columns by an equal amount. Limiting data to reasonable values (optional but useful). To minimize data entered into a range of cells to reasonable values, perform the following steps. This is useful to prevent inadvertent data entry. Example: A user enters the year 206 instead of 2006. 1. Highlight the range of cells for which data entry is to be limited. 2. On the menu bar, select Data > Validation. 3. Select the Settings tab. 4. Select the type of data that is acceptable. Example: Whole number. 5. Select the minimum and maximum allowable values. Example: Minimum = 1990 and Maximum = 2004. 6. Click on the Input Message tab. 7. Enter a title, typically the name of the field. Example: Year of Birth. 8. Enter an Input message. Example: Must be between 1990 and 2004. 9. The following steps are optional and, in my view, redundant. 10. Select the Error Alert tab. 11. Select the type of pop-up to be displayed if an out-of-range entry is attempted (Stop/Warning/Information). 12. Select a title to be displayed when an out-of-range entry is attempted. Example: Out of acceptable range! 13. Select an Error message to be displayed when an out-of-range entry is attempted. Example: Range is limited to between 1900 and 2004. 14. Click on the OK button. 15. Note that you need not use all these features. Example: You can limit the range of acceptable values without entering an Input Message or Error Alert. Excel will give you a default message when out-of-range data are entered. Limiting data to values from a drop-down list (optional but useful). 11 1. Enter the optional values in an unused section of the spreadsheet. Example: P 23 Alpha 24 Bravo 25 Charlie 26 Delta 27 Echo 28 Foxtrot 2. Highlight the cell(s) for which the drop-down list is to be provided, i.e., P23:P28 in this example. 3. Click on Data > Validation. 4. In the slot below ―Allow‖ on the pop-up select List. 5. Place the cursor in the slot below ―Source.‖ 6. Highlight, in this example, cells P23 to P28. 7. Ensure that there is a check in the box to the left of ―In-cell dropdown‖ on the pop-up. 8. Click on OK. 9. When the cursor is on one of the cells for which a drop-down list is available, a down-pointing arrow will appear to the right of the cell. 10. Click on one of the choices from the drop-down list to enter that choice into the cell. 11. To remove the drop-down list, highlight the cell(s) from which you want the list to be removed. 12. Click on Data > Validation. 13. In the slow below ―Allow‖ select Any value. Importing information from another source: Access 1. Open Access. 2. Open the table to be exported to Excel. 3. From the Access menu bar select File > Export. 4. In the box to the right of ―Save as type” select “Microsoft Excel (*.xls)‖ 5. Click on the down-pointing arrow to the right of ―Save in:‖ at the top right of the pop-up and select the location where you want the Access table to be saved. 6. The name of the table will appear to the right of the slot labeled ―File name:‖ by default. You can change this name if you wish. 7. Click on the button at the bottom right of the pop-up labeled ―Export‖ to export the table. The Access table will be saved as an Excel file with the name to the right of ―File name:‖ using the path that you selected. 8. You may have to adjust the width of the columns to see all the characters that make up the column. Importing information from another source: Word 1. Highlight the table containing the desired information in Word. 2. Press the Ctrl and C keys on the keyboard simultaneously (to copy) or the Ctrl and X keys on the keyboard simultaneously (to cut, that is remove from the Word document). 12 3. Put the cursor in the upper left-hand corner of the cell in the Excel work sheet where you want the upper left-hand corner of the copied table to be. 4. Press the Ctrl and V keys simultaneously and the table will be copied into Excel. 5. The copied table may have bold grid lines around each cell of the copied table. To remove them, select from the menu bar: Format > Cells > Border > None > OK. 6. The font may also vary from the rest of the worksheet. To change the font, highlight the copied cells with the mouse then select from the menu bar: Format > Cells > Font and then select the typeface and size of the font you want before pressing the OK button. Importing information from another source: PowerPoint NOTE: There is seldom, if ever, a reason to copy anything but a data table into Excel from PowerPoint. Thus, only directions for importing a data table will be given. 1. Highlight the data table in PowerPoint by placing the cursor over the left-most column so a down-pointing arrow is visible. 2. .Depress the left mouse button and highlight the desired columns. Drawing a box around the desired table often fails to copy properly. 3. Press the Ctrl and C keys on the keyboard simultaneously (to copy) or the Ctrl and X keys on the keyboard simultaneously (to cut, that is remove from the PowerPoint document). You can include the row and column labels if you wish but you do not need to. 4. Put the cursor in the upper right hand corner of the cell in the Excel work sheet where you want the upper right hand corner of the copied table to be. 5. Press the Ctrl and V keys simultaneously and the table will be copied into Excel. 6. The copied table may have bold grid lines around each cell of the copied table. To remove them, select from the menu bar: Format > Cells > Border > None > OK. 7. The font may also vary from the rest of the worksheet. To change the font, highlight the copied cells with the mouse then select from the menu bar: Format > Cells > Font and then select the typeface and size of the font you want. Importing information from another source: SPSS NOTE: You must have the SPSS program package loaded on your computer to import SPSS data into Excel. 1. Open SPSS and the SPSS file from which you want to import data. 2. Use the cursor to highlight the cells you want to import into Excel. This need not be complete rows or columns of data. Press the Ctrl and C keys simultaneously to put the information into the Clipboard. 3. Open Excel to the work sheet where you want to place the copied cells. 4. Place the cursor in the upper right corner of the range where you want the copied cells to be located in the Excel work sheet. 5. Press the Ctrl and V keys simultaneously to copy the contents of the clipboard to the Excel work sheet. 6. The SPSS column headings (field names) will not be copied into the Excel work sheet and must be entered manually. 7. You can export from SPSS using the ―Save as‖ option (select Excel 2.1 *.xls as the file type) under ―File‖ to save the column headings. 13 Importing information from another source: ASCII or text files. NOTE: You cannot directly import an ASCII file into Excel. You must first import the file into Access and from Access export the table to Excel. 1. Open Microsoft Access, and then open a new or existing database. 2. In the Database window, select Tables on the Objects bar. 3. On the main menu, select point to File > Get External Data > Import. 4. In the Files of type box, select Text Files. (The file must have a ―txt,‖ ―csv,‖ ―tab‖ or ―asc‖ extension. Files with ―dat‖ or other extensions must have the extension renamed before importing). 5. In the File name box, enter the name of the text file to import. 6. Click on Import. 7. Follow the directions in the Import Text Wizard dialog boxes. Arithmetic Operations in Excel PREVIEW: Spreadsheets are best suited for performing routine but lengthy arithmetic operations on cell contents. Some of operations are quite sophisticated and complicated, particularly the functions such as PMT, briefly described above. Copying and pasting arithmetic operations from one group of cells to another group of cells is much faster, easier and more reliable than doing the arithmetic operations on a cell-by-cell basis. Example: Suppose I want to find the square of each the number in each of 10 cells in a column in the following table. A B C 1 11.2 125.44 3.35 2 33.6 3 23.4 4 2.8 5 122 6 27.4 7 42.0 8 1.6 9 35.1 10 11.6 To calculate the square of the contents of cell A1, put the cursor in cell B1 and type the following =A1^2. and then press the Enter key. Since the ^ sign means exponentiation, this entry means ―Take the value of the number in cell A1 and raise it to the second power.‖ So Excel would multiply 11.2 X 11.2 which results in the value of 125.44 which would appear in cell B1. 14 If I wanted to find the square root of the contents of cell A1 and put the square root of cell A1 in cell C1, I would put the cursor in cell C1 and type the following =A1^0.5 and then press the Enter key. Again, since the ^ sign means exponentiation, the contents of cell A1 (11.2) would be raised to the ½ power (square root). If I wanted to repeat these operations on each value between A2 and A10, I could repeat the above operations for each of the remaining nine rows. But it would be much easier, faster and more reliable to copy the operations from cells B1 and C1 to cells B2, C2, B3, C3, …, B10, C10. To do this I can highlight cells B1 and C1 and press the Ctrl and C keys on the keyboard simultaneously to copy the formulas in cells B1 and C1 into the clipboard. I can then place the cursor on cell B2, press the left mouse button and highlight all the cells between B2 and C10 and then press the Ctrl and V keys simultaneously. The formulas entered into cells B1 and C1 are copied into the highlighted cells between B2 and C10. Consequently the squares of each row in column A between row 2 and row 10 are put in the column headed B (cells B2 through B10) and the square roots of each number in column A for rows 2 through 10 are put in cells C2 through C10. Performing calculations on data utilizing copy and paste operations comprise the vast majority of data manipulations for which clinicians, scientists and data managers use Excel Entering Arithmetic Formulas All arithmetic operations must begin with an equal sign followed by a combination of cell references and arithmetic operators. (Be careful that you don‘t put a space before the equal sign). Example: =C1+D1. The cell that contains this formula adds the value of cell C1 to the value of cell D1. The arithmetic operators Excel uses are as follows. Say, for example that the value in cell C1 is 4 and the value in cell D1 is 7 + for addition. Example. =C1+D1 adds the value in cell D1 to the value in cell C1 with a result of 11. - for subtraction. Example: =C1-D1 subtracts the value in cell D1 from the value in cell C1 with a result of -3. * for multiplication. Example: =C1*D1 multiples the value in cell C1 by the value in cell D1 with a result of 28. / for division. Example: =C1/D1 divides the value in cell C1 by the value cell D1 with a result of 4/7 (0.57). 15 ^ for exponentiation. Example =C1^D1 raises the value in cell C1 to the power of the value in cell D1 with a result of 47 (16,384). You can use fractional values for roots – ½ for square root, 1/3 for cube root, etc. Order in Arithmetic Operations Excel has a specific order of operations for complex formulas that must be followed for calculations to be accurate. However, operations in parentheses override the following and should be done first.. Any exponentiation is done after parenthetic operations Multiplication and division are done next Addition and subtraction are done last. Thus =4*3+2 = 14 =4*(3+2) = 20 =(4*2) + (3*5)*4 = 68 =((4*2) + (3*5))*4 = 92 = 4*2 + 2*3^2 = 26 An easy way to remember the order of operation is ―Practically Every Mister Deserves A Sister.‖ (Parentheses, Exponentiation, Multiplication, Division, Addition, Subtraction) To Sort Cell Contents The following describes how to sort rows within one or more columns as this is, by far, the most common way to sort. You can also sort columns within rows. See Excel Help for how to do this. 1. Highlight the cells to be sorted. You can include up to three columns of columnar data (or three rows of row data) easily. See Excel help for instruction on how to sort by a fourth column. 2. Click Data > Sort. 3. If there is one or more unselected rows containing data in the columns you have highlighted, a popup labeled ―Sort Warning‖ may (or may not, Excel isn‘t always consistent) appear and you will be asked if you want to expand your selection. Select the appropriate response for your needs. 4. A new popup labeled ―Sort‖ will appear. By default Excel selects the first column selected to be the first sort column sorted. You can change this to have, for example, the second selected column sorted first. 5. You can sort on a second column within the first column and a third column within a second column. 6. You can select with ascending or descending order with each column. 7. By clicking on ―Options‖ on the popup labeled ―Sort‖ you can elect to sort in a case-sensitive or case-insensitive manner. 16 Keeping Cell Content Labels Visible on the Screen When using very long worksheets, the labels associated with row contents may be off the top of the screen and not visible when working toward the bottom of the spreadsheet. Likewise, when using very wide spreadsheets the labels associated with column contents may be off the left of the screen and therefore not visible when working on the right side of the spreadsheet. Cannot see columns on the right Cannot see rows on the bottom To keep the row and column labels (the grey areas rows 1, 2 and 3 and column A above) visible, one can use what Excel calls ―Pane Locking.‖ 1. Place the cursor in the first row below the labels for the column contents (row 4) and the first column to the right of the labels for the row contents (column B). Hence the cursor should be in cell B4, the active cell in the figure above. 2. Select Window > Freeze Panes from the menu bar. 3, The labels in row 1 through 3 and the labels in column A will remain visible no matter where the active cell is on the screen. 4. You can also freeze just the labels in one or more contiguous columns without locking the labels in the rows. Likewise you can freeze just the labels in one or more contiguous rows without locking labels in the columns. 17 5. To unfreeze the panes, select Window > Unfreeze Panes from the menu bar. Note that the active cell in the figure below, Q42, is in an area of the worksheet that is not visible in the figure above; the figure above shows only the area A1:O40. In the absence of pane locking, the row and column labels would not be visible in the figure below. Printing Excel Worksheets 1. From the menu bar select Print > Page Setup. 2. A page setup pop-up will appear. 3. Click on the Page, Margins, Header/Footer and Sheet tabs and make the desired choices with regard to portrait vs. landscape, margins, etc. then click on the OK button to close the pop-up. 4. Highlight the area you want to print. 5. From the menu bar select Print > Print Area > Set Print Area. 6. From the menu bar select Print > Print then make the selections for your specific printer. Making an Excel graph of means with error bars. 18 ●Create an Excel spreadsheet with the labels in one row (ie Drug A, Drug B, Drug C, Drug D), the means in one row immediately below the label row and the SDs (or SEs) in the row immediately below the means row. You may place the second row and third rows elsewhere but it is usually easier to have the three rows contiguous. ●If you have more than one series, say 40 mg and 50 mg doses for each drug, place the mean of the second series (50 mg) directly below the row with the first series (40 mg). Place the series label in the first cell to the left of the first cell containing the means. A B C D E 1 Drug A Drug B Drug C Drug D 2 40 mg 500 600 700 800 3 50 mg 100 110 120 130 4 SD-40 10 15 20 25 5 SD-50 25 35 45 55 ●Highlight the range holding cells containing the labels for the rows (Drug A, Drug B, etc) and the series labels (40 mg and 50 mg) and the means. If the information is in the upper left corner of the spread sheet, as in our example, highlight A1:E3. ●Left click on Insert then Chart and a pop-up will appear. Select the desired Chart sub-type, usually the one in the upper left corner. ●Left click on Next. A pop-up showing the graph will appear. Left click on Next again and a new pop-up will appear. ●Add the title, and x-axis and y-axis labels (if desired) and left click on Next.” ●You can now put the chart on the current Excel sheet or a new Excel sheet as desired. Left click on Finish. ●Place the cursor on one of the bars of series 1 (40 mg) and left click to select a data series. A small box will appear in the center of each bar. ●Left click on Format on the menu bar at the top of the screen then Selected Data Series then Y Error Bars on the pop-up. ●Select the desired display, usually Plus. ●Select the Custom radio button. ●Highlight the cells containing the SDs (or SEs) for that series (40 mg, B4 to E4); the cells selected will automatically appear to the right of ―Custom.‖ ●Click on OK. ●Place the cursor on one of the bars of the second series (50 mg) and left click to select the second data series. A small box will appear in the center of each bar. ●Left click on Format at the top of the screen then Selected Data Series then Y Error Bars on the pop-up. ●Selected the desired display, usually Plus. ●Select the Custom radio button. ●Highlight the cells containing the SDs (or SEs) for that series (50 mg, B5 to E5); the cells selected will automatically appear to the right of ―Custom.‖ ●Click on OK and the graph will be finished. It can be printed, saved or cut and pasted into another Microsoft product. You can also use the tool bars to draw lines arrows, add text, etc. 19 Graph Title 1000 800 Y-Axis Label 600 40 mg 400 50 mg 200 0 Drug A Drug B Drug C Drug D X-Axis Label C:\Documents and Settings\kkuhleme\My Documents\Word\Department\MakingExcelGraphs.Doc About Excel Statistical Analysis Tools Microsoft Excel provides a set of data analysis tools — called the Analysis ToolPak — that you can use to save steps when you develop complex statistical analyses. You provide the data and parameters for each analysis; the tool uses the appropriate statistical macro functions and then displays the results in an output table. Some tools generate charts in addition to output tables. Note that the Analysis ToolPak is not included in the default setup of Excel – you must load it in addition to the default loading of Excel. Some of the statistical functions are built-in but others become available only when you install the Analysis ToolPak. Installing the Analysis ToolPak - The Analysis ToolPak is not automatically available when Excel is installed but it is a simple matter to activate it. Simply open Excel and click on Tools > Add-Ins > Analysis ToolPak > OK. The Analysis ToolPak will then be available under Tools on the menu bar. Accessing the data analysis tools -The Analysis ToolPak includes the tools described below. To access these tools, click Data Analysis on the Tools menu. If the Data Analysis command is not available, you need to load the Analysis ToolPak add-in program as described in the previous paragraph. ANOVA - The ANOVA (analysis of variance) analysis tools provide different types of variance analysis. The tool to use depends on the number of factors and the number of samples you have from the populations you want to test. ANOVA: Single Factor -This tool performs a simple analysis of variance, testing the hypothesis that means from two or more samples are equal (drawn from populations with the same mean). This technique expands on the tests for two means, such as the t-test. Use ANOVA rather than t- tests when you are comparing more than two groups. Example: You can use analysis of variance to analyze the responses to 4 levels of a drug on some independent variable. 20 Consider the following data which show the responses of 10 subjects to each of four doses of a drug. A B C D E 1 Subject Dose1 Dose2 Dose3 Dose4 2 1 1.47 -0.34 0.27 1.08 3 2 1.02 1.13 1.60 1.48 4 3 0.80 0.06 0.82 -1.47 5 4 0.71 0.23 0.18 1.51 6 5 0.51 0.81 -0.74 0.42 7 6 -0.24 2.15 1.00 1.23 8 7 0.69 0.83 3.34 2.32 9 8 -1.59 -0.64 1.14 1.40 10 9 -1.68 1.08 0.81 0.06 11 10 0.18 -0.52 1.17 0.25 12 Mean 0.19 0.48 0.96 0.83 1. To analyze these data select from the menu bar Tools > Data Analysis … > Anova: Single Factor then click on the OK button. 2. In the slot to the right of Input Range: type in B1:E11. 3. Be sure there is a check mark in the box to the left of Labels in First Row. 4. Be sure Output Range is selected in the Output Options section of the pop-up and enter G1 in the slot to the right of Output Range then click on the OK button. G1 will be the cell of the upper left corner of the output. 5. The follow results of the analysis appear below. G H I J K L M Anova: Single Factor SUMMARY Groups Count Sum Average Variance Dose1 10 1.87 0.187 1.130268 Dose2 10 4.79 0.479 0.774277 Dose3 10 9.59 0.959 1.129854 Dose4 10 8.28 0.828 1.115529 ANOVA Source of Variation SS df MS F P-value F crit Between Groups 3.653728 3 1.217909 1.173909 0.333199 2.866265 Within Groups 37.34935 36 1.037482 Total 41.00308 39 ANOVA: Two-Factor Without Replication This analysis tool performs a two-factor ANOVA that does not include more than one sampling per group, testing the hypothesis that means from 21 two or more samples are equal (drawn from populations with the same mean). This technique expands on tests for two means, such as the t-test. Reconsider the same data considered under Anova: Single Factor. 1. To analyze these data select from the menu bar Tools > Data Analysis … > Anova: Two-Factor Without Replication then click on the OK button. 2. In the slot to the right of Input Range: type in A1:E11. Note that we use A1, not B1, as the upper left corner as we must include both row and column labels since we will check ―Labels.‖ You must include both row and column labels or neither row nor column labels. 3. Be sure there is a check mark in the box to the left of Labels in First Row. 4. Be sure Output Range is selected in the Output Options section of the pop-up and enter G20 in the slot to the right of Output Range then click on the OK button. The output for the analysis will have cell G20 as its upper left corner. 5. The results of the analysis appear below. G H I J K L M Anova: Two-Factor Without Replication SUMMARY Count Sum Average Variance 1 4 2.48 0.62 0.6594 2 4 5.23 1.3075 0.076492 3 4 0.21 0.0525 1.155292 4 4 2.63 0.6575 0.380092 5 4 1 0.25 0.4634 6 4 4.14 1.035 0.969367 7 4 7.18 1.795 1.604967 8 4 0.31 0.0775 2.057758 9 4 0.27 0.0675 1.543425 10 4 1.08 0.27 0.480867 Dose1 10 1.87 0.187 1.130268 Dose2 10 4.79 0.479 0.774277 Dose3 10 9.59 0.959 1.129854 Dose4 10 8.28 0.828 1.115529 ANOVA Source of Variation SS df MS F P-value F crit Rows 12.8299 9 1.425545 1.569762 0.174732 2.250133 Columns 3.653728 3 1.217909 1.341121 0.2818 2.960348 Error 24.51945 27 0.908128 Total 41.00308 39 Note that the output is somewhat different from the output described under ANOVA: Single Factor because we are testing different hypotheses. 22 ANOVA: Two- Factor With Replication This analysis tool performs an extension of the single-factor ANOVA that includes more than one sample for each group of data. Example: You can use this test to compare responses to four different doses of a drug (the first factor) in each 10 patients (the second factor) I personally find Microsoft‘s description misleading since the second factor is the individual unit of observation, not truly a second factor. In my view, a second factor would be a second drug to be tested at each of four doses in our example. Consider the following listing the results from test four doses of a drug two times in each of 10 patients. A B C D E 1 Subject Dose1 Dose2 Dose3 Dose4 2 1 1.47 -0.34 0.27 1.08 3 1 1.29 0.39 -0.08 0.67 4 2 1.02 1.13 1.60 1.48 5 2 -1.01 0.55 0.83 0.27 6 3 0.80 0.06 0.82 -1.47 7 3 1.47 1.65 1.21 0.24 8 4 0.71 0.23 0.18 1.51 9 4 2.14 0.36 0.73 -0.79 10 5 0.51 0.81 -0.74 0.42 11 5 -0.59 -0.99 0.07 0.90 12 6 -0.24 2.15 1.00 1.23 13 6 1.62 -0.74 1.46 0.52 14 7 0.69 0.83 3.34 2.32 15 7 2.06 1.34 0.96 1.35 16 8 -1.59 -0.64 1.14 1.40 17 8 0.17 1.23 1.43 1.53 18 9 -1.68 1.08 0.81 0.06 19 9 -0.62 -0.06 -0.55 0.38 20 10 0.18 -0.52 1.17 0.25 21 10 1.13 0.57 -0.52 -0.93 Mean 0.48 0.45 0.76 0.62 1. To analyze these data select from the menu bar Tools > Data Analysis … > Anova: Two-Factor with Replication then click on the OK button. 2. In the box to the right of Input Range: type A1:E21. Notice that this range includes both the row and column labels. 3. In the box to the right of Rows per Sample: type 2 since we have two replications per patient. 4. In the box to the right of Alpha select the probability for an alpha error that you deem appropriate. This is usually 0.05, the default value. 5. Click in the circle to the left of Output Range: 6. In the slot to the right of Output Range: enter the cell reference for the upper left corner of the analysis results, say G1. 7. Click on the OK button and the results will be added to the worksheet with cell G1 in the upper left corner of the results which are given below. G H I J K L Anova: Two-Factor With Replication 23 SUMMARY Dose1 Dose2 Dose3 Dose4 Total Subject1 Count 2 2 2 2 8 Sum 2.76 0.05 0.19 1.75 4.75 Average 1.38 0.025 0.095 0.875 0.59375 Variance 0.0162 0.26645 0.06125 0.08405 0.423855 Subject2 Count 2 Sum Count 2 2 2 2 Average Sum 0.01 1.68 2.43 1.75 Variance Average 0.005 0.84 1.215 0.875 Variance 2.06045 0.1682 0.29645 0.73205 Subject3 Count 3 Sum Count 2 2 2 2 Average Sum 2.27 1.71 2.03 -1.23 Variance Average 1.135 0.855 1.015 -0.615 Variance 0.22445 1.26405 0.07605 1.46205 Subject4 Count 4 Sum Count 2 2 2 2 Average Sum 2.85 0.59 0.91 0.72 Variance Average 1.425 0.295 0.455 0.36 Variance 1.02245 0.00845 0.15125 2.645 Subject5 Count 5 Sum Count 2 2 2 2 Average Sum -0.08 -0.18 -0.67 1.32 Variance Average -0.04 -0.09 -0.335 0.66 Variance 0.605 1.62 0.32805 0.1152 Subject6 Count 6 Sum Count 2 2 2 2 Average Sum 1.38 1.41 2.46 1.75 Variance Average 0.69 0.705 1.23 0.875 Variance 1.7298 4.17605 0.1058 0.25205 Subject7 Count 7 Sum Count 2 2 2 2 Average Sum 2.75 2.17 4.3 3.67 Variance Average 1.375 1.085 2.15 1.835 Variance 0.93845 0.13005 2.8322 0.47045 Subject8 Count 8 Sum Count 2 2 2 2 Average Sum -1.42 0.59 2.57 2.93 Variance Average -0.71 0.295 1.285 1.465 24 Variance 1.5488 1.74845 0.04205 0.00845 Subject9 Count 9 Sum Count 2 2 2 2 Average Sum -2.3 1.02 0.26 0.44 Variance Average -1.15 0.51 0.13 0.22 Variance 0.5618 0.6498 0.9248 0.0512 Subject10 Count 10 Sum Count 2 2 2 2 Average Sum 1.31 0.05 0.65 -0.68 Variance Average 0.655 0.025 0.325 -0.34 Variance 0.45125 0.59405 1.42805 0.6962 Total Count Total Sum Count 20 20 20 20 Average Sum 9.53 9.09 15.13 12.42 Variance Average 0.4765 0.4545 0.7565 0.621 Variance 1.279582 0.715942 0.857256 0.875757 ANOVA Source of Variation SS df MS F SS F crit Sample 16.45303 9 1.828114 2.246748 0.038583 2.124029 Columns 1.185254 3 0.395085 0.485558 0.694224 2.838746 Interaction 21.84231 27 0.808974 0.994228 0.497377 1.766338 Within 32.54685 40 0.813671 Total 72.02744 79 Correlation - The correlation analysis tool measures the relationship between two data sets that are scaled to be independent of the unit of measurement. You can use the correlation analysis tool to determine whether two ranges of data move together — that is, whether large values of one set are associated with large values of the other (positive correlation), whether small values of one set are associated with large values of the other (negative correlation), or whether values in both sets are unrelated (correlation near zero). As correlation statistics are given in regression analyses, the directions for calculating a correlation coefficient will not be given here. Covariance - Covariance is a measure of the relationship between two ranges of data. In real life, covariance is seldom used – the use of correlation offers many advantages over the use of covariance. The Covariance analysis tool returns the average of the product of deviations of data points from their respective means, based on the following formula. You can use the covariance tool to determine whether two ranges of data move together — that is, whether large values of one set are associated with large values of the other (positive covariance), whether small values of one set are associated with large values of the other (negative covariance), or whether values in both sets are unrelated (covariance near zero). 25 Descriptive Statistics - The descriptive statistics analysis tool generates a report of univariate statistics for data in the input range, providing information about the central tendency and variability of your data. Consider the following data entered in cells A1:B24. 1. To calculate descriptive statistics for the data in cells A1:A21, select from the menu bar Tools > Data Analysis … > Descriptive Statistics then click on the OK button. 2. In the slot to the right of Input Range: enter A1:B22. 3. Click on the circle to the left of Columns. 4. Make sure the box to the left of Labels in first row is checked since the labels are included in the Input Range. 5. Click on the circle to the left of Output Range and Enter D1. 6. Click on the box to the left of Summary Statistics the click on the OK button. 7. Your screen should look like the following. A B C D E F G 1 Lbs In Lbs In 2 127 66 3 250 74 Mean 191.65 Mean 68.8 4 238 71 Standard Error 8.58557 Standard Error 0.7348 5 220 70 Median 188.5 Median 69 6 180 65 Mode 250 Mode 71 7 150 68 Standard Deviation 38.3958 Standard Deviation 3.2863 8 120 61 Sample Variance 1474.24 Sample Variance 10.8 9 180 71 Kurtosis -0.51396 Kurtosis 0.3255 10 192 71 Skewness 0.01499 Skewness -0.545 11 252 72 Range 132 Range 13 12 250 66 Minimum 120 Minimum 61 13 175 70 Maximum 252 Maximum 74 14 187 68 Sum 3833 Sum 1376 15 195 69 Count 20 Count 20 16 170 69 17 225 74 18 168 64 19 200 71 20 164 67 21 190 69 Note that the results for both columns A and B are given in cells D1:G15. Histogram - The histogram analysis tool calculates individual and cumulative frequencies for a cell range of data and data bins. This tool generates data for the number of occurrences of a value in a data set. For example, in a class of 20 students, you could determine the distribution of scores in letter-grade categories. A histogram table presents the letter-grade boundaries and the number of scores between the lowest bound and the current bound. The single most-frequent score is the mode of the data. Consider the following data entered in cells A1:B24 which gives the value of a certain block of stock. Cell B1 contains the upper limit for the first group, ie we want the number of observations 26 which are less than 1,000,000. In the next group we want the number of observations greater than 1,000,000 but less than 1,100,000. For the third group we want the number of observations between 1,100,001 and 1,200,000, etc. Excel calls these Bins. 8. To create a histogram of the data in cells A1:A24, select from the menu bar Tools > Data Analysis … > Histogram then click on the OK button. 9. In the slot to the right of Input Range: enter A1:A24. 10. In the slot to the right of Bin Range: Enter B1:B6 since we have six bins. 11. Make sure the box to the left of Labels is NOT checked since we have included no labels here. 12. Click on the circle to the left of Output Range and Enter D1 which will be the upper left corner of the output. 13. Your screen should look like the following. A B C D E 1 $849,132 1000000 Bin Frequency 2 $890,408 1100000 1000000 6 3 $911,826 1200000 1100000 4 4 $957,648 1300000 1200000 4 5 $969,916 1400000 1300000 3 6 $988,285 1500000 1400000 3 7 $1,009,325 1500000 3 8 $1,023,893 More 1 9 $1,040,471 10 $1,062,401 11 $1,107,962 12 $1,151,608 13 $1,175,216 14 $1,190,452 15 $1,215,432 16 $1,239,043 17 $1,267,723 18 $1,300,801 19 $1,358,184 20 $1,387,587 21 $1,409,778 22 $1,459,224 23 $1,482,410 24 $1,540,064 The number listed under BIN is the upper limit of that bin. Thus the histogram output indicates that there are 6 values equal to or less than 1,000,00, 4 values between 1,000,001 and 1,100,000, 4 values between 1,100,001 and 1,200,000, etc. Rank and Percentile - The rank and percentile analysis tool produces a table that contains the ordinal and percentage rank of each value in a data set. You can analyze the relative standing of values in a data set. Simple (Bivariate) Regression - The Regression analysis tool performs linear regression analysis by using the "least squares" method to fit a line through a set of observations. You can 27 analyze how a single dependent variable is affected by the values of another independent variable. For example, you can analyze how body weight is related to height. Consider the following data in columns A and B, ignoring for the moment information in columns C through J. A B C D E F G H I J 1 Lbs In 2 127 66 SUMMARY OUTPUT 3 250 74 4 238 71 Regression Statistics 5 220 70 Multiple R 0.66596 6 180 65 R Square 0.4435 7 150 68 Adjusted R Square 0.41258 8 120 61 Standard Error 29.4278 9 180 71 Observations 20 10 192 71 11 252 72 ANOVA 12 250 66 df SS MS F Signif F 13 175 70 Regression 1 12423 12423 14.34 0.0013 14 187 68 Residual 18 15588 865.99 15 195 69 Total 19 28011 16 170 69 17 225 74 Coeffs SE t Stat P-val Lower 95% Upper 95% 18 168 64 Intercept -343.662 141.49 -2.429 0.026 -640.92 -46.4 19 200 71 X Variable 1 7.7807 2.0543 3.7875 0.001 3.4647 12.097 20 164 67 21 190 69 1. To analyze these data select from the menu bar Tools > Data Analysis … > Regression then click on the OK button. 2. In the slot to the right of Input Y Range, enter A2:A21. 3. In the slot to the right of Input X Range enter B2:B21. 4. Make sure the box to the left of Labels is NOT checked. 5. Accept the default Confidence Limit of 5%. 6. Click on the circle to the left of Output Range and enter D2 in the slot to the right of Output Range. 7. Click on the OK button. 8. Your screen should look as follows. The correlation coefficient is 0.66596 and the amount of variation in weight that is explained by variation in height is 44.35%. The best estimate of weight based on height is: Expected weight in pounds = -343.l662 + 7.7807 X height in inches. We could probably do a better job of estimating weight if we had waist circumference or gender in addition to height. Adding these additional variables would require multiple regression rather than simple regression as computed above. Excel cannot perform multiple regression analyses; use SPSS for multiple regression. 28 t-Test - The t-test analysis tools test the means of different types of populations. Note that t-tests are used to compare only two means. Use analysis of variance if you want to test more than two means.. t-Test: Two-Sample Assuming Equal Variances. This analysis tool performs a two-sample student's t-test. This t-test form assumes that the variances of both data sets are equal; it is referred to as a homoscedastic t-test. You can use t-tests to determine whether two sample means are equal. Reconsider the data given under Anova: Single Factor above, ignoring the responses to Doses3 and Dose4. 9. To analyze these data select from the menu bar Tools > Data Analysis … > t-Test Two- Sample Assuming Equal Variances then click on the OK button. 10. In the slot to the right of Variable 1 Range, enter B1:B11. 11. In the slot to the right of Variable 2 Range enter C1:C11. 12. In the slot to the right of Hypothesized Mean Difference enter the expected mean difference, usually zero. 13. Make sure there is a check mark in the box to the left of Labels. 14. Accept the default value of 0.05 for alpha. 15. Click on the circle to the left of Output Range. 16. Enter G1 in the slot to the right of Output Range and click on the OK button. 17. The results of the analysis will appear with cell GI in the upper right corner of the results section. 18. The output from the above exercise is given below. G H I Hypothesized Mean Difference Dose1 Dose2 Mean 0.187 0.479 Variance 1.130268 0.774277 Observations 10 10 Pooled Variance 0.952272 Hypothesized Mean Difference 0 df 18 t Stat -0.66909 P(T<=t) one-tail 0.255963 t Critical one-tail 1.734063 P(T<=t) two-tail 0.511927 t Critical two-tail 2.100924 t-Test: Two-Sample Assuming Unequal Variances. This analysis tool performs a two-sample student's t-test. This t-test form assumes that the variances of both ranges of data are unequal; it is referred to as a heteroscedastic t-test. You can use a t-test to determine whether two sample means are equal. Use this test when the groups under study are distinct. Use a paired test when there is one group before and after a treatment. 29 1. To analyze these data select from the menu bar Tools > Data Analysis … > t-Test Two- Sample Assuming Equal Variances then click on the OK button. 2. In the slot to the right of Variable 1 Range, enter B1:B11. 3. In the slot to the right of Variable 2 Range enter C1:C11. 4. In the slot to the right of Hypothesized Mean Difference enter the expected mean difference, usually zero. 5. Make sure there is a check mark in the box to the left of Labels (since we included cells B1 and C1). 6. Accept the default value of 0.05 for alpha. 7. Click on the circle to the left of Output Range. 8. Enter G1 in the slot to the right of Output Range and click on the OK button. 9. The results of the analysis will appear with cell GI in the upper right corner of the results section. 10. The output from the above exercise is given below. t-Test: Two-Sample Assuming Unequal Variances Dose1 Dose2 Mean 0.187 0.479 Variance 1.130268 0.774277 Observations 10 10 Hypothesized Mean Difference 0 df 17 t Stat -0.66909 P(T<=t) one-tail 0.25621 t Critical one-tail 1.739606 P(T<=t) two-tail 0.51242 t Critical two-tail 2.109819 t-Test: Two-Sample Assuming Unequal Variances Notice that the P-values are slightly different from the results of the analysis where the variances were assumed to be equal. t-Test: Paired Two Sample For Means This analysis tool and its formula perform a paired two- sample student's t-test to determine whether a sample's means are distinct. This t-test form does not assume that the variances of both populations are equal. You can use a paired test when there is a natural pairing of observations in the samples, such as when a sample group is tested twice — before and after an experiment. Reconsider the data given in ANOVA: Single Factor above. 1. To analyze these data select from the menu bar Tools > Data Analysis … t-Test: Paired Two-Sample for Means then click on the OK button. 2. In the slot to the right of Variable 1 Range, enter B1:B11. 3. In the slot to the right of Variable 2 Range enter C1:C11. 4. In the slot to the right of Hypothesized Mean Difference enter the expected mean difference, usually zero. 5. Make sure there is a check mark in the box to the left of Labels. 30 6. Accept the default value of 0.05 for alpha. 7. Click on the circle to the left of Output Range. 8. Enter G1 in the slot to the right of Output Range and click on the OK button. 9. The results of the analysis will appear with cell GI in the upper right corner of the results section. 10. The output from the above exercise is given below. G H I t-Test: Paired Two Sample for Means Dose1 Dose2 Mean 0.187 0.479 Variance 1.130268 0.774277 Observations 10 10 Pearson Correlation -0.05371 Hypothesized Mean Difference 0 df 9 t Stat -0.65211 P(T<=t) one-tail 0.265315 t Critical one-tail 1.833114 P(T<=t) two-tail 0.5279 t Critical two-tail 2.2622 Notice that the results are slightly different from the results (and degrees of freedom) of the two analyses above as a consequence of different assumptions, even though the data are exactly the same in each analysis. Z-Test - The z-Test: Two Sample for Means analysis tool performs a two-sample z-test for means with known variances. This tool is used to test hypotheses about the difference between two population means. For example, you can use this test to determine differences between the performances of two car models. Reconsider the data that follows in Columns A and B. Ignore for the moment the data in columns D through F. A B C D E F 1 Trt A Trt B z-Test: Two Sample for Means 2 127 266 3 250 274 Trt A Trt B 4 238 271 Mean 191.65 268.8 5 220 270 Known Variance 1474.2 10.8 6 180 265 Observations 20 20 7 150 268 Hypothesized Mean Difference 0 8 120 261 z -8.9533 9 180 271 P(Z<=z) one-tail 0 10 192 271 z Critical one-tail 1.6449 11 252 272 P(Z<=z) two-tail 0 12 250 266 z Critical two-tail 1.96 31 13 175 270 14 187 268 15 195 269 16 170 269 17 225 274 18 168 264 19 200 271 20 164 267 21 190 269 22 1474.24 10.8 Variances 1. Calculate the variance for the data in cells A2:A21 using the instructions located in Descriptive Statistics above. Repeat for the data in cells B2:B22. 2. To analyze these data select from the menu bar Tools > Data Analysis … z-Test: Two Samples for Means then click on the OK button. 3. Enter A2:A21 into the slot to the right of Variable 1 range. 4. Enter B2:B21 into the slot to the right of Variable 2 range. 5. Enter the hypothesized mean difference (usually 0) into the slot to the right of Hypothesized Mean Difference. 6. Enter the variance for column A (located in cell A22) in the slot to the right of Variable 1 Variance (known). 7. Enter the variance for column A (located in cell B22) in the slot to the right of Variable 1 Variance (known). 8. Make sure there is a check mark in the box to the left of Labels. 9. Accept the default value of 0.05 for Alpha. 10. Click in the circle to the left of Output Range. Enter D1 in the slot to the right of Output Range and click on the OK button. 11. The results are listed in the table above in cells D1:F12. Copying Formulas vs. Copying Numeric Cell Contents There are times when you want to perform some complicated mathematical manipulations on data in Excel and then use the results in another analysis, say a statistical analysis. Example: you might want to use Excel to calculate body mass index from the heights and weights of patients and then do some kind of statistical analysis on the calculated body mass indexes. In these circumstances, you need the numeric values of the cells (the actual body mass indexes), not the formulas that calculate the body mass indexes. But if you try to copy (or cut) and paste a range of cells, the formulas are pasted, not the values (body mass indexes) the formulas generate. Other programs will not be able to read the formulas so you need some way to copy (or cut) and paste the values, not the formulas that generate the values. To copy (or cut) calculated numeric values, not the formulas that produced them, do the following. 1. Highlight the cells to be copied (or cut) and press the Ctrl and C keys simultaneously to copy the cell contents into the clipboard. 2. Put the cursor on the upper left corner of the range to which you want the values copied. This can be, but need not be, the upper left corner of the exact range from which the 32 values are copied (or cut). If you do paste in the identical range, the formulas will be overwritten. 3. On the menu bar click on Edit > Paste Special. A Paste Special pop-up will appear. 4. Click on the circle to the left of ―Values‖ in the pop-up then click on the OK button. 5. The values in the copied (or cut) cells will be stored as numeric values, not formulas, in the range copied to. 6. Note that you must use Edit > Paste Special. Edit > Paste or pressing the Control and V keys simultaneously will not copy the numeric values. About PivotTable reports A PivotTable report is an interactive table that quickly combines and compares large amounts of data. You can rotate its rows and columns to see different summaries of the source data, and you can display the details for areas of interest. Personally I seldom use PivotTables because I use Access for database functions. If there is interest in using PivotTables, let me know and I will work up a description of how to generate them. Using a Pivot Table Report Use a PivotTable report when you want to analyze related totals, especially when you have a long list of figures to sum and you want to compare several facts about each figure. In the report illustrated above, you can easily see how the third-quarter golf sales in cell F3 stack up against sales for another sport or quarter, or the total sales. Because a PivotTable report is interactive, you can change the view of the data to see more details or calculate different summaries, such as counts or averages. Organization of data in a pivot report In a PivotTable report, each column or field in your source data becomes a PivotTable field that summarizes multiple rows of information. In the example above, the Sport column becomes the Sport field, and each record for Golf is summarized in a single Golf item. A data field, such as Sum of Sales, provides the values to be summarized. Cell F3 in the report above contains the sum of the Sales value from every row in the source data for which the Sport column contains Golf and the Quarter column contains Qtr3. Creating a pivot table report To create a PivotTable report, you run the PivotTable and PivotChart Wizard. In the wizard, you select the source data you want from your worksheet list or external database. The wizard then provides you with a worksheet area for the report and a list of the available fields. As you drag the fields from the list window to the outlined areas, Microsoft Excel summarizes and calculates the report for you automatically. After you create a PivotTable report, you can customize it to focus on the information you want: change the layout, change the format, or drill down to display more detailed data. Project: Making a Check Register 33 1. Open Excel. 2. Open a new work sheet. 3. Add nine more worksheets (to the existing three) by selecting Insert > Worksheet from the menu bar. 4. Rename the first worksheet tab to ―Jan ‗04‖ by placing the cursor on the first tab, right clicking the mouse, then selecting ―Rename‖ then typing Jan ‘04 5. Rename the second worksheet tab to ―Feb ‗04‖ by placing the cursor on the first tab, right clicking the mouse, then selecting ―Rename‖ then typing Feb ‘04 6. Rename the 3rd through the 12th tabs ―Mar ‗04‖ through Dec ‘04 in a similar manner. 7. Save the file naming it Check Register 2004.xls. 8. Add the following labels to the spreadsheet. Note that all labels are right-justified except ―Payee‖ and ―Source‖ 9. Highlight cells A1 through H1. Click on Format > Cells > Word Wrap. Adjust the cell widths to your preference. You can always adjust the widths later. A B C D E F G H 1 Chk Num Debit Date Payee Debit Amount Credit Date Source Credit Amount Balance 2 10. Highlight cells B3 through B53 (assuming you won‘t write more than 50 checks a month). On the menu bar select Format > Cells > Number > Date. Highlight the sample ―03/14/01‖ then click on the OK button. (Select another date format if you wish). This sets the format for the dates. 11. Repeat the process for the ―Credit Date‖ column. 12. Highlight cells D3 through D53. On the menu bar select Format > Cells > Currency. Select $ as the indicator, accepting the default value of 2 decimal places then click on the OK button. This sets the format for these cells to currency with a dollar sign preceding the amount and two digits to the right of the decimal point. If number signs (#) appear in the column, expand the width of the column until they disappear. 13. Repeat the process for columns G and H. 14. Enter the following information into the worksheet. If you don‘t add the $0.00 entries in the Credit Amount column where indicated, the formulas may not work properly and you may get an error message. A B C D E F G H Debit Debit Credit Credit 1 Chk Num Date Payee Amount Date Source of Deposit Amount Balance 2 $7,054.69 3 Debit 1-Jan BG&E $214.99 1-Jan Xmas gift from Mom & Dad $200.00 4 Debit 6-Jan Verizon $57.27 $0.00 5 Debit 10-Jan First Savings & Loan $735.47 $0.00 6 1333 11-Jan Giant $48.12 $0.00 7 1334 16-Jan Safeway $23.76 15-Jan JHU $1,277.92 8 1335 18-Jan Target $14.54 $0.00 9 1336 18-Jan Hechts $185.91 $0.00 10 Debit 23-Jan Discover $389.68 $0.00 11 Debit 28-Jan Visa $130.83 $0.00 12 ATM 29-Jan JHU Credit Union $187.44 31-Jan JHU $1,277.92 13 34 14 15. Enter the following formula into cell H3: =H2-D3+G3. Excel will subtract $214.99 (D3, the check to BG&E) from the previous balance (H2, $7,054.69) and then add the deposit of the Xmas gift (G3, $200.00), resulting in a new balance of $7,039.70 (H3). 16. Highlight cell H3. Press the Ctrl and C keys simultaneously to copy the formula to the clipboard. Highlight the range of cells H4:H12. Press the Ctrl and V keys simultaneously to paste the relative (default) formula in the highlighted cells. This copies the formula in cell H3 to cells H4 to H12. 17. You now have a running account of your checking account balance. 18. (Optional) Highlight cells D3:D12. Press the AutoSum icon (∑) on the tool bar and the sum of all checks and debits will be placed in the cell after the last entry. 19. (Optional) Highlight cells G3:G12. Press the AutoSum icon on the tool bar and the sum of all deposits will be placed in the cell after the last entry. 20. Highlight the range A1:H1. Press the Ctrl and C keys simultaneously to copy the cells into the clipboard. 21. Click on the Feb ’04 tab at the bottom of the screen to bring it forward. Place the cursor in cell A1 and press the Ctrl and V keys simultaneously to copy the labels to the worksheet for February. Repeat for March through December. 22. Return to the Jan ‘04 worksheet. Copy the contents of cells H3 into the clipboard. 23. Re-open the Feb ‘04 worksheet. Paste the contents of the clipboard into cell H3. Repeat for March through December. 24. Now all you have to do for February is enter the balance as of 31-Jan from worksheet Jan ‘04 to cell H2 of Feb ‘04 before the Feb ‘04 worksheet is ready for use. 25. Return to the Jan ‘04 worksheet and copy the contents of cell H12. 26. Go to the Feb ‘04 worksheet and highlight cell H2. 27. Click on Edit > Paste Special > Values > OK. 28. Optional: In the first cell below lower right hand corner of the entries type the following: =Now(). This will tell you the last time the file was opened. 29. In the first cell below the lower left hand column of the entries type the path and file name. Example: C:\My Documents\Excel\Home\Check Register.xls. This will help you if you forget where the file is located provided, of course, you have a paper copy of the check register. 30. Save the file, giving it a file name and path of your choice. Project: Making an 30-Year Mortgage Amortization Schedule with Monthly Payments 1. Open Excel 2. Open a new work sheet. 3. Enter the following values into the indicated cells A1: Months of Mortgage A2: Original Balance A3: Annual Interest Rate (Decimal) A4: Points A5: Payment A8: Date Due B8: Balance 35 C7 Interest C8: Due D7: New D8: Balance E8: Pmt Num F8: Ann Int D1 360 D2: 100,000 D3: 0.06 D4: 4.25 D5: =PMT(D3/12,D1,-D2*(1+D4/100),0,0) Note: PMT is an Excel function with five arguments (see the definitions section for the definition of an argument) which calculates the payment necessary to pay off a specific loan. The syntax is as follows: PMT(rate,nper,pv,fv,type) Rate is the interest rate for the loan. (D3/12) Nper is the total number of payments for the loan. (D1) Pv is the present value, or the total amount that a series of future payments is worth now; also known as the principal. (-D2*(1+D4/100 [The original amount of the loan plus the amount of the points. If you pay points, you are basically increasing the amount of your loan. Each point is 1/100 of the value of the loan. Hence the points must be divided by 100]. Fv is the future value, or a cash balance you want to attain after the last payment is made. If fv is omitted, it is assumed to be 0 (zero), that is, the loan will be paid in full and the future value of a loan is 0. (0) Type is the number 0 (zero) or 1 and indicates when payments are due. (0) 4. Right justify the following cells: A8, B8, C7, C8, D7, D8, E8, F8 to make the worksheet more readable. 5. Put the cursor on cell A9. From the menu bar select Format > Cells > Date > Mar-01 then click on the OK button. This gives cell A9 a date format. 6. Enter Jan-05 into cell C9. Our amortization schedule will begin with January 2005 7. Enter the following entries into the indicated cells. B9: =D2*(1+D4/100). This calculates the original requested mortgage amount plus the points. C9: =(D3/12*B9). This calculates the interest due for the first month. D9: =B9+C9-$D$5. This sums the existing balance and the interest due for the first month and then subtracts the monthly payment to give the new balance. E9: 1. This signifies this is the first payment. B10: =(B9+C9-$D$5). This is the balance after the first payment. C10: =($D$3/12)*B10. This is the interest due on the previous month‘s balance. D10: =(B10+C11-$D$5. This the previous balance plus the interest due on that balance less the monthly payment. Cell D10 is the new balance after the second payment. 36 E10: =E9+1. This increases the value in cell E10 by 1, i.e. the value in E10 is the second payment. 8. Place the cursor on cell E10 and press the Ctrl and C keys simultaneously to copy the contents of cell E10 into the clipboard. 9. Highlight cells E11:E368 and press the Ctrl and V simultaneously to copy the relative contents of cell E10 into cells E11 to E368. 10. Enter Feb-01 into cell A10, Mar-01 into cell A11, …, Dec-30 so all cells in column A up through Dec, 2003 have entries. 11. Highlight cells B9:D9 and select from the menu bar Format > Cells > Currency. 12. Select 2 decimal points and the dollar sign as the symbol then press the OK button.. This will give the highlighted cells a currency format with 2 decimal places and a $. 13. Highlight cells B9 to D9 and press the Ctrl and C keys simultaneously to copy the contents of those cells into the clipboard. 14. Highlight cells B10 to D368, then press the Ctrl and V keys simultaneously to copy the contents of the clipboard to the highlighted area. 15. Enter the following into cell G10: =Sum(C9:C20). This sums the interest paid for calendar year 2005 for income tax purposes. 16. Copy the contents of cell G10 into the cell opposite the December cell (and only the December cell) for each year 2006 through 2034. This gives the interest paid for each of the succeeding 29 years. 17. Save the file, giving it a file name and path of your choice. By entering the number of payments, amount borrowed, annual interest rate and points paid in cells D1 through D4 you can alter the payment schedule for any specific loan. Maximizing the data printed on a standard sheet of paper. It is usually advantageous to print as much Excel information as possible on a single sheet of paper so you can see as many rows and columns as possible without having to leaf through other pages. Some easy things you can do to maximize the information on a single sheet of paper are as follows. Increasing the number of columns printed on a single sheet is especially useful. ● Use narrower margins. Margins are set with File > Page Setup > Margins. ● Use shorter column headings. This can become self-defeating if the column headings are so short they lose meaning. ● Narrow the columns that are wider than needed for clarity. ● Use smaller type. To change the type size of an entire spreadsheet at once, click on the unlabeled cell just above row 1 and to the left of column A. (At the tip of the arrow in the figure below). This will select the entire sheet. Then select Format > Cells > Font and select a font size that you think will work for you. Although the pull-down list has 8 as the smallest font size, you can manually enter a smaller number in the slot for an even smaller font size. Bear in mind that font sizes of less than 6 or so will be difficult for many people to read. This is 6-point size. ● Excel will automatically adjust the type size to make the contents fit in a given cell. To do this highlight the cell(s) for which you want the type size adjusted, then click on Format > Cells > Alignment, then put a check in the box to the left of ―Shrink to Fit.‖ ● Tilt the column headings from horizontal to slanting. You can rotate the contents of the cell up to 90 degrees up or down to minimize the width of the column heading. To do this, click on the cell of interest, then select Format > Cells > Alignment. Place the cursor on the tip of the line to the right of ―Text‖ in the section of the popup labeled ―Orientation.‖ Use the cursor to pivot 37 the line up (or down) as desired. You can increase the height of the cell by placing the cursor on the dividing line of the row of interest and the row immediately below. When the cursor takes the shape of a horizontal line with an up arrow and a down arrow visible in the center of the line, left click and drag the mouse down to increase the height of the row. ● You can also use word wrap to increase the height of columns while holding the width constant. To do this select, highlight the cell to be reformatted then select Format > Cells > Alignment > Wrap Text. Note that the height of the entire row will take the new height. ● You can combine word wrap with slanting the cell contents as in cells B1 and C1 below. Note that cell A5 also has word wrap turned on. This is a comment The contents of cell B1 have been rotated upward 90 degrees while the contents of cell C1 have been rotated up about 60 degrees. ● Add a comment to a cell. Comments can be used at any place in the spreadsheet to make a note to yourself. If a cell has a comment attached, it will have a very small triangle in the upper right corner of the cell. When the cursor is on the cell, the comment will be visible. If the comment doesn‘t automatically become visible (Excel is a bit unreliable in this regard), right click and then select ―Edit Comment.‖ To add a comment, put the cursor on the cell for which you want to attach a comment and right click. Select ―Insert Comment.‖ A box will appear (probably with your name in it in bold type – you can delete your name and the bold if you like before typing in the comment). Normally the comment will disappear when you move the cursor off the cell but you can make it permanently (until you change it again) by right clicking then selecting ―Show Comment.‖ You can also edit and delete the comment. Example. You could make a column heading ―Change‖ and then add a comment to the cell ―From 1/1/2005.‖ This results in a much narrower column than ―Change from 1/1/2005‖ with the loss of only a slight amount of clarity when looking at the spreadsheet. 38 39 C:\Documents and Settings\\kkuhleme\My Documents\Word\Department\ ExcelExercises.doc. Exercises. 1. Create a new folder named Excel Imported Files in the root directory (C:\). 2. Transfer a Word table, an Access table, and ASCII file, an entire SPSS data file and a selected range of an SPSS file into separate Excel worksheets in the same file into the Excel Imported Files folder. a. Name the worksheets, Word, Access, ASCII, SPSSA and SPSSB, respectively. (Note: you can‘t transfer SPSS files unless you have SPSS loaded on your computer. 3. Insert a new worksheet and name it ―Excel de novo.‖ In that worksheet develop an Excel file de novo. a. Make one of the fields (columns) a date field with a format mm/dd/yy and name it DOB b. With the cursor in one of the DOB cells, enter the following values and watch what happens: 3/2/4, 02/03/04, 3/2/2004, 3/2/2224. c. Limit the permissible date range of DOB to 2001 to 2005. Try to enter a date outside this range and note the result. d. Make a text field, ―Marital Status.‖ Make a drop-down list for this field and limit entries to the following: Single, Married, Divorced, Widowed, Separated, Other. e. Make a currency field, ―Expenses.‖ Show the $ sign and show three places to the right of the decimal. f. Join any three empty horizontally contiguous cells. Enter a phrase. Center the phrase. Right justify the phrase. Left justify the phrase. Change the font of the phrase to 14 point and make it italic. g. Join any three vertically contiguous cells. Turn word wrap on for this cell. Enter a phrase long enough to utilize word wrap. Make the column wider and watch what happens to the word-wrapped phrase. h. Delete the phrase and entered in ‗f; above and split the cells joined back into three separate cell i. Delete the phrase entered in ‗g‘ above and split the cells back into three separate cells. j. Transfer the data portion (do not include labels) into Word, Access, PowerPoint and SPSS. 4. Give the results of the following computations using Excel rules for computations. Enter the formulae (don‘t forget to put the equal sign before the formula) into Excel and see if you‘re right. a. 9^1/2= b. 9^(1/2)= c. 9^(-1/2)= d. 4*2 + (3*5*4)= e. 4*2 + 3*(5*4)= f. ((4*2) + (3*2*2))*4= g. (4*2) + (3*2*2)*4= h. ((4*2) + (3*2*2)*4)= 5. Suppose you divided a series of patients into four groups which walked on a treadmill at 3 MPH up a 5% grade for the following periods (1) 10 min/day; (2) 20 min/day; (3) 30 min/day and (4) 40 min/day five days a week for six weeks. At the end of six weeks each patient did his regular walk and the 1-minute post exercise pulse rate was measured for 40 each patient. The results (mean ±sd) were as follows: Group 1: 82±13; Group 2: 95±18; Group 3: 120±19; Group 4: 135±22. Suppose you tested a separate group of patients with bicycle ergometry (900 kpm) for 10, 20, 30 and 40 min/day as with the treadmill group and the 1-minute post exercise heart rates were as follows (mean ±sd). Group 1: 96±16; Group 2: 110 ± 20; Group 3: 125±18; Group 4 140±27. Make a vertical bar graph with s.d. ―whiskers‖ with all the results (treadmill and bicycle ergometer groups) on the same graph. Label the graph ―1-Min Post Exercise Pulse Rates‖ in an18-pt bold italic font. Label the x-axis ―Minutes of Exercise‖ and the y-axis ―Pulse Rates.‖ 6. Suppose you own a bakery that sells bread, rolls, cookies and cakes. The sale price per unit is as follows: bread - $3.50/loaf; rolls - $2.96/dozen; cookies - $1.49/dozen and cake - $5.00 each. In a particular week, the bakery sold the following goods. Item Mon Tue Wed Thu Fri Sat Bread (loaves) 95 80 75 73 80 202 Rolls (doz) 24 32 26 40 20 65 Cookies (doz) 32 30 41 39 35 40 Cakes (each) 16 18 18 12 19 25 a. Make a worksheet showing the dollar value of sales for each product (bread, rolls, cookies, cakes) for each day. (Your life will be a little simpler if you copy the above Word table directly into a worksheet instead of re-entering the data). b. Write one equation that can be copied to each of the other 23 cells that are to contain the values.. c. Write a formula for the total sales for Monday that can be copied to Tuesday through Saturday. d. Write a formula for the total sales for bread that can be copied for rolls, cookies and cakes. e. Write a formula for the total sales for the week. f. Suppose that Tuesdays and Thursdays are sales days. Write a formula for that gives the total of Tuesday and Thursday sales for bread that can copied for rolls, cookies and cakes. 7. Make a worksheet with the following column headings: para-amino benzoic acid (2003), sodium acetyl salicylate (2001), dihydroepiandosterone (1999). Use word wrap, change the font size and slant the entries to minimize column widths. Insert a comment to attach the appropriate year to each column heading so the year doesn‘t have to be included in the text of the headings. 41 EXCEL Attendees will be able to ● Understand the following words/concepts ▪ Menu bar ▪ Drawing tool bar ▪ Standard tool bar ▪ Formatting tool bar ▪ Rows ▪ Columns ▪ Active cell ▪ Cell reference ▪ Scroll bars ▪ Range ▪ Worksheet ▪ Worksheet tabs ▪ Absolute reference ▪ Relative reference ▪ Mixed reference ▪ Formula for active cell Perform the following operations ● Enter information directly into a spreadsheet ● Maximize data entry accuracy by adding pull-down lists and placing limits on fields ● Import data from Word and Access ● Export data to Word and Access ● Place limits on cell entries ● Compose mathematical formulas ● Copy and paste formulas ● Know how to move around on a spread sheet ● Print Excel spreadsheets or parts of spreadsheets ● Make a graph using Excel data ● Perform statistical analyses ▪ ANOVA, single factor ▪ ANOVA, two-factor without replication ▪ ANOVA, two-factor with replication ▪ Correlation ▪ Descriptive statistics ▪ Generate a histogram ▪ Regression ▪ Student t-test, two-sample with equal variances ▪ Student t-test, two-sample with unequal variances ▪ Student t-test, paired two-sample ▪ Z-test ● Use ―Paste Special‖ to accomplish tasks not possible with ―Paste‖ ● Maximize the data printed on a standard sheet of paper ● Know when to use a database and when to use a spreadsheet 42