|
|
||||
|
|
|
|
|
|
Single Argument Statistical Functions on Excel
In the first lesson, you learned how to enter the SUM function, which is a single argument function. In this lesson, you will learn how to use 18 single argument statistical functions. Before we do so, lets enter the following data into a blank spreadsheet
|
A |
B |
C |
D |
|
|
1 |
||||
|
2 |
1 |
|||
|
3 |
2 |
|||
|
4 |
2 |
|||
|
5 |
||||
|
6 |
3 |
|||
|
7 |
4 |
|||
|
8 |
4 |
|||
|
9 |
5 |
|||
|
10 |
word |
|||
|
11 |
||||
Make sure you put the 1 in cell B2, the 2s in cells B3 and B4, the 3 in B6 the 4s in B7 and B8, the 5 in B9 and type word in B10. Now, put your cursor in cell C11 and type =AVEDEV(B2:B10) then press enter. Cell C11 should now say 1.142857, which is the average absolute deviation of 1, 2, 2, 3, 4, 4, 5. Let's review the meaning of what we typed. The = tells the program that what follows is a formula or function. AVEDEV() is a function returning the average absolute deviation of numerical values in the specified cell range. B2:B10 is the specified cell range, with B:2 being the cell in the top left corner of the data and B10 being in the lower right hand corner of out data. The AVEDEV() function ignores any cells that are blank or that have characters other than numbers in them. In this case, the formula ignores cell B5 (which is blank) and cell B10 which contains a word.
That is basically all there is to single argument functions. The next page contains eighteen single argument statistical functions and the answers they will give using B2:B10 as the cell range with the data you have above. Try them and see how easy this is.
Single Argument Statistical Functions on Excel
|
Function |
||
|
=AVEDEV(B2:B10) |
Returns the average of the absolute deviations of data points from their means. |
1.142857 |
|
=AVERAGE(B2:B10) |
Returns the average (arithmetic mean) of its arguments. |
3 |
|
=COUNT(B2:B10) |
Returns the number of cells that contain numbers within the list of arguments. |
7 |
|
=COUNTA(B2:B10) |
Returns the number of cells that are not empty within the argument |
8 |
|
=COUNTBLANK(B2:B10) |
Counts empty cells |
1 |
|
=DEVSQ(B2:B10) |
Returns the sum of squares of deviations of data points from their sample mean. |
12 |
|
=GEOMEAN(B2:B10) |
Returns the geometric mean of an array or range of positive numeric data |
2.667097 |
|
=HARMEAN(B2:B10) |
Returns the harmonic mean of a data set of positive numbers: the reciprocal of the arithmetic mean of reciprocals. |
2.307692 |
|
=KURT(B2:B10) |
Returns the kurtosis of a data set |
-1.2 |
|
=MAX(B2:B10) |
Returns the largest value in a set of values. |
5 |
|
Mean |
See AVERAGE |
|
|
=MEDIAN(B2:B10) |
Returns the median, or the number in the middle of the set of given numbers. |
3 |
|
=MIN(B2:B10) |
Return the smallest number in a set of values. Ignores logical values and text. |
1 |
|
=MODE(B2:B10) |
Returns the most frequently occurring, or repetitive, value in an array of range of data. Warning: If multimodal, will only tell you one mode without telling you there are others. |
2 |
|
=SKEW(B2:B10) |
Returns the skewness of a distribution: a characterization of the degree of asymmetry of a distribution around its mean. |
0 |
|
=STDEV(B2:B10) |
Estimates the standard deviation based on a sample (ignores logical values and text in the sample). |
1.414214 |
|
=STDEVP(B2:B10) |
Calculates standard deviation based on the entire population given as arguments (ignores logical values and text). |
1.309307 |
|
=VAR(B2:B10) |
Estimates the variance based on a sample (ignores logical values and text in the sample). |
2 |
|
=VARP(B2:B10) |
Calculates variance based on the entire population given as arguments (ignores logical values and text). |
1.714286 |