Transcript Document

Statistics in Excel
Things about Excel you absolutely have
to know
• Autofilling cells
– dragging
– Home | Fill
• Referencing cells
– A1, A1:A10, B5 * A1
– absolute referencing … B5 * $A$1
Excel’s statistical capabilities
Name box Insert function
button
Formula bar
Formulas | Insert function
• Insert function – Shft F3
• Try it:
– Autofill values in column, calculate their =SUM
• Array functions – calculates multiple values and
puts them into an array of cells, e.g.
=FREQUENCY (binning)
• Give column a name
=SUM(Name_of_the_column)
Add More Stat into Excel
•
•
File | Options | Add-Ins | Manage | Go…
Analysis ToolPak
Now, you have Data | Data Analysis
Try now
Center
• =AVERAGE
• =AVERAGEA
– does not ignore cells with text, if a cell has text, its
treated as value 0
– not very useful, anyway
• =AVERAGEIF, =AVERAGEIFS
– conditional, IFS includes more conditions
• =MEDIAN
– data does not have to be sorted
Deviations
• variance – average of the squared deviations
2 
 X  X 
N
2
s2 
 X  X 
2
N 1
– =VARP, =VARPA … population variance
– =VAR, =VARA … sample variance
• Use =VARP if your data represent the whole
population. If you know your data are just a
sample from much larger population, use =VAR.
• Try yourself: what is the population and sample
variance of the following data: 50,47,52,46,45
Standard deviation
  2 
 X  X 
2
N
• =STDEVP, =STDEVPA
• =STDEV, =STDEVA
s  s2 
 X  X 
N 1
2
Standards
• =STANDARDIZE
– mean and standard deviation must be calculated
aforehead
– Try it
• =RANK
– Simply rank the data in the array
– Range in Ref field must have absolute references
– Try it
• =PERCENTILE
– find the value at the given percentile
• Data | Data Analysis | Rank and Percentile
– Try it
CLT simulation
• Show, that population that is not normally
distributes results in normally distributed
sampling distribution.
• Use huge population consisting of three
variables that can take a value of just 1, 2, 3.
Each of this variable is equally likely to appear.
– What is the name of such distribution?
• Select a cell D2, use
=RANDBETWEEN(1;3)
• Autofill the two cells right to the D2
• In G3, calculate average of D2:F2
=AVERAGE(D2:F2)
• Autofill remaining rows, so you have at
least 100 rows
• Enter possible values of the sample
mean into column I
– Express the possible values as fraction
– Before doing this, you must change the
format of I column to Fraction
• Select J3:J9, Formulas | More Func |
Statistical | FREQUENCY
– Data_array – data in G col
– Bin_array – I col
– Close the dialog box by Ctrl-Shft-Enter
• Insert | Columns
– Choose the first layout in the 2D graph