Posted: 4/4/2014 8:50:53 PM EDT
|
Hello all,
I am trying to make a line chart about yield curves on different securities between their yields at different maturities. US treasuries in 2,5,10,30 year maturities A rated muni's in 2,5,10,20 year maturities A rated corp. bonds in 2,5,10,20 year maturities TIPS in 2,5,10,30 year maturities This is how I have set up the data in excel 2 year 5 year 10 year 20 year 30 year us t a rated muni a rated corp TIPS My problem is that the A rated stuff only has a max maturity of 20 years and the US T and TIPS have 30 year max maturity. My question is, what do I put in the empty cells so the graph will come out correct and keep the labeling of the horizontal axis with both 20 and 30 years? When I do it, the 30 year changes to 20 year, or if I input 0 the line plunges to the bottom at the maturity. I have also tried making separate graphs and copy pasting the line into another graph, and when I do that, the horizontal axis label is changed from 30 to 20 years....LOL...Help... |
|
If I am understanding you correctly, you want to do a dual axis graph.
I don't have a computer in front of me....if you have 2010, it's just a checkbox to move one of the data sets to a secondary axis...then you can set the y-axis scale independently to whatever you like. If you hit google and search for 'excel secondary axis' you should find a ton of examples...if that matches what you are trying to do, give it a go. Forgive me if I am missing your need entirely.....I've been up for almost 24 hours 
|
|
I appreciate the advice, and I will try that right now. I am going to try and use a scatter graph as well, and see if I can get that to work. it shouldn't be this difficult, and even pulled out my "learn excel expert skills" book, and can't seem to find an answer.
Will check back in the morning, thanks again. |
|
interesting problem--excel auto-formatting can be annoying. seems like an easy fix would be to define the empty cells as equal to the midpoint between 10 year and 30 year for that series[cell value = (30y-10y)/2]. if your chart is going to be linear between the points instead of fitting a curve, then interpolating the mean would be mathematically valid. IOW, it's a point that would be drawn through anyway, so you're not distorting your data at all. but if you're doing anything more complex, like fitting an actual curve (representing a decay between points) or doing any kind of regression, then you can't use this method validly. the graphical issue would be that you actually have a point representing an actual value for 20 years, which isn't really kosher when it comes to using figures to represent data. in SPSS, you can edit chart properties and simply delete the point itself without messing with the line, but i'm not sure if this is possible in excel. good luck man--let us know if you come up with a fix.
|
|
I figured it out, I just had to select the chart, right click and select "select data". hit the hidden and empty cells button, and then it asks what you want to do with empty cells. It gives you the options: gaps, zero, or connect data points with a line. It was set for zero, so that is why my curves plunged to zero at the 20 year point.
Now I have my horizontal axis listed correctly, and all my curves correct. YEY!!! Thanks a lot for responding so quickly, I had been working on it for quite a while, and I started to panic, and ran to good ole GD for help... Thanks again. |
|
Quoted: I figured it out, I just had to select the chart, right click and select "select data". hit the hidden and empty cells button, and then it asks what you want to do with empty cells. It gives you the options: gaps, zero, or connect data points with a line. It was set for zero, so that is why my curves plunged to zero at the 20 year point. oh, fine...do it the easy way! back in my day, we had to do our excel spreadsheets with flint, steel, and bird intestines. they hadn't even invented math yet--we just input prayers and incantations, and the results of functions came back as mystical runes and strange weather. i'll never forget the virgins we had to sacrifice in the name of pie charts...
|
|
Quoted:
oh, fine...do it the easy way! back in my day, we had to do our excel spreadsheets with flint, steel, and bird intestines. they hadn't even invented math yet--we just input prayers and incantations, and the results of functions came back as mystical runes and strange weather. i'll never forget the virgins we had to sacrifice in the name of pie charts... Quoted:
Quoted:
I figured it out, I just had to select the chart, right click and select "select data". hit the hidden and empty cells button, and then it asks what you want to do with empty cells. It gives you the options: gaps, zero, or connect data points with a line. It was set for zero, so that is why my curves plunged to zero at the 20 year point. oh, fine...do it the easy way! back in my day, we had to do our excel spreadsheets with flint, steel, and bird intestines. they hadn't even invented math yet--we just input prayers and incantations, and the results of functions came back as mystical runes and strange weather. i'll never forget the virgins we had to sacrifice in the name of pie charts... I have no virgins 
|
|
Don't know if this is something you need to do on a frequent basis, but if you are doing a lot of graphs (or more specifically, statistics or correlations) look into minitab. It's not a cheap program, but it blows excel away as far as plotting data and statistically analysis. You can download a 30 day free trial from their website.....I'd recommend getting your data sets formatted the way you want in excel, then dragging and dropping them into minitab...then under the 'graph' menu pick some options.
Enjoy 
|