Excel Amortization Tables 175

Document Sample
Excel Amortization Tables 175 Powered By Docstoc
					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

				
DOCUMENT INFO
Description: Excel Amortization Tables 175 document sample