Creating a Simple Bar Chart - 2 Areas for help please

[BRob]

I've got 2 columns of numbers - column A is a continuous set of integers
going from about 40 to 210; column B is a frequency count going from blank
up to 10. A sample is shown below.

PROBLEM 1

Using XL2007 I can't even seem to get a simple bar chart showing frequency
count (y axis) against number (x axis). I just get either a total as a
single bar or a cumulative count.

PROBLEM 2

Once I've got this working I'd like to be able to group those numbers (eg
into 5's or 10's) so as to reduce the length of the x-axis

Can SKS help out please.

Tx




44 2
45
46
47 1
48
49 1
50
51
52 2
53 2
54 5
55

 

[Jon Peltier]

Put a label atop column B, and leave a blank cell atop column A. This will
help Excel pot the data the way you want it plotted.

To bin the results, put bin limits in another column:

40
50
60

down one row further than the number of bins you'll have. This formula in
the first cell of the next column will tell you the sum between the bin in
that row and the next higher one:

=SUMIF($C$34:$C$68,"<"&F35,$D$34:$D$68)-SUMIF($C$34:$C$68,"<"&F34,$D$34:$D$68)

where $C$34:$C$68 is your original range of numbers from 44 to whatever,
$D$34:$D$68 is the original range of counts, F34 is the first number in your
list of bins, and F35 is the second. Drag this down to fill to the next to
last item in your bin list.

- Jon

 

[BRob]

Oop - sorry.

Posted to wrong group

Rob

 

[Jon Peltier]

Why is this the wrong group? I assumed you wanted a histogram or similar
representation.

- Jon

 

[Shane Devenshire]

Hi Rob,

It's unclear to me if you have solved your problem or not. Let's assume
not:

1. You can attach the Analysis ToolPak add-in and then use the Tools, Data
Analysis, Histogram tool. (attach the add-in by choosing Tools, Add-in in
XL2003.
2. Or make an XY Scatter chart of your data using the chart wizard. A line
or column chart is not usually the correct choice.
3. The Histogram tool allows you to group the data in increments.
4. If you don't use the histogram tool you can use the FREQUENCY function
or an array formula to group the data.

Cheers,
Shane Devenshire