Documents
Resources
Learning Center
Upload
Plans & pricing Sign in
Sign Out

Sample Excel Spreadsheets

VIEWS: 276 PAGES: 37

Sample Excel Spreadsheets document sample

More Info
  • pg 1
									Intro to Excel
Spreadsheets
Nancy Clark
FSU College of Medicine



1
    What are they used for?

   Financial forecasting      Budgets
   Statistical analysis       Research
   Data collection            Gradebook
   Data manipulation          Charts
   Summarizing data           Big tables
   Communicating data         Job aids




    2
Spreadsheet Construction



       Columns (letters)
       Rows (numbers)
       Worksheets
       Cells (E9)




3
Mousing Around a Cell




                 Mon       Tue



        Highlight/Select
          Fill Contents
        MoveContents

4
Cell Formats                 5-Mar 45.2719
                             STUDENT45.3
                             5-Mar-02 27%
                             3/5/2002 .2719
                                    $45.27

   Words
   Numbers
       # decimal places
       $
       %
   Days/Dates
   Formulas Calculations…



5
Formulas

   =A2+B2+C2         $29.00
   =SUM(A2:AS2)      $1,886.00


                                AutoSum



                            Formula


                            Results

6
Formulas                     =SUM (A2:AS2)
                          Function     Range

   =Today()                       Column
   Average(range)                     (C2:C80)
   Count(range)                   Row
                                       (B2:AK2)
   Max(range)
                                 Block
   IF(logical_test,value_if_true,value_if_false)
                                     (A2:G50)
       =IF(F3>89,"A",IF(F3>79,"B",IF(F3>69,"C","F")))
   Formulas can be Filled Down


Insert menu, Function…, Help on this function
7
      More on Cell Formats

 Alignment
 Word wrap

 Orientation

 Shrink to fit

 Font




      8
    More on Cell Formats

   Width
   Height




    9
Merging and Splitting

    Add to Formating Toolbar
    Highlight cells to merge
    Click Merge Button
    Unmerge




10
Copying and Pasting

    Highlight cells
    Hit Copy (CTRL-C)
    Dotted lines
    Click on target cell
        Top of area
    Hit Paste (CTRL-V)
    Unless still dotted not
     in Clipboard
    CTRL to select multiple
     targets

11
    Worksheets

   Open
    U:\Nancy Clark\Spreadsheets\Handbase Pt
      Encounters.xls




    12
Sorting Data

    Entire worksheet of tabular data with
     headings
        Data menu, Sort…
    Small section of worksheet
      Highlight section
      Hit sort button




13
Formulas that Reference Other
Worksheets

    =COUNT(Allison!B2:Allison!B100)
        Allison!B2 -- cell B2 on sheet Allison




14
Copying Worksheets

    Edit menu
    Move or copy sheet…



                               2




         1

                           3
15
Adding and Deleting Sheets

To add a sheet
 Insert menu
        Worksheet
To delete a sheet
 Edit menu
        Delete sheet



16
Page Setup (Format)

    Landscape or Portrait
    Print to 1 page wide
    Margins
    Headers and Footers
    Print Titles




17
Making Sheets Pretty

    Do it yourself method
      Fill Color
      Font Colors
      Borders toolbar

 Autoformat (Format)
 Conditional Formatting




18
Charts

    Visual
     representations of
     data
    Select Data
    Insert menu,
     Chart…




19
Charts – Step 1




20
Charts – Step 2




21
Charts – Step 3




22
Charts – Step 4




23
Charts – Final
           Patients by Age Group


               0%
             5% 5%   16%
                                   0 - 3 wks
                                   1 mo - 10 yrs
     74%                           11 - 24 yrs
                                   25 - 64 yrs
                                   65 yrs+




24
Different Scales

                Score on     % Not
                  Survey      Installed
PDxMD                 4.76                 8
InfoRetriever         4.80                 7
ePocrates             4.87                 8
Stedmans              4.65                 9
CP Onhand             4.45                22
25
                                  PDA Software

25




20




15

                                                                        Score on Survey
                                                                        % Not Installed

10




5




0
     PDxMD   InfoRetriever   ePocrates           Stedmans   CP Onhand




       26
Printing

    Always Print Preview
    Page Setup
    Select Print Area
        Highlight area
        File, Set Print Area
        File, Clear Print Area




27
Insert Excel Spreadsheets

 Copy and Paste
 Use Insert Microsoft Excel Worksheet
  button
 Double click on table or chart

 Calls up Excel




28
Suggestion for Spreadsheet

 Summarize your patient data
 No data? Do the composite.
 Do a chart of some part of the data
 Put the data sheet in as well
 3 sheets:
      Data
      Summary
      Chart

29
Statistical Analysis

Using Excel




30
Descriptive Statistics

    Distribution          Dispersion
        frequency             Range
         distribution          Standard deviation
        Histogram             Variance
    Central tendency      N
        Mean              Not P (inferential
        Median             stats)
        mode


31
Central Tendency

    Mean
        =AVERAGE(b2:b1500)
    Median
        =MEDIAN(A2:A7)
    Mode
        =MODE(A2:A7)
    N
        =COUNT(A2:A1500)
        =COUNTBLANK(A2:B5)
32
Dispersion

    Range
        =MAX(A2:A60)- MIN(A2:A60)
    Standard deviation
        =STDEV(A2:A110)
    Variance
        =VAR(A2:A110)



33
Distribution

    Frequency distribution
      Not easy – use SPSS
      FREQUENCY(data_array,bins_array)
      Use help

    Histogram
        Bar chart of frequency table



34
Data Tables

 Field names at top
 Each row is a record (sample)

 Sorting whole table
      By one column
      By more than one column

    Sorting individual sections


35
Hands on experience

    Analyze data in examples.xls




36
Sensitivity, Specificity

    sensitivity =              Patient Patients
     a / (a+c)                  s with  without
                                disease disease
    specificity =
     d / (b+d)       Test is
                     positive
                                  a         b
                     Test is
                     negativ
                     e            c         d
37

								
To top