[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Overlay histogram on line chart

Posted on 2011-04-18
8
Medium Priority
?
1,008 Views
Last Modified: 2012-05-11
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?
0
Comment
Question by:sjgrey
  • 3
  • 3
  • 2
8 Comments
 
LVL 1

Author Comment

by:sjgrey
ID: 35421562
Forgot to attach the example

Here it is

Histogram-plot.xlsx
0
 
LVL 50
ID: 35421723
Hi,

when I open your file with Excel 2010, this is the chart I see:

 chart
If this is not what you see with 2007, then it's a matter of Excel version. Have you installed the latest service packs?

Excel 2007 is very buggy, especially with regards to charting.

cheers, teylyn
0
 
LVL 1

Author Comment

by:sjgrey
ID: 35421748
What I see is as in the attached.  Sounds like an Excel issue.  I have SP2 installed.

Is Excel 2010 any better?

Histogram-plot.pdf
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 85

Expert Comment

by:Rory Archibald
ID: 35422057
This is what I see in 2007 SP2. Perhaps it's a printer driver issue?
chart.jpg
0
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 35422071
Ignore that - I'm clearly not awake yet!
0
 
LVL 85

Accepted Solution

by:
Rory Archibald earned 1000 total points
ID: 35422101
This is the best I can do in 2007 - it uses an additional X axis set up as a timescale.
Histogram-plot.xlsx
0
 
LVL 50

Assisted Solution

by:Ingeborg Hawighorst (Microsoft MVP / EE MVE)
Ingeborg Hawighorst (Microsoft MVP / EE MVE) earned 1000 total points
ID: 35423260
Strange. This should also work in 2007.

>> Is Excel 2010 any better?

Yes!
0
 
LVL 1

Author Closing Comment

by:sjgrey
ID: 35423456
Thanks to both respondents.  I woin't waste my time trying to make Excel do it the easy way but I have a work around if I need it.

I think I'll look into upgrading to 2010
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

After seeing numerous questions for Dynamic Data Validation I notice that most have used Visual Basic to solve the problem. This suggestion is purely formula based and can be used in multiple rows.
If you need to forecast numbers -- typically for finance -- the Windows and Mac versions of Excel 2016 have a basket of tools to get the job done.
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.

873 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question