Open In App

How to Calculate a Five Number Summary in Excel?

Last Updated : 18 Jul, 2021
Improve
Improve
Like Article
Like
Save
Share
Report

In statistics, the five-number summary is mostly used as it gives a rough idea about the dataset. It is basically a summary of the dataset describing some key features in statistics. The five key features are :

  1. Minimum value: It is the minimum value in the data set
  2. First Quartile, Q1: It is also known as the lower quartile where 25% of the scores fall below it.
  3. Median (middle value) or second quartile: It is basically the mid-value in the dataset.
  4. Third Quartile, Q3: It is also known as the Upper quartile in which 25% of the data is above it and the rest 75% falls below it.
  5. Maximum value: It is the maximum value in the dataset. 

It is important to note that we can find the Five-number summary only for a single variable dataset. It is meaningless to calculate the Five-number summary for two variables data also known as bivariate.

These five number summaries give a concise inference about the distribution of the dataset. Using these quartiles we can find the Interquartile Range and create a Box Plot out of it. For more details refer Creating Box Plots in Excel.

In this article, we are going to discuss how to calculate the Five-number summary in Excel using a suitable example.

Implementation :

The following Excel in-built functions can be used to calculate the above quartiles.

QUARTILE(Array,quart) // Used in Excel 2007 version and lower

Array : Cell range
quart : The five quart values from 0 to 4

0- Minimum value
1- First Quartile (25 percent)
2- Median Value (50 percentile)
3- Third Quartile (75 percentile)
4- Maximum value

In the latest version of Excel, we use the function 

QUARTILE.INC(Array,quart)

The parameters and quart values are the same as discussed above.

To calculate the minimum, maximum, and median values we can also use the following functions :

=MIN(Array)        //to calculate minimum value
=MAX(Array)        //to calculate maximum value
=MEDIAN(Array)    //to calculate second quartile or the median

Example: Consider the dataset which consists of information about the BMI of 10 geeks.

Dataset

CALCULATION:

1. Minimum and Maximum Value

It can either be calculated using the MIN, MAX functions or using the QUARTILE function with quart value as 0 for minimum and 4 for maximum.

The dataset is stored in column “A” of the worksheet and the observations are stored from cell A2 to A11

So the array will start from A2 and end at A11.

Minimum BMI

Similarly, we can find the maximum value.

Maximum BMI

2. Median value or Second Quartile 

The Excel function MEDIAN can be used or we can use the QUARTILE function with quart value as 2.

3. Quartiles Q1 and Q3

The function QUARTILE or QUARTILE.INC is used to calculate these quartile values with quart values 1 for Q1 and 3 for Q3.

Quartile 1

Quartile 3

Summary

To calculate all the Five-number summary :

Quartile Values Formula
Minimum Value =QUARTILE.INC(Cell_Range, 0)
Q1 =QUARTILE.INC(Cell_Range, 1)
Median =QUARTILE.INC(Cell_Range, 2)
Q3 =QUARTILE.INC(Cell_Range, 3)
Maximum Value =QUARTILE.INC(Cell_Range, 4)

Five-Number Summary


Similar Reads

How to calculate Sum of Digits in a Number using Non Array Formulas in Excel?
In Excel, we can easily add the digits of a number. Usually, calculations that involve multiple values can be dealt with the help of array formulas, but here, we are going to find the sum of the digits of a number using non-array formulas. As an example, say we have a number 238 in one of the cells of the Excel sheet and we want to find the sum of
4 min read
Editing Excel Macros in Excel
Excel Macro is a set of actions that can be recorded, saved, used multiple times. This feature saves us a lot of time when dealing with repetitive tasks and huge data sets. We can always make changes to an existing Macro in Excel. There are two ways to record and run macros: Excel Commands Excel Visual Basic for Applications(VBA).1. Copying a Macro
1 min read
How to Calculate Frequency Distribution in Excel?
A frequency distribution table is an organized representation of the frequency of each element in a dataset/record. It helps us to visualize data in terms of class range and the number of time each element belong to that class interval. The table usually consists of two columns, the first is the class interval and the second one is the frequency it
4 min read
How to Calculate Relative Standard Deviation in Excel?
Definition: Related standard deviation is also known as the relative percentage standard deviation form, the deviation measurement which tells us how different numbers are dispersed around the mean in a particular set of data. This format shows the percentage distribution of data. If a relative standard deviation of the product is higher, that mean
2 min read
How to Calculate Weighted Average in Excel?
Have you ever think why we need an average or weightage average? In our daily, we can't calculate everything, let's suppose I am asking how's the weather for a week, you are not going to tell me every day's temperature, but an estimation here comes to the concept of an Average. Mathematically you can evaluate the average= Sum of Values/Number of Va
3 min read
How to Calculate Root Mean Square Error in Excel?
In simple terms, Root mean square error means how much far apart are the observed values and predicted values on average. The formula for calculating the root-mean-square error is as follows : Where, n: number of samplesf: Forecasto: observed valuesCalculating Root Mean Square Error in Excel : Follow the below steps to calculate the root means squa
2 min read
How to Calculate Mean Squared Error in Excel?
Mean Squared Error is defined as the mean of the square of the difference between the actual values and the expected values. Where, O = Observed value,E = Expected Value,n = No. of observations Example: Follow the below steps to evaluate the MSE in Excel: Step 1: Suppose we are given the data in form of expected and observed value as follows: Step
1 min read
How to Calculate Mean Absolute Error in Excel?
Mean absolute error is the measure of error between the observed and the expected values in a given data set. Where, O stands for Observed values,E stands for Expected values,n stands for total no. of observations. Now let us understand it with the help of an example. Example: Follow the below steps to calculate MAE in Excel: Step 1: Suppose we hav
1 min read
How to Calculate Time in Excel?
In this article, we will look into methods for calculating time and time variance in Excel. In Excel, time is stored as numbers where day and part of the day are represented by the whole numbers and decimal numbers respectively. It is also important to note that the whole number 1 represents 01 Jan 1900 which is also the starting point from which E
6 min read
How to Calculate Mean Absolute Percentage Error in Excel?
In statistics, we often use Forecasting Accuracy which denotes the closeness of a quantity to the actual value of that particular quantity. The actual value is also known as the true value. It basically denotes the degree of closeness or a verification process that is highly used by business professionals to keep track records of their sales and ex
2 min read
Article Tags :