Variable interval Histogram?

[jimbobjamiescott]

For my school project i need to produce histograms of a varied range
of estimates. I have attempted to produce the histogram using the
histogram tool yet i need to use this set of interval:

INTERVAL
40-70
70-100
100-120
120-130
130-140
140-150
150-170
170-200
200-250

i cannot enter this into the 'bin' obviousally and when i try to enter

40 , 70, 100 .... ....250
excel just gives me the ammounts for the averages that are exaclty
40 exactly 70 and so on.

does anyone know of how to solve this and a selection of formulas or
way of producing the correct histogram.

 

[Mike Middleton]

jimbobjamiescott -

Excel's Column chart type has equal-width bars, so the Column chart created
by the Histogram tool is appropriate only if the data ranges are of equal
width. That is, the bin values (inclusive upper limit of each interval) must
be equally spaced.

If you want to use arbitrary upper limits as bins, the frequency
distribution table created by the Histogram tool will still be accurate, but
the corresponding Column chart (histogram) will be a distorted view of the
data.

I have never seen a result where "excel just gives me the ammounts for the
averages that are exaclty 40 exactly 70 and so on." If you specify a column
of "bins" on your worksheet with the values 40, 70, 100, 120, ..., the first
frequency will be X<=40, the next frequency will be 40<X<=70, the next is
the count of values 70<X<=100, the next for 100<X<=120, etc.

Instead of using the Histogram tool, you can obtain a frequency distribution
table using the array-entered FREQUENCY worksheet function, multiple COUNTIF
worksheet functions, or a pivot table.

If you really need intervals of unequal width, you might be able to use
Stephen Bullen's technique described in FunChrt5.zip, available at
http://www.bmsltd.ie/Excel/Default.htm. "This chart shows how to create the
effect of having variable-width columns in your column chart."

If you can get along with intervals of equal width, you might want to try my
free Better Histogram add-in, available for download at
http://www.treeplan.com.

- Mike
http://www.mikemiddleton.com

 

[jimbobjamiescott]

jimbobjamiescott -

Excel's Column chart type has equal-width bars, so the Column chart created
by the Histogram tool is appropriate only if the data ranges are of equal
width. That is, the bin values (inclusive upper limit of each interval) must
be equally spaced.

If you want to use arbitrary upper limits as bins, the frequency
distribution table created by the Histogram tool will still be accurate, but
the corresponding Column chart (histogram) will be a distorted view of the
data.

I have never seen a result where "excel just gives me the ammounts for the
averages that are exaclty 40 exactly 70 and so on." If you specify a column
of "bins" on your worksheet with the values 40, 70, 100, 120, ..., the first
frequency will be X<=40, the next frequency will be 40<X<=70, the next is
the count of values 70<X<=100, the next for 100<X<=120, etc.

Instead of using the Histogram tool, you can obtain a frequency distribution
table using the array-entered FREQUENCY worksheet function, multiple COUNTIF
worksheet functions, or a pivot table.

If you really need intervals of unequal width, you might be able to use
Stephen Bullen's technique described in FunChrt5.zip, available athttp://www.bmsltd.ie/Excel/Default.htm. "This chart shows how to create the
effect of having variable-width columns in your column chart."

If you can get along with intervals of equal width, you might want to try my
free Better Histogram add-in, available for download athttp://www.treeplan.com.

- Mikehttp://www.mikemiddleton.com








- Show quoted text -

Thankyou i eventually used a COUNTIF function:

=COUNTIF(B6:C37,">=40")-COUNTIF(B6:C37,">70")

then i just created a normal graph
thats the calculations sorted now i just have to analise it all,
hmmmmm sounds like fun!
=D
=