This chapter gives an overview of some very useful statistical functions in Excel.
To calculate the average of a range of cells, use the AVERAGE function.
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.
To find the median (or middle number), use the MEDIAN function.
To find the most frequently occurring number, use the MODE function.
To calculate the standard deviation, use the STEDV function.
Min To find the minimum value, use the MIN function
To find the maximum value, use the MAX function.
To find the third largest number, use the following LARGE function.
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.
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.
Excel has two useful functions when it comes to generating random numbers. The RAND and RANDBETWEEN function.
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.
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:
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.