Histogram Tool – Excel

Provides a histogram table and chart options for the distribution of data.

Open via: Tools - Data Analysis - Analysis Tools – Histogram

Input Options: 

• Input Range enter the cells containing the data to analyse. 
• Bin Range enter the cells that contains boundary values to define the bin range  

If the bin ranges are left blank Excel automatically creates a set of evenly distributed bins between the minimum and maximum values.

Output Options

• Pareto (sorted histogram) to list results in descending order of frequency 
• Cumulative Percentage
• Chart Output

Example Histogram Chart

 Example Histogram Table

Example Histogram Source Data

Quartiles and Percentiles - Statistics - Excel

Quartiles - Excel

=QUARTILE(range,quartile#)

Quartile# can be 0,1,2,3 or 4 as detailed below:

0 = Minimum Value        
=QUARTILE(range,1)  or  =PERCENTILE(range,0)

1 = Lower Quartile Q₁ = number that 25% of observations are less than and 75% are greater than

=QUARTILE(range,1)  or  =PERCENTILE(range,0.25)

2 = Second Quartile Q_{2 = number} Half  the observation are larger than or equal to ( median).

=QUARTILE(range,2)  or  =PERCENTILE(range,0.50)

3= Upper Quartile Q₃ = number that 75% of observations are less than and 25% are greater than

=QUARTILE(range,3)  or  =PERCENTILE(range,0.75)

4 = Maximum Value

=QUARTILE(range,4)  or  =PERCENTILE(range,1)

Percentiles - Excel

=PERCENTILE(range,percentile#)
 

Percentile# can be any value between 0 and 1, e.g. detailed below:

0.9 = 90^th Percentile =PERCENTIL(range,0.90)

Standard Deviation - Statistics - Excel

Measures how widely values are dispersed from the mean average.

Standard Deviation for a sample - Excel

=STDEV(range)

The above function ignores text and logical values (False/True),
The below function includes logical values and text. Text and logical value False = 0, True = 1.


=STDEVA(range) 


Standard Deviation for an entire population - Excel


=STDEVP(range)


The above function ignores text and logical values (False/True),
The below function includes logical values and text. Text and logical value False = 0, True = 1.


=STDEVAP(range)