Creating Excel chart where each column contains a range of values

  • Thread starter Thread starter brendan
  • Start date Start date
B

brendan

hi there,

I have a really long column with lots of numbers, e.g.

4
5
6
13
17
2
15

I want to create a chart that contains all of the numbers in one range
in one bar, and all of the numbers in the next range in another bar,
and so on.

e.g.

1-10 ****
11-20 ***

Is there a way to do this in Excel?

thanks!
 
There's a worksheet function called =frequency() that can count up your numbers
based on the bins (categories) you give it.

I put a bunch of numbers (between 1:100) in A1:A600.

In B1:B10, I put 10 numbers:
10
20
30
....
100

Then I selected C1:C10 and put this in C1
=FREQUENCY(A1:A600,B1:B10)
But instead of just hitting enter, I hit ctrl-shift-enter. This is an array
formula that will fill the selected cells with its results. If you do it
correctly, excel will wrap curly brackets {} around your formula. (don't type
them yourself.)

Then in D1, I put this formula:

=REPT("*",C1)
and dragged down to D10.

For a neater effect, try this formula in D1 (and drag down):
=REPT(REPT(CHAR(134),4)&" ",INT(C1/5))&REPT("|",MOD(C1,5))
 
Another option is to use a PivotTable to summarize the data, and create
a chart from the PivotTable. There are some instructions and links here:

http://www.geocities.com/jonpeltier/Excel/Pivots/pivottables.htm

Your column of numbers would need a heading, e.g. Numbers. Then, in the
pivotTable layout, add Number to the Row area, and Number again to the
Data area. There, double-click on the Sum of Number button and choose to
summarize by Count.

In the pivotTable, right-click on the Numbers field button, and choose
Group and Show Detail>Group. Set 'starting at' to 1, and set 'by' to 10
 
Back
Top