Important Excel Formulas

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