Here we will summarize some of the functions that you will use the most. A full list of functions can be found in excel by looking under the “Formulas” tab and clicking the “Insert Function” button.
Descriptive statistics
Purpose |
Excel function |
Description |
Sum or adding |
=SUM(A:B) |
A:B The cells values that are to be summed |
Average or mean |
=AVERAGE(A:B) |
A:B The cell values that are to be averaged |
Standard deviation |
=STDEV(A:B) |
A:B The cells from which variance is to be calculated from |
Variance |
=VAR(A:B) |
A:B The cell values that are to be averaged |
Number of cells selected (sample size) |
=COUNT(A:B) |
A:B The cell that are to be counted |
Percentile |
=PERCENTILE(A:B,C) |
A:B The cells that contain your data set
C The percentile you are looking for (0.0 to 1.00) |
Quartile |
=QUARTILE(A:B,C) |
A:B The cells that contain your data set
C The quartile you are looking for (0,1,2,3,4) |
Median |
=MEDIAN(A:B) |
A:B The cell values from which the median is to be found |
Frequency |
=FREQUENCY(A:B,C:D) |
A:B The cells that contain your data set
C:D The cells that contain your bin array (ranges that you will be sorting by)
Note that you must select an equal number of output cells to your bin number when using the function. When done click crt+shift+enter. |
Max value |
=MAX(A:B) |
A:B The cells that contain your data set |
Minimum value |
=MIN(A:B) |
A:B The cells that contain your data set |
Statistical theory
Purpose |
Excel function |
Description |
p-value in a normal distribution |
=NORM.DIST(A,B,C,D) |
A Critical value (Z-score)
B Mean (0 for a standard normal distribution)
C Standard deviation (1 for a standard normal distribution)
D TRUE or FALSE This decides whether you are looking for the area of the tail, or just the probability of the critical value
This function gives a left-sided tail |
p-value in a t distribution |
=T.DIST(A,B,C) |
A Critical value (t-score)
B Degree of freedom
C TRUE or FALSE This decides whether you are looking for the area of the tail (TRUE), or just the probability of the critical value (FALSE)
This function gives a left-sided tail |
p-value in a chi squared distribution |
=CHISQ.DIST(A,B,C) |
A Critical value (t-score)
B Degree of freedom
C TRUE or FALSE This decides whether you are looking for the area of the tail (TRUE), or just the probability of the critical value (FALSE)
This function gives a left-sided tail |
p-value in a f distribution |
=F.DIST(A,B,C,D) |
A Critical value (t-score)
B Degree of freedom for variable 1
C Degrees of freedom for variable 2
D TRUE or FALSE This decides whether you are looking for the area of the tail (TRUE), or just the probability of the critical value (FALSE)
This function gives a left-sided tail |
Statistical tests
Purpose |
Excel function |
Description |
t test |
=T.TEST(A:B,C:D,E,F) |
A:B Data for group 1
C:D Data for group 2
E 1 or 2 One tailed (1) or two tailed (2) test
F 1, 2, or 3 Type of t test: paired (1), common variance (2), non-common variance (3) |
Chi squared test |
=CHISQ.TEST(A:B,C:D) |
A:B Observed data set
C:D Expected data set |
Pearson’s R (correlation) |
=CORREL(A:B,C:D) |
A:B Data set for independent variable
C:D Data set for dependent variable |