**Statistical Functions**

This chapter gives an overview of
some very useful **statistical functions** in **Excel**.

**Average**

To calculate the average of a range of cells, use the AVERAGE function.

**Averageif**

To average cells based on one criteria, use the AVERAGEIF function. For example, to calculate the average excluding zeros.

Note: <> means not equal to. The AVERAGEIF function is similar to the SUMIF function.

**Median**

To find the median (or middle number), use the MEDIAN function.

Check:

**Mode**

To find the most frequently occurring number, use the MODE function.

**Standard
Deviation**

To calculate the standard deviation, use the STEDV function.

**Min**
To find the minimum value, use the MIN function

**Max**

To find the maximum value, use the MAX function.

**Large**

To find the third largest number, use the following LARGE function.

Check:

**Small**

To find the second smallest number, use the following SMALL function.

Top of Form

**Negative Numbers to Zero**

A clever **Excel** trick to
change **negative numbers to zero** but leave positive numbers unchanged.

Below you can find two columns with numbers. To subtract the numbers in column B from the numbers in column A, execute the following steps.

1. Select cell C1.

2. Enter the formula =A1-B1

3. Click the lower right corner of cell C1 and drag the formula down.

Result:

4. To change the negative numbers to zero but leave the positive numbers unchanged, instead of =A1-B1, use =MAX(0,A1-B1) and drag the function down.

Result:

**Random Numbers**

Excel has two useful functions
when it comes to generating **random numbers**. The **RAND** and **RANDBETWEEN**
function.

**Rand**

The RAND function generates a random decimal number between 0 and 1.

1. Select cell A1.

2. Type RAND() and press Enter. The RAND function takes no arguments.

3. To generate a list of random numbers, select cell A1, click on the lower right corner of cell A1 and drag it down.

Note that cell A1 has changed. That is because random numbers change every time a cell on the sheet is calculated.

4. If you don’t want this, simply copy the random numbers and paste them as values.

5. Select cell C1 and look at the formula bar. This cell holds a value now and not the RAND function.

**Randbetween**

The RANDBETWEEN function generates a random whole number between two boundaries.

1. Select cell A1.

2. Type RANDBETWEEN(50,75) and press Enter.

3. If you want to generate random decimal numbers between 50 and 75, modify the RAND function as follows:

**Rank**

The **RANK function**
returns the rank of a number in a list of numbers.

1. If the third argument is omitted
(or 0), **Excel** ranks the largest number first, second largest number
second, etc.

Note: when we drag the RANK function down, the absolute reference ($A$1:$A$9) stays the same, while the relative reference (A1) changes to A2, A3, A4, etc.

2. If the third argument is 1, Excel ranks the smallest number first, second smallest number second, etc.

3. Use the RANK.AVG function to return the average rank if more than one number has the same rank.

**MaxIfs and MinIfs**

Use the MAXIFS and MINIFS function
in **Excel 2016** to find the maximum and minimum value based on one
criteria or multiple criteria.

1. For example, the **MAXIFS
function** below finds the highest female score.

Note: the first argument (D2:D12 in this example) is always the range in which the maximum or minimum will be determined. This MAXIFS function has 1 range/criteria pair (B2:B12/Female).

2. The **MINIFS function** below
finds the lowest female score.

3. For example, the MAXIFS function below finds the highest female score in Canada.