moving x-axis labels in histogram

  • Thread starter Thread starter nycexcelmonkey
  • Start date Start date
N

nycexcelmonkey

I have created a chart using pre-defined bin ranges using the histogra
function under data analysis. Unfortunately, the bin label is place
beneath each bar on the graph. In actuality the bin range number
(-6%, -4%, -2%, 0%, 2%, 4%, 6%) should appear IN BETWEEN the bars!
Otherwise, the graph is confusing to read. For example, say that th
bar over the 2% bin has a value of 24. Does this mean there were 2
instances of between 0-2% or between 2-4%? I know it is the former
but it is not clear to someone not familar w/excel. HOW CAN ON
REPOSITION THE X-AXIS LABELS TO APPEAR BETWEEN THE BARS IN A HISTOGRA
GRAPH?!?!?!?!

Thanks
 
nycexcelmonkey -
I have created a chart using pre-defined bin ranges using the histogram
function under data analysis. Unfortunately, the bin label is placed
beneath each bar on the graph. In actuality the bin range numbers
(-6%, -4%, -2%, 0%, 2%, 4%, 6%) should appear IN BETWEEN the bars!
Otherwise, the graph is confusing to read. For example, say that the bar
over the 2% bin has a value of 24. Does this mean there were 24 instances
of between 0-2% or between 2-4%? I know it is the former, but it is not
clear to someone not familar w/excel. HOW CAN ONE REPOSITION THE X-AXIS
LABELS TO APPEAR BETWEEN THE BARS IN A HISTOGRAM GRAPH?!?!?!?! <
Click to expand...

The Histogram tool creates an Excel Column chart type, and it is not
possible to align the x-axis labels under the tick marks between each
vertical bar. One workaround is to use an x-axis title "Upper limit of each
range" or something similar. A much-more-involved workaround uses a
combination chart: vertical bars of the Column chart and x-axis numerical
values of an XY (Scatter) chart.

- Mike

www.mikemiddleton.com
 
Alternatively, use a custom number format like "<"0%, so the X axis labels look like
<-2%, <0%, <2%, etc. Or use worksheet formulas like
=TEXT(A1,"0%")&" to "&TEXT(A2,"0%")
to produce a set of labels like "-6% to -4%", "0% to 2%", etc.

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