How to describe the data

1
How to describe the data?
1
Enter the individual grades in columns into an
Excel worksheet
2
Use descriptive statistics techniques to
summarize the set of data.
2
Introducing Functions in Excel

• Just as a function as defined in Mathematics
  relates an independent variable x with a
  dependent variable y
• in Microsoft Excel, a function is a predefined
  formula that takes supplied values (i.e. your
  data set), performs an operation, and returns
  another value.

3
Syntax

• Functions use the syntax NAME(arguments), where
  NAME is simply the name of the function.
• The arguments, in parentheses, are the cell
  references, values, or other instructions that
  the function needs to do its work.

Note You can type a function name in either
uppercase or lowercase Excel changes the name to
uppercase when you finalize the entry by pressing
Enter or one of the cursor movement keys.
4
Using Functions

• If you know the name of the function you want to
  enter, just activate the cell that you want to
  contain the function results, and then type
  followed by the function name and arguments.
• You can point to cells or cell ranges to add
  their addresses to the argument.

5
Paste Function Dialog Box

• If you do not know the function name or
  arguments, get help from Excel using the Function
  Wizard.
• The Insert Function tool is located on the
  Formulas ribbon (Excel 2007, shown below) or
  Standard toolbar (Excel 97-2003, not shown).
  Click the tool.

6
Paste Function Dialog Box

• First, choose the Function Category you are
  interested in and then select the function you
  want in that category. When you select the proper
  function, click OK to move to Step 2.

7
Paste Function Dialog Box

• In Step 2, specify the cells the function should
  use for its calculations. These cells are the
  arguments. Select the cells with the mouse and
  click OK.
• Note The format and minimum number of arguments
  required may differ between functions.

8
Basic Statistical Functions

• We will briefly survey several of the most useful
  statistical functions in Microsoft Excel.

9
Average

• Using the AVERAGE( ) function, we find the
  class's average (or arithmetic mean) grade is a
  disappointing 2.47, or a mid-C. The syntax for
  this common function is AVERAGE(number1,
  number2, ...)

10
Median

• We can further analyze the data using the MEDIAN(
  ) function. The median gives the middle number in
  a set of numbers and its syntax is
  MEDIAN(number1, number2,...). We see that the
  median grade is 3.0, meaning that half of the
  grades are higher than 3.0, and half are lower.

11
Mode

• Additionally, we can also analyze the grade
  distribution by using the MODE( ) function. The
  mode gives the most frequently occurring value of
  a set of numbers and its syntax is MODE(number1,
  number2,...). We see that the mode grade is 4,
  meaning that a score of 4 was the most common.

12
Count, Max, Min

• Without going into too much detail, we can also
  use some of Excel's built-in functions to
  determine the number of grades entered, and the
  maximum and minimum grades of the distribution.
• The COUNT( ) function gives the number of cells
  that contain numbers. Its syntax is
  COUNT(value1, value2, ...).
• The MAX( ) function returns the largest value in
  a set of numbers. Its syntax is MAX(number1,
  number2, ...).
• The MIN( ) function returns the smallest value in
  a set of numbers. Its syntax is MIN(number1,
  number2, ...).

13
Count, Max, Min
14
Standard Deviation

• The average grade of the class could be better
  interpreted by computing its standard deviation.
  This is a statistic that that tells us how
  tightly all the various examples are clustered
  around the mean in a set of data.
• To quickly determine the standard deviation of
  any measurement, use Excel's built-in STDEV( )
  function.

15
Standard Deviation
16
Summary of Basic Functions
17
Descriptive vs. Inferential Statistics
Descriptive Statistics Frequency
distributions Measures of central tendency Mean,
median, mode Measures of dispersion Standard
deviation, percentiles Histograms Etc.
Inferential Statistics Z-Test/T-Test Chi-Square
Test Analysis of Variance (ANOVA) Regression
Analysis Correlation Analysis Etc.
Statistical software, e.g. SPSS
Spreadsheets, e.g. Microsoft Excel
Industry-specific modeling software
18
Links

• http//www.utexas.edu/its/training/handouts/excels
  tat/
• http//phoenix.phys.clemson.edu/tutorials/excel/st
  at.html