PQ Systems Knowledge Base

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:

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

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:

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.