Documents
User Generated
Resources
Learning Center

VIEWS: 276 PAGES: 37

• pg 1
```									Intro to Excel
Nancy Clark
FSU College of Medicine

1
What are they used for?

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

2

   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

    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
Encounters.xls

12
Sorting Data

    Entire worksheet of tabular data with
    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

    Move or copy sheet…

2

1

3
15

   Worksheet
To delete a sheet
   Delete sheet

16
Page Setup (Format)

    Landscape or Portrait
    Print to 1 page wide
    Margins
    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
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

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

 Calls up Excel

28

 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