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: Charts not working on a new PC
Viewed 2000 times since Fri, Nov 9, 2007
CR: Changing the chart type will change the Default Chart Style to missing
Viewed 2105 times since Fri, Nov 9, 2007
CR: Time Display
Viewed 2075 times since Fri, Nov 9, 2007
CR: General Filtering Tips
Viewed 2205 times since Fri, Nov 9, 2007
CR: Largest Subgroup Size Supported by CHARTrunner
Viewed 2025 times since Fri, Nov 9, 2007
CR: Error “External table is not in the expected format” using Excel 2007 workbook
Viewed 4036 times since Wed, Oct 14, 2009
CR: Data grouping tardiness
Viewed 3161 times since Tue, Oct 6, 2009
CR: Show every date on the chart
Viewed 2216 times since Thu, Jan 31, 2008
CR: SQL Server Stored Procedures Returns No Records
Viewed 2268 times since Fri, Nov 9, 2007
CR: Unable to fully display settings and windows
Viewed 1919 times since Fri, Jan 22, 2010