Excel Functions Relevant to Probability Distributions

A Few Mathematical Functions

ABS(x)

Absolute value, $|x|$.

SQRT(x)

$\sqrt{x}$

EXP(x)

$e^x$

Functions Related to Sums, Products, and Counts

For the examples discussed below, let $R_1 = \{4,6,7,-3\}$ and $R_2 = \{6,8,-5,7\}$ $R_3 = \{5, \textrm{""}, \textrm{"A"}, 6, -1\}$

SUM($R_1$)

Sum of the elements in range $R_1$. Example:
$\textrm{SUM}(R_1) = 4 + 6 + 7 - 3 = 14$

SUMSQ($R_1$)

Sum of the squares of the elements in range $R_1$. Example:
$\textrm{SUMSQ}(R_1) = 4^2 + 6^2 + 7^2 + (-3)^2 = 110$

SUMPRODUCT($R_1$, $R_2$)

Sum of pairwise products of elements from $R_1$ and $R_2$. Example:
$\textrm{SUMPRODUCT}(R_1,R_2) = 4(6) + 6(8) + 7(-5) -3(7) = 16$

This function can be particularly useful for finding the mean and variance for a discrete random variable X given its possible outcomes and their respective probabilities:



One can use the following formulas to replicate the above:

$$\begin{array}{|c|l|}\hline \textrm{cell} & \textrm{formula}\\\hline C5 & =C2^2\\\hline C6 & =SUMPRODUCT(C2:F2,C3:F3)\\\hline C7 & =SUMPRODUCT(C5:F5,C3:F3) - C6^2\\\hline \end{array}$$

SUMPRODUCT is also useful for calculating dot products.

PRODUCT($R_1$)

Product of elements in range $R_1$. Example:
$\textrm{PRODUCT}(R_1) = 4 \cdot 6 \cdot 7 \cdot (-3) = -504$

COUNT($R_1$)

Count of numeric elements in range $R_1$. Example:
$\textrm{COUNT}(R_3) = 3$

COUNTA($R_1$)

Count of non-empty elements in range $R_1$. Example:
$\textrm{COUNTA}(R_3) = 4$