On Aug 12, 3:22 pm, Del Cotter wrote:
On Sat, 11 Aug 2007, in microsoft.public.excel.charting,
Chris S said:

On Aug 11, 3:56 pm, Del Cotter wrote:
have you considered building your histogram from an Area
Chart that uses a date scale, and your vertical lines from a Line Chart
that uses the same date scale? That feels like it could work. You just
have to be careful to create your histogram bars out of rectangular
areas,
Thanks Del, I will try your date series suggestion, if not, VBA here I
come!

Try this

http://www.branta.demon.co.uk/excel/histogram.xls

The vertical lines had to be scatter series, as they can't share the
same x-series as the histogram. But luckily that doesn't stop them using
the primary x axis, and as a bonus they are even positioned with decimal
accuracy, which I didn't know could happen with Time-scales. That means
it's much less necessary to mess with the Time-scale, which, in Line and
Area charts, only works for x values that are integers between zero and
a bit shy of three million (but see below about your bins)

(Why three million? It's 31 Dec 9999, in number of days from 1 Jan 1900.
If only Excel dates started from 1 Jan AD 1-- I had terrible trouble
with a chart of American History dates recently, because they were in
the 1700s and 1800s!)

I used a Line Chart series to make the histogram bars, so they're empty,
but if you want to fill them, it's easy to change the Chart Type of that
series to Area, so it looks exactly like columns of a bar chart.

I have a few questions:

Do you intend the bins to always be the same size, e.g. 5? If not, you
may still have to use VBA to alter the major unit in the scales.

Do you intend the bins to ever be non-integer or extend into negative
values? Both of these will be much harder to do with a Time-scale.

--
Del Cotter
NB Personal replies to this post will send email to ,
which goes to a spam folder-- please send your email to del3 instead.


Del thanks again for this. I ended up using a hybrid of a 10 line vba
code snippet + an imporved and simplified "fake histogram with a
scatter chart" method that you suggested (i.e. ALL series are now
scatters, and therefore I avoid havinf differently scaled axes - the
VBA the goes and rescales ALL x-axes in my sheet so that the charted
data alwasy covers 100% of the x-axis).

Works great. Now, if I could only figure out how to shade those fake
columns...