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: Data grouping tardiness
Viewed 3098 times since Tue, Oct 6, 2009
CR: Chart [the name of the chart] failed with one or more errors
Viewed 2032 times since Fri, Nov 9, 2007
CR: Formatting an Excel Date
Viewed 2399 times since Fri, Nov 9, 2007
CR: Charts not working on a new PC
Viewed 1955 times since Fri, Nov 9, 2007
CR: SQL Server Stored Procedures Returns No Records
Viewed 2234 times since Fri, Nov 9, 2007
CR: Some Excel Spreadsheet Values Seem To Be Missing
Viewed 2174 times since Fri, Nov 9, 2007
Cr: Charting elapsed time
Viewed 2399 times since Fri, Nov 9, 2007
CR: Collating sequence not supported
Viewed 2570 times since Tue, Dec 11, 2007
CR: Installing CHARTrunner disables desktop wallpaper
Viewed 2015 times since Fri, Nov 9, 2007
CR: A simple query pattern for summarizing raw data
Viewed 2859 times since Tue, Oct 6, 2009