Transcript Excel and Calculator

GrowingKnowing.com © 2011
GrowingKnowing.com © 2011
1
Central tendency
 People like to know, what are the central values?
 You can use central values to measure how you are
doing, compare with others, or what to expect.
 My grade is above the average.
 I am doing well.
 The average shows this employer promotes quickly, so
I will apply for a job.
GrowingKnowing.com © 2011
2
Mean
 Arithmetic mean is often called the “mean” or average.
 The mean is the number in the middle;
 add the size of every number for a total,
 then divide by the how many numbers given (count).
 The mean is an excellent place to start analyzing your data
 What is the mean salary where I work?
 What is the mean time it takes to drive to work?
 There are many types of mean calculations
 Arithmetic, weighted, harmonic mean, geometric mean, …
 We will learn arithmetic mean and weighted mean
GrowingKnowing.com © 2011
3
Manually calculate the mean?
 Example: you are given numbers 1,2, and 3.
 Add each number to get a total (i.e. sum, symbol ∑)
 1+2+3 = 6
 Count how many numbers you were given (symbol n)
 3
 Divide the total by the count
 6/3=2
 The mean includes every value, and finds the middle
point of those values, in our example, number 2.
GrowingKnowing.com © 2011
4
Using Excel
 Click the fx function button on the menu:
The mean for the data in
cells B71 to B75 is -1.2
GrowingKnowing.com © 2011
5
Formula
 Population Mean:
 Sample Mean:
 μ is called mu, the population mean.
 x̄ is pronounced "x bar“, the sample mean.
 Σ is Sigma, which is the sum of the data values.
 x is a variable representing each of the data values.
 N is the count of the data values for a population.
 n is the count of the data values for a sample.
GrowingKnowing.com © 2011
6
Beware of mean mistakes
 There are times when the mean can be misleading
 The average American has ½ a uterus and 1 testicle
 Almost everyone earns less than the average salary!
 One CEO earns 800 million, 50,000 workers earn
$35,000


Average salary of $50,999
50,000 people out of 50,001 earn less than average!
 When data includes an extreme value, called an
outlier, using the mean may be misleading.
GrowingKnowing.com © 2011
7
Weighted Mean
 Calculate the mean score for people playing a game
3 people got a score of 20, 4 got 10, and 8 people got 5
 Count how many numbers: n = 3 + 4 + 8 = 15
 Multiply each, then total:
3 x 20 + 4 x 10 + 8 x 5 = 140
 Mean = total sum / count
 = 140/ 15
= 9.3333
 Calculate the weighted mean for salary from the last slide
 1 x $800,000,000 + 50,000 x $35,000 = 2,550.000,000 /50001
= $50,998.98
GrowingKnowing.com © 2011
8
Weighted Mean Example
Units
Weight
Product
21
48
1008 = (21 x 48)
18
57
1026 = (18 x 57)
3
89
267 = ( 3 x 89)
23
66
1518 = (23 x 66)
Total = 65
Total = 3419
 Sum the number of units. 21+18+3+23 = 65

Excel =SUM(A1:A4) = 65
 Multiply units and weights, then sum
 Excel =SUMPRODUCT(A1:A4, B1:B4) = 3419
 Divide sumproduct by units = 3419 / 65 = 58.75
GrowingKnowing.com © 2011
9
Median
 The median is the number in the middle using a
count of how many numbers we are given
 Given the data, 1,2, 999.
 Median = 2 which is the number in the middle.
 Always sort the data first
 Sorting ensures the number in the middle does not
depend on the order numbers are given.
 For an odd list of numbers, take the middle number.
 For even list of numbers, use the mean of middle 2
numbers.
GrowingKnowing.com © 2011
10
Median examples
 Odd count of data items
 If we are given 1, 4, 9, 5, 3
 Sort: 1, 3, 4, 5, 9.
 Median is middle number = 4
 You can calculate the middle number with (N + 1)/2
 With 5 numbers. (5+1) / 2 = 3 so 3rd number is 4.
 Even count of data items
 Sort numbers
 Find data at position N/2 and average with the data item
above it in the sorted list
 Given: 1, 9, 4, 2, 2, 6
 Sort: 1, 2, 2, 4, 6, 9
 Take mean middle 2 numbers. 2 + 4 / 2 = 3. Median is 3.
GrowingKnowing.com © 2011
11
Median
 Excel function: =MEDIAN(A1: A6)
 The =MEDIAN function does not require you sort data
GrowingKnowing.com © 2011
12
Median versus Mean
 Mean for 1, 2, 3 is 2
 Median for 1, 2, 3 is 2
 Mean and median are close if the data has no outliers.
 Mean for 1, 2, 999 is 334.
 Median for 1, 2, 999 is 2.
 Use median instead of mean if outliers are extreme
GrowingKnowing.com © 2011
13
Avoiding median mistakes
 The common error is forgetting to sort the data.
 If the data list is short, students find medians easy to
find without a computer.
 To avoid errors, use =MEDIAN function if you have
access to Excel
 Excel will automate any needed steps to avoid silly errors
GrowingKnowing.com © 2011
14
Mode
 Mode is the data value that occurs most often
 We are often interested in the mode
 What is the most popular color car?
 Who is the popular leader?
 Cars sold by color: blue, blue, yellow, black, black, black.
 Black is the mode.
 You can have no mode or multiple modes.
 1,2,3,4 has no mode. No data value occurs most often
 1,2,2,3,3 has 2 modes, called bimodal. 2 and 3 occur the most
 1,2,2,3,3,4,4 has 3 modes. 2,3,4 occur most often
 1,2,2,3,3,4,4,4,5,5 has 4 as the mode.
GrowingKnowing.com © 2011
15
Excel
 Use the =MODE(a1:a4) function
 #N/A is Excel saying ‘no mode’
 N/A means Not Available
 TIP: =MODE shows only the 1st mode
 Excel 2010 has mode.sngl and mode.mult
 =Mode.sngl works the same as =mode
 =Mode.mult handles multiple modes but is
awkward to use
 We suggest sorting data, then run =MODE
multiple times to scan for multiple modes
GrowingKnowing.com © 2011
16
Can you write a poem about the mean?
No means
I am a man
of means
and a child
of extremes.
There is a mean
between extremes;
extremes predicting the means,
if you get what I mean ?
That's extreme, an extreme mean,
or did I mean, a mean extreme ?
Ex-mean, is no longer mean.
Such nonsense, extremeanus meantremes.
GrowingKnowing.com © 2011
Dr. Terry James
17