Overlay histogram on line chart
Posted on 2011-04-18
Please see attached example. I'm using XL 2007.
I have a simulation package producing output charts showing the risk of exceeding a budget set somewhere in the range of realistically likely outcomes - the black curve in the chart. This uses data from columns N and O.
I need to overlay a histogram that shows the proportion of the simulation outputs that fell at each point along the X-axis and I've created a set of X and Y values in columns R and U. Each value in column R is the centre of a bar in the histogram and the corresponding value in column U is the height of the bar.
I added the histogram data to the graph and it came in as a line graph (XY scatter) at the correct locations on the chart. However, when I changed its type to a vertical bar chart and it all collapsed to the left hand edge of the chart.
I tried setting up a second vertical axis for the histogram just in case that might make a difference but it didn't. (The second axis is invisible as I set it not to show tick marks or labels.)
What I want is the black line to remain where it is and the histogram bars to fall where their X values in column R are set.
I also want to be able to do this again with other models, not just fix up this example.
Is this possible?