Home » Categories » CHARTrunner

CR: Bar Chart of a column of numbers in various categories

Revision Date: 2007-03-29

Problem:

You have a large column of numeric values in an Excel spreadsheet. You would like to see a bar chart showing how many numbers fall into some predefined categories. For example, your numbers might look like this:

Value
12
6
14
Etc.

What you want to see is a chart reflecting something like this:

Category Number of values
0 to 10 12
11 to 20 6
21 to 30 5
31 to 40 4
41-above 2

Solution:

Add a second column to your spreadsheet named Category. Put a formula in the first row of this column that uses the VLOOKUP function. VLOOKUP requires that you setup a lookup table, in another part of the sheet. This lookup table will define your categories. Here is what the lookup table would look like for the example above:

0 0 to 10
11 11 to 20
21 21 to 30
31 31 to 40
10000 41 - above

In the last row put a value larger than any value you would expect. Once you get the formula working, copy it into ALL the rows if the Category column. In your formula, be sure to use absolute references to the lookup table so that when you copy it into all the rows, it will continue to work. If the raw data is in Column A and your lookup table starts in D20, your formula will look something like this:

=VLOOKUP(A1,$D$20:$E$24,2,True)

The 2 means that we want to use the content of the second column in the lookup table. The True means that we are doing a range lookup. For example, the second row will be used for all values from 11 to 20.

Once this is finished save your spreadsheet. Next, use CHARTrunner to setup a Pareto chart using this spreadsheet. The only column you have to map, on the data definition tab, is the Category column. Tell CHARTrunner to treat this as a category column.

The attached Excel spreadsheet demonstrates how to setup the lookup table and shows you the formula to use when creating the Category column.

Attachments (1) Attachments
Related Articles RSS Feed
CR: A custom query for Length of Stay data
Viewed 2657 times since Fri, Nov 9, 2007
CR: CHARTrunner and SharePoint
Viewed 4214 times since Fri, Mar 9, 2012
CR: Time Display
Viewed 2156 times since Fri, Nov 9, 2007
CR: Error “External table is not in the expected format” using Excel 2007 workbook
Viewed 4239 times since Wed, Oct 14, 2009
CR: A simple query pattern for summarizing raw data
Viewed 3029 times since Tue, Oct 6, 2009
CR: Show every date on the chart
Viewed 2301 times since Thu, Jan 31, 2008
CR: Changing the chart type will change the Default Chart Style to missing
Viewed 2214 times since Fri, Nov 9, 2007
CR: Collating sequence not supported
Viewed 2800 times since Tue, Dec 11, 2007
CR: CR1.1 - Notes and Causes Problem
Viewed 2166 times since Fri, Nov 9, 2007
CR: The out-of-control (OOC) test may fail on the last sample with valid data
Viewed 2174 times since Fri, Nov 9, 2007