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: Formatting an Excel Date
Viewed 2497 times since Fri, Nov 9, 2007
CR: Charts with large amounts of data
Viewed 1950 times since Fri, Nov 9, 2007
CR: Label your X-Axis with more than one identifier
Viewed 2565 times since Tue, Oct 6, 2009
CR: Multiple chart lines from one data column
Viewed 2289 times since Fri, Nov 9, 2007
CR: Histogram bars beyond specification lines
Viewed 2350 times since Fri, Nov 9, 2007
CR: CR1.1 - Charts in the Chart Workspace do not print all of the charts
Viewed 2070 times since Fri, Nov 9, 2007
CR: Excel tips
Viewed 3067 times since Tue, Oct 6, 2009
CR: SQL Server Stored Procedures Returns No Records
Viewed 2298 times since Fri, Nov 9, 2007
CR: CR1.5 - My control chart data lines are not printing
Viewed 2209 times since Fri, Nov 9, 2007
CR: Largest Subgroup Size Supported by CHARTrunner
Viewed 2056 times since Fri, Nov 9, 2007