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!

Create Names From Selection

Creating Defined Names enables you to take advantage of a host of excel features and shortcuts. This post shows one easy method to create Defined Names from your selection and showcases a nifty method to find where two Defined names intersect.

This tip involves naming cells, also known as Defined Names. Among the many benefits, Defined Names can be used to:

  • quickly navigate large spreadsheets
  • easily define print areas
  • simplify formula entry

In the below table you want to easily reference the data in any given column or row. Since the data is in a table you can easily create a Defined Name, for each column and row, using the Create from Selection command.

CreateNamesIntersect

Creating Defined Names From Selection

  1. Select all the cells in the table.
    Tip: select one cell, then press CTRL + A.
  2. On the Formulas tab, in the Defined Names group, click Create from Selection. The Create Names from Selection dialog appears.
  3. Check Top row and Left column.
  4. OK.

To display the Defined Names, press F3 or click the Name Box drop-down. Defined Names will also appear as you enter formulas, preceded by the name tag iconNametag.

 

PowerTip: The intersection of two Defined Names can be displayed using a formula.

Using Defined Names to Display the Intersecting Value

  1. In a blank cell, type =.
  2. Type the first Defined Name followed by a space.
  3. Type the second Defined Name and press Enter.

Cheers!

hɔuᴉnb

 
Watch this ~1m video to learn more.

Previous post and additional reading:

Automating the Spreadsheet using Check Boxes, DSUM & IF

In this post, we put this altogether with an interactive report. Using check boxes and DSUM the user will select what criteria to sum by.

4 of 4: Automating the Spreadsheet using Check Boxes , If Statements, and DSUM

In the previous posts we added Check Boxes to our spreadsheet, linked Check Boxes to cells, and created a conditional summation using DSUM. In this post, we put this altogether with an interactive report. Using Check Boxes, IF statements, and DSUM, the user will select what criteria to sum by.
Dsum_CheckBox
Click to enlarge

In the example depicted above, we sum the values in the Amount column based upon the criteria entered in column G.

Watch this ~3m video to learn more.

 

Adding and Linking the Check Boxes

DSUM_Checkbox Properties Linked Cell

  1. Insert two blank rows above the database.
  2. In the second blank row, add Check Box Form controls for each criteria value to be included in the summary. In the example depicted, we created Check Boxes for each region (see Adding a Check Box for instructions).
  3. Link the Check Box to the cell above: Right + Click on the Check Box, select Format Control, and enter above cell in the Cell link field (for more detailed instructions click here ). Repeat for each Check Box.

 

Creating the Criteria Column

DSUM_Checkbox Function dialog

We will use the IF function to copy the Checked labels into the criteria range

  1. In a blank column, enter the field name into the cell (i.e., ‘Region’) .
  2. Move cursor to the cell immediately below.
  3. On the Formula tab, in the Function Library group, click Logical and select IF.
  4. In the  Logical_test field, select or type Linked cell above the Check Box. In the example depicted below enter A3 to test Checked state for ‘North America.’
  5. In the Value_if_true field, select or type the Check Box cell. In the example depicted below enter A4 for the text ‘North America.’
  6. In the Value_if_false field, type ” – ” (dash in quotes).
  7. Click OK.

Repeat steps 2 through 7 for each Check Box value to be included in the summation.

To  test the IF statements, check and uncheck the boxes. When checked, the text associated with that Check Box will appear in the criteria column.

 

Inserting the DSUM function

DSUM_Checkbox Properties DSUM

  1. Select the cell to contain summation (i.e., F5).
  2. On the Formulas tab, in the Function Library group, click Insert Function. The Insert Function dialog appears.
  3. In the Search for function area, type DSUM and click Go. DSUM appears in the Selection function area.
  4. Click OK. The Function Arguments dialog appears.
  5. In the Database area, enter either the saved name or cell reference for the table (e.g., FoodInventory or A7:F255).
  6. In the Field area type the column heading to summarize, in quotes (i.e., “Amount”).
  7. In the Criteria area, type or select the cells that contain the criteria; this includes the column heading(s) and value to search for (i.e., G4:G8).
  8. Press OK.

