Creating a Simple Bar Chart - 2 Areas for help please

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

BRob - 28 Apr 2008 10:21 GMT

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

Reply to this Message

Jon Peltier - 28 Apr 2008 23:14 GMT

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
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
_______

> 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
[quoted text clipped - 27 lines]
> 54 5
> 55

Reply to this Message

BRob - 01 May 2008 08:43 GMT

Oop - sorry.

Posted to wrong group

Rob

> 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
[quoted text clipped - 27 lines]
> 54 5
> 55

Reply to this Message

Jon Peltier - 01 May 2008 13:32 GMT

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

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
_______

> Oop - sorry.
>
[quoted text clipped - 32 lines]
>> 54 5
>> 55

Reply to this Message

Shane Devenshire - 18 May 2008 02:42 GMT

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

> Why is this the wrong group? I assumed you wanted a histogram or similar
> representation.
[quoted text clipped - 42 lines]
>>> 54 5
>>> 55

Reply to this Message