Histograms and Class Frequency (97)

Topic

New Reply

Hi Sue,

Just type 10, 20, 50 and 100 into a range of four cells, then select that range as the “Bin Range” in the Histogram dialog.

JIM

Reply | Quote

Replies

Edited by sue farrell on 27-Oct-01 10:07.

I am learning how to use the histogram analysis tool because I need it for an assignment. I can create class / ranges of equal width using the ‘bins’ but I cannot find a way to create the class / ranges of unequal width, eg. 0 – 10, 10 – 20, 20 – 50, 50 – 100.

The column widths on the chart need to be representative of the width of the range (’50-100′ being 40 units wider than ’10-20′ ) and hence the rectangle area equates to the frequency.

Thanks,

Reply | Quote

Jim

Thanks for the advice – realised I hadn’t explained my problem fully, so I have edited my query (see 2nd para). I need the base of each rectangle to equate to the width of the range. Hope you can still help…..

Cheers

Reply | Quote

Sue – the only way I can think of to do this is a kludge: graph your data as an X-Y chart with two points per data -point, being the frequency and the lower and upper bounds of the histogram bin or class. Setting the chart to include a line between the points will create what look like the top of a column chart. An example may make this clearer:

Bin      frequency
range
0-10       3
10-20      5
20-50      6
50-100     3

you would graph the following data points as an XY chart:

(0,3)    (10,3) series 1
(10,5)   (20,5) series 2
(20,6)   (50,6) series 3
(50,3)  (100,3) series 4

with the “lines” turned on for each data series. The result will be four horizontal lines on your chart. Ugly, but it works.

Secondly (but more fundamentally) you said that you wanted the area of the rectangles to be proportional to the frequency… The height can be, but not the area.

Reply | Quote

Hi Sue,

I think you would have to manually draw rectangles that represent the widths you want, because the only way to adjust the widths of the bars of a data series is to change th “Gap Width”, which affects all the bars uniformly.

With a VBA routine, such a routine would not be impossible. Is this something you need to do often, or is it a one-time deal? If you need to do it often, I would be willing to try to put something useful together for you.

Reply | Quote

Jim & Dean

Thanks for the help guys – I’ll give Dean’s suggestion a go.

This isn’t something I need to do alot of – it emerged from an assignment I’ve been set by a lecturer (who is a statistics expert) as part of my MA course. He avoided the subject when asked how he did it – I suspect he cheated!! If I find that he has a solution for this I’ll let you know. Meanwhile, unless I find that I desperately need a solution I wouldn’t want to trouble you, Jim, for a VBA routine.

This is something we are taught quite early on @ school in statistics – I’m surprised it cannot be done easily in Excel.

Cheers guys

Reply | Quote