-- Excel notes --

Jim Mahoney, Jan 2005

I'm using Microsoft Excel X for Mac, Service Release 1.
Your mileage may vary.

-------------------------------
1. Getting the data in

 * I like the "comma separated values" or .csv text files, myself,
   as a cross-platform fairly stable data format.
 * However, you should be aware that newlines aren't quite the
   same on windows/mac/unix platforms.

 a) Open a new Excel workbook.
 b) Choose the Data/Get_External_Data/Import_Text_File... menu item
    * pick the file in the dialog
    * choose Enable: All Documents at the top of the window
    * pick a file like bluman_es_data-sets_text/Databank.txt
    * now you walk through a "Text Import Wizard" dialog with several screens.
      * choose the "Delimited" radio box.
      * "File origin" is most typically "Windows", but you may have to try several.
      * click "Next"
      * click on "Comma" for the "Delimiter" (i.e. what's between the columns)
        The bottom of the window should now show the data spread between the columns.
      * click "Next" and then "Finish".
      * When it asks "Where do you want to put the data",
        you can click in a cell on the worksheet to choose the top-left spot.
        Then click "OK".

2. Here's an example of creating a new column based on an old one :
    let's make a weight in kg column.
    * type "weight (kg)" in a new column, to the right of the others.
    * click in the next cell below that.
    * now click up in the formula bar.  It can be shown with the View/FormulaBar menu.
    * type "=", which is the start of a formula.
    * click in the cell with the corresponding weight; you'll
      see something like "=G6" in the formula bar.
      This means the value in cell G6 (a row/column address relative to the cell 
      with the formula).
    * now type /2.2, so it says "=G6/2.2" which is the weight in kg.
    * Click somewhere else in the workbook to see the number get filled in.
    * To duplicate that formula for the whole column, click in the workbook
      on the cell you just changed and drag downward.  
    * Then use the menu Edit/Fill/Down and voila - a whole new column
      based on the values in the weight column.

3. There are a bunch of statistical functions, but you need to turn 'em on :
    in the Tools menu, choose Tools/Add-Ins and click "Analysis ToolPak".
    That adds a new menu item Tools/Data Analysis

4. So let's count the number of people in each weight range.
    * do Tools/Data_Analysis/Descriptive_Statistics   (to see low/hi)
    * click "input range", then drag through all the weights
    * click "output range", then click in workbook where you want results
    * check "summary statistics", then OK.
    Looks like the weights go from 99 to 234.
    * Now type a column of bins : 80, 90, 100, ... 240  
      (I used "=cell+10" where 'cell' is you clicking on cell above, and "filling")
    * Tools/Data_Analysis/Histogram
    * same "input range" as before
    * click "bins", select column of 80...240
    * click "output range", pick where you want it
    * check "chart output", then OK
    * finally, adjust size and position of chart with the mouse
    * After clicking on the chart, you can change all sorts of things in Chart menu

5. Finally, let's plot height vs systolic
    * Insert/Chart
    * choose XY (scatter)
    * click "Series"
    * for X and Y, click in the box, then drag through the values
    * once the chart is created, double-click (data, axis, background,...) 
      to change 'em (log scale, error bars, colors, ...)