Excel-11 is a comprehensive guide to statistical functions in Microsoft Excel. Here you'll find practical tips, concise explanations, and illustrated examples for functions such as AVERAGE, AVERAGEIF, MEDIAN, MODE, STDEV, MIN, MAX, LARGE, SMALL, Weighted Average, Moving Average, RAND, RANDARRAY, RANK, Percentiles, Quartiles, Box and Whisker Plot, FORECAST, FREQUENCY, and more.
📚 Goal: Help you master Excel's statistical tools for data analysis, whether you're a student, analyst, or professional.
- AVERAGE
- AVERAGEIF & AVERAGEIFS
- MEDIAN
- MODE
- Standard Deviation (STDEV, STDEV.P, STDEV.S)
- Variance (VAR.P)
- MIN, MAX, LARGE, SMALL
- AVERAGEA
- Weighted Average
- Moving Average
- Random Numbers (RAND, RANDBETWEEN, RANDARRAY)
- RANK, RANK.AVG, RANK.EQ
- Percentiles and Quartiles
- Box and Whisker Plot
- FORECAST & FORECAST.LINEAR
- MAXIFS & MINIFS
- FREQUENCY
- Descriptive Statistics
- How to add Analysis ToolPak
- Screenshots
- Requirements
- Author
To calculate the average of a group of numbers, use the AVERAGE function.
Use AVERAGEIF to average cells based on one criterion (e.g., excluding zeros).
Use AVERAGEIFS to apply multiple criteria.
Find the median (middle number) using the MEDIAN function.
ℹ️ In case of an even number of values, the median is the mean of the two middle numbers.
Find the most frequently occurring number with the MODE function.
- STDEV: Calculates standard deviation for a sample (old function).
- STDEV.P: For the entire population.
- STDEV.S: For a sample (recommended).
ℹ️ Standard deviation indicates how much values differ from the average.
Variance is the square of the standard deviation.
Functions for finding minimum, maximum, largest, and smallest values in a dataset.
(See also examples below for usage with AVERAGE and LARGE.)
AVERAGEA returns the arithmetic mean, treating text and logical values differently than AVERAGE:
FALSEand text = 0TRUE= 1
- Calculate the average of the top 3 numbers using
AVERAGEandLARGE:
- Calculate the average of cells that meet criteria with
AVERAGEIF:
Calculate a weighted average using SUMPRODUCT and SUM.
Smooth trends in time series data with a moving average.
Use the Analysis ToolPak:
- Data tab → Analysis group → Data Analysis
- Select "Moving Average"
- Choose input range, interval, and output range
📈 The larger the interval, the smoother the trend line.
-
RAND: Random decimal between 0 and 1
-
RANDBETWEEN: Random integer between two values
-
RANDARRAY: Random array (Excel 365/2021 only)
⚠️ Random numbers update on every recalculation.
- RANK: Returns rank in a list.
- RANK.AVG: Average rank if duplicates exist.
- RANK.EQ: Updated version; works as RANK.
- Use
RANK,LARGE,FILTER, andSORTto display top results:
-
PERCENTILE: Find e.g. the 30th percentile.
-
QUARTILE: Find the 1st quartile.
Shows min, 1st quartile, median, 3rd quartile, and max.
- Select range (e.g., A1:A7)
- Insert → Statistic Chart → Box and Whisker
- Median (middle line)
- Mean (x in box)
- Whiskers show min/max
- FORECAST: Old function, now replaced by FORECAST.LINEAR
- Predicts a value along a linear trend.
💡 For seasonal data, use FORECAST.ETS (Excel 2016+).
Find maximum or minimum values based on criteria (Excel 2016+).
Calculate how often values fall into bins.
Use the Analysis ToolPak to generate summary statistics.
- Data tab → Data Analysis
- Select "Descriptive Statistics"
- Input/output range, check "Summary statistics"
- File → Options
- Add-ins → Analysis ToolPak → Go
- Check Analysis ToolPak → OK
- Data tab → Analysis group → Data Analysis
All images can be found in the /Screenshots folder.
- Microsoft Excel (recommended: 2021/365 for modern functions)
- Windows OS (for some add-ins)
Project and documentation by Kuba27x
Repository: Kuba27x/Excel-11