Using Histogram for Data Analysis

Histogram

After completing the J.P. Morgan Corporate Challenge 5K race  I was curious: how many other runners had a similar finish time as I? What an Excel-lent opportunity for a Histogram!

DataAnalysisHistogram

 

A Histogram analyzes values, groups those numbers into bins, (population frequencies) of your choosing, and displays that data in a table or chart. The Histogram tool is part of the Data Analysis Toolpak. It may not initially appear on your ribbon, but is a cinch to install.

Adding the Data Analysis Toolpak

  1. On the Ribbon, click File, then Options.
  2. Click Add-ins.
  3. On the Manage drop-down, select Excel Add-ins and click Go.
  4. Select Analysis Toolpak and click OK.

For a simple Histogram, here’s what you will need:

  • Input Range: cells containing values to be reviewed. The range must be sorted in ascending sequence.
  • Bin Range: cells to act as virtual bins within which Excel will place matching numbers. For example, a teacher grading a test might use Bin values for the test scores she wants to lump together. Bin range is an optional; if left blank, Excel will create Bins.In my example (below) the Bin values are increments of one minute, between 17 and 37 minutes (the fastest and longest finishing times).
BIN
Bin range, using finish times from 17:00 to 37:00
  • Output: Location for the Histogram table. These options include RangeNew Worksheet, and New Workbook.
  • Chart Output: (optional) charts the Histogram table output.

Creating the Histogram Table and Chart

  1. On Data tab of the Ribbon, click Data Analysis. The Data Analysis dialog appears.
  2. Select Histogram and click OK. The  Histogram dialog appears.
  3. Select or enter the Input Range (e.g., E11:E2804) , Bin Range (e.g., K13:K32), and Output range (e.g. M12).,
  4. Check Chart Output.
  5. OK.

To download the sample file click JPM Corp Challenge (SF) Results

Watch this ~4m video to learn more.

 

Cheers!

hɔuᴉnb

 

Related posts:

Comments and questions are always welcome!

Advertisements

Author: quincy harley jr

Quincy is a technophile, coach and Learning Development expert. He has an extensive legal IT background and is practiced in MS Office application support and product development. As a project leader he has been integral in numerous new application rollouts. Whatever spare time he has, is spent with reading, archery and watching his young sons grow.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s