Excel analysis should do proper histograms inc. bars touching

[Guest]

Excel produces a wide range of really useful graphs, with the exception of
histograms (and box-and-whiskers are also difficult to do), even in the 2003
version that I use.

The Analysis ToolPak produces an apology for a histogram with bars not
touching and frequency density not used on the y - axis ("schoolboy error").

I am sure microsoft could easily rectify this if they wanted - there are
other programs (e.g. Autograph) that do it quite successfully.



----------------
This post is a suggestion for Microsoft, and Microsoft responds to the
suggestions with the most votes. To vote for this suggestion, click the "I
Agree" button in the message pane. If you do not see the button, follow this
link to open the suggestion in the Microsoft Web-based Newsreader and then
click "I Agree" in the message pane.

http://www.microsoft.com/office/com...1e437abd23&dg=microsoft.public.excel.charting

 

[Mike Middleton]

Numskull -

If you want vertical bars of a Column chart to touch, set the Gap Width to
zero.

Or, try my free Better Histogram add-in from www.treeplan.com.

- Mike
www.mikemiddleton.com

 

[Guest]

Thanks for the suggestion, Mike. However, I am looking to do histograms with
variable width classes and need to use frequency density rather than
frequency on the vertical axes.

Following a hint from Umberto Barreto and Frank Howland of Wabash College
who have also produced an Excel add-in I have managed to work out how to do
the graph using a scatter diagram joined by straight lines. I am sure it
would be possible to write a macro to do the calculations also.

Here is the method:

1. Work out the limits and frequency densities for your data (f.d. = freq ÷
class interval)

2. Enter the first point as coordinates in adjacent cells with x = lower
limit, y = 0
The second point is x = lower limit, y = first freq density

3. The next three points are: x = 2nd limit, y = first freq density, then x
= 2nd limit, y = 0 and x = 2nd limit, y = 2nd freq density

4. Keep repeating step 3 for each data group until you get to the last
group, then finish with x = last limit, y = last freq density and finally x =
last limit, y = 0

5. Now highlight the two columns (or rows), and create a scatter diagram
with the points joined by straight lines.

6. Note that it is essential to do the points in the given order.

7. Unfortunately you cannot colour in the “bars†that appear to have been
created, but it’s worth thickening up the lines a bit.

NB Missing out the interior zero values will give the outline of the graph
without the "bars"

 

[Jon Peltier]

You can use a variation on this technique to get variable width colored
bars:

http://pubs.logicalexpressions.com/Pub0009/LPMArticle.asp?ID=508

Use just a single series, not stacked to 100% full scale.

- Jon