Tip: Hide the criteria column to make the report appear less cluttered.

That’s it. Now, to add a region to the summation, check that region’s Check Box. To remove a region from the summation, uncheck it.

 

Cheers!

hɔuᴉnb

 

Previous post and additional reading:

Comments and questions are always welcome!

Using DSUM

3 of 4: Summing values using DSUM

(revised and expanded from an earlier post)
In the previous posts we added Check Boxes to our spreadsheet and linked those Check Boxes to cells to display True when checked and False when unchecked. In this post, we take a brief break from Check Boxes to explore the DSUM function. But don't fret, in upcoming post we will tie Check Boxes and DSUM together to produce an interactive report.
Click to enlarge

DSUM, short for Database Summation, enables you to summarize data in a table (aka, database) based upon a specific condition (aka, criteria). Similar to the SUMIF function, DSUM can employ Boolean (i.e., AND/OR) logic in its criteria.

=DSUM( database, field, criteria)

  • Database: the entire table, including row values, and headings
  • Field: the column containing numbers to sum
  • Criteria: condition to test for inclusion in the summary (contains field headings and value)

In the example depicted above, we sum the values in the Amount column based upon the criteria entered in B3:B4 (i.e., Region: Europe)

Watch this ~3m video to learn more.

 

Tip: when working with large data, name the cells to simplify formula entry.

To Name a Group of Cells

  1. Select all the cells.
    Tip: select one cell, then press CTRL + A to select all contiguous (touching) cells
  2. Click in the Name box, type name (e.g., “FoodInventory”) and press Enter.
    Note: Names cannot begin with a number, or include spaces.

Creating the Summation using DSUM

  1. Type criteria, entering the field names on one row and the condition to test right below it
    (i.e., In B3 enter Region and in B4 enter Europe).
  2. Select the cell to contain summation (i.e., A4).
  3. On the Formulas tab, in the Function Library group, click Insert Function. The Insert Function dialog appears.
  4. In the Search for function area, type DSUM and click Go. DSUM appears in the Selection function area.
  5. Click OK. The Function Arguments dialog appears.
  6. In the Database area type the name of database (i.e., FoodInventory).
    Tip: press F3 key to display list of named cells and select  from that list.
  7. In the Field area type the column heading to summarize, in quotes (i.e., “Amount”).
    Alternatively, you can enter the number of the summation column, counting from left of the table (i.e., 5).
  8. In the Criteria area, type or select the cells that contain the criteria; this includes the column heading(s) and value to search for (i.e., B3:B4).
  9. Press OK.

Next and  Final: Automating the spreadsheet using DSUM and Check Boxes.

Cheers!

hɔuᴉnb

 
Previous post and additional reading:

Comments and questions are always welcome!

Linking the Check Box to a Cell

2 of 4: Linking the Check Box to a Cell

In the earlier post, Adding Check Boxes, we added the Form Check Box control to our spreadsheet. In this post we explore adding an ActiveX Check Box to the spreadsheet, and how to link a Check Box to a cell. The linked cell will display True, when the Check Box is checked, and False when unchecked.
Click to enlarge
Form Check Box with Linked Cell

 

Form Check Box

Of the two types of Check Box controls, Form and ActiveX, the Form control is the simpler of the two types.

Form Check Box features:

  • simple management, with comparatively few options
  • compatible with the XLS file format (Excel 2003 and earlier)

Watch this ~3m video to see how to place the Form Check Box and link it to a cell.

To Link the Check Box Form Control to a Cell

  1. Add the Form Check Box control (for details, see earlier post or review the above video).
  2. Right + Click on the Check Box control and select Format Control.  The Format Control dialog appears.
  3. On the Control tab, click in the Cell link field, then type or select the cell.
  4. OK.

ActiveX Check Box

For more programming power, use the ActiveX Check Box. Similar to Form Check Boxes, the ActiveX Check Box has additional options.

ActiveX Check Box features:

  • additional formatting and programming options
  • designed to be associated with macros
  • compatible with the XLSM and XLTM file formats (macro enabled file and macro enabled template, respectively)

Watch this ~3m video to see how to place the ActiveX Check Box and link it to a cell.

To Link the Check Box ActiveX Control to a Cell

  1. On the Developer Tab, In the Control group, Click the ActiveX Check Box control, then Click + Drag to place on spreadsheet.
    Note: the spreadsheet is automatically placed in Design Mode.
  2. Right + Click on the Check Box control and select Properties.  The Properties dialog appears.
  3. Click in the Caption property field and delete the text (i.e., CheckBox1).
  4. For the BackStyle property, click drop-down and select 0 – fmBackStyleTransparent.
  5. Click in the LinkedCell property field and type the cell reference (i.e. F10).
  6. Close the Property dialog.
  7. On the Developer tab, click Design Mode to toggle Design Mode off.

 

Next: Using DSUM!

Cheers!

hɔuᴉnb

Previous post and additional reading:

Comments and questions are always welcome!

Adding Check Boxes

1 of 4: Adding Check Boxes

In these four post we will will create an interactive report using Check boxes and DSUM (and without the using macros). In this first of four, we start with adding Check Boxes.

Since Excel 2007, the use of macros in spreadsheets have added an additional element of complexity. Macros must now be stored in XLSM format, which raises a red flag in excel as a potential threat. In this series of posts, we eschew VBA and ActiveX automation, in exchange for ‘old fashioned’ excel programming, using form controls and statistical functions.

Check Box

Adding Check boxes to your spreadsheet can make it easier for you and your reviewers to edit spreadsheet data. The Check box tool is found on the Developer Tab. To add the Developer tab, click File, Options, Customize Ribbon, and check Developer.

To Add a Check box

  1. On the Developer tab, in the Controls group, click Insert Check box (Form Control).
  2. Using the mouse, Click & Drag to draw a Check box control. A Check box control is placed, consisting of the Check box and the label text.
    Tip: Hold down the ALT key as you Click & Drag to draw an object equal in size to the selected cell.
  3. Right + Click on the Check box control and select Edit Text. An insertion point appears.
  4. Delete the Check box label text (e.g. Check Box 1).
  5. Click & Drag rightmost selection handle to reduce the size of the Check box control.
  6. Enter your text into the cell that contains the Check box.
    Tip: To indent the text, on the Home ribbon, click the Alignment dialog launcher and increase the cell Indent to 2.

 

alignmentNext: Linking the Check Box to a Cell!

Cheers!

hɔuᴉnb

Additional reading:

Comments and questions are always welcome!

Excel NCAA templates

March Madness is upon us!

In 1892 Coach James Naismith hammered two peach baskets into the gym balcony, and forever changed how we use Excel!

NCAA_LooneyToons

Using these free templates available in Excel, you can plot out your final four projections!

To access NCAA templates

  1. Click File and select New.
  2. In the Search Office Templates field type ‘NCAA‘ (no quotes) and press the search arrow. The search results will populate the dialog.
  3. Select preferred template and click Download.

ncaa

from Wikipedia:

[Naismith] divided his class of 18 into 2 teams of 9 players each and set about to teach them the basics of his new game of Basketball. The objective of the game was to throw the [ball], into the fruit baskets nailed to the lower railing of the gym balcony. Every time a point was scored, the game was halted so the janitor could bring out a ladder and retrieve the ball. Later, the bottoms of the fruit baskets were removed. The first public basketball game was played in Springfield, MA, on March 11, 1892. That day, he asked his class to play a match in the Armory Street court: 9 versus 9, using a soccer ball and two peach baskets.

Cheers!

hɔuᴉnb

Additional reading:

Comments and questions are always welcome!