Problems with "hyster-grams" :-)

[[email protected]]

I some problems using histograms in Office Excel 2003
(11.6355.6360) SP1.

The one problem I cannot seem to work around is: the
X-axis data "labels" are centered under the bar. I want
the "labels" to be centered under the right-edge of the
bar.

(I edit change Overlap and Gap Width in Format Data Series /
Options so that I have "fat" bars that touch each other.)

The fact is: the X-axis "labels" are not labels at all.
They are, in fact, the max value of the bin. ("Well, of
course!") I want them to appear where the max value
belongs, namely the right edge. That is the format that
we are using in class.

In fact, I want the X-axis values to be centered under
the right edge, just as they are in Excel scatter plots.

How can do that with histograms (actually "clustered
column" charts)?

The other problem I have worked around. But if there
is a simpler solution, I'd appreciate hearing about it.

When I enter the Input Range in the Histogram tool, I
am not able to enter the ranges by name. If I type the
name in the field, I get an error. When I have the
dialog box for the field open, I cannot type into the
name field (upper-left of the worksheet), as we normally
can do to select a named range.

Of course, I can simply drag the cursor to highlight
the range. But that is a pain to do when the range is
several hundred rows -- which is why I assigned a name
to it in the first place.

Is there some way to enter the Input Range by name?

[Jon Peltier]

Mike Middleton shows how to make Better Histograms:

http://www.treeplan.com/better.htm

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


[email protected] wrote:

> I some problems using histograms in Office Excel 2003
> (11.6355.6360) SP1.
>
> The one problem I cannot seem to work around is: the
> X-axis data "labels" are centered under the bar. I want
> the "labels" to be centered under the right-edge of the
> bar.
>
> (I edit change Overlap and Gap Width in Format Data Series /
> Options so that I have "fat" bars that touch each other.)
>
> The fact is: the X-axis "labels" are not labels at all.
> They are, in fact, the max value of the bin. ("Well, of
> course!") I want them to appear where the max value
> belongs, namely the right edge. That is the format that
> we are using in class.
>
> In fact, I want the X-axis values to be centered under
> the right edge, just as they are in Excel scatter plots.
>
> How can do that with histograms (actually "clustered
> column" charts)?
>
> The other problem I have worked around. But if there
> is a simpler solution, I'd appreciate hearing about it.
>
> When I enter the Input Range in the Histogram tool, I
> am not able to enter the ranges by name. If I type the
> name in the field, I get an error. When I have the
> dialog box for the field open, I cannot type into the
> name field (upper-left of the worksheet), as we normally
> can do to select a named range.
>
> Of course, I can simply drag the cursor to highlight
> the range. But that is a pain to do when the range is
> several hundred rows -- which is why I assigned a name
> to it in the first place.
>
> Is there some way to enter the Input Range by name?
>

[[email protected]]

Jon Peltier wrote:
> Mike Middleton shows how to make Better Histograms:
> http://www.treeplan.com/better.htm

Good pointer. Thanks.

[[email protected]]

Jon Peltier wrote:
> Mike Middleton shows how to make Better Histograms:
> http://www.treeplan.com/better.htm

Although the instructions are well-written, they do not
seem to work with Office Excel 2003 (11.6355.6360) SP1.

I can work around one problem (Paste Special is no
longer permitted when the chart is selected -- I use
the custom chart type "line - column on 2 axes").

But when I perform the actions that are intended to
move the numeric axis from vertical to horizontal,
Excel insists on treating the numeric axis as categories,
centering the numeric labels again.