|
|
Featured Tutorial
Featured Macro
Excel Magic Trick #243: MEAN MEDIAN MODE STDEV Histogram
See how to calculate and interpret Mean Median Mode Standard Deviation in Excel. Create a Frequency Distribution and then a Histogram. Basic Statistics.
Mean Median Mode, and Standard Deviation
Mean Median and Mode are all Averages
The reason we have averages is because we need "ONE" value that will represent all the values so we can talk about the "typical score". All the data is so spread out that is hard to talk about 'all" the data unless we calculate a typical value. Here are three ways to calculate a typical value: Mean, Median Mode.
MEAN is the arithmetic mean (add all the scores and divide by the count). In Excel we use the AVERAGE function
MEDIAN is the one in the middle (position) after we have sorted (this is good when we have extreme values like in real estate (most of the houses are around $200,000, but a few are $1,000,000)). In Excel we use the MEDIAN function
MODE is the one that occurs most often. This is good when we have "word" categories such as preference for "cola". In Excel we use the MODE function (It will not tell you when there are more than 1 mode).
The Standard Deviation tells you: 1) how spread out the data is; 2) what the mean deviation is; 3) does the average represent its data points fairly. In Excel we use the STDEV function for a sample and the STDEVP function for a population (population is all possible values; sample is some of the values but not all).
Histogram. SUMPRODUCT COUNTIF function formula. Column Chart Ampersand Concatenate
all these functions ignore blanks or dashes. If you really want to include them you must put a zero instead of a dash or blank.
Mean Median Mode, and Standard Deviation
Mean Median and Mode are all Averages
The reason we have averages is because we need "ONE" value that will represent all the values so we can talk about the "typical score". All the data is so spread out that is hard to talk about 'all" the data unless we calculate a typical value. Here are three ways to calculate a typical value: Mean, Median Mode.
MEAN is the arithmetic mean (add all the scores and divide by the count). In Excel we use the AVERAGE function
MEDIAN is the one in the middle (position) after we have sorted (this is good when we have extreme values like in real estate (most of the houses are around $200,000, but a few are $1,000,000)). In Excel we use the MEDIAN function
MODE is the one that occurs most often. This is good when we have "word" categories such as preference for "cola". In Excel we use the MODE function (It will not tell you when there are more than 1 mode).
The Standard Deviation tells you: 1) how spread out the data is; 2) what the mean deviation is; 3) does the average represent its data points fairly. In Excel we use the STDEV function for a sample and the STDEVP function for a population (population is all possible values; sample is some of the values but not all).
Histogram. SUMPRODUCT COUNTIF function formula. Column Chart Ampersand Concatenate
all these functions ignore blanks or dashes. If you really want to include them you must put a zero instead of a dash or blank.