Autoscale a Line Chart to stack the curves

hello!

To compare financial market assets moves I use a single chart with 2 series.

The chart has 2 vertical axes and the scales are pretty different in term of 
values.

The autoscale works fine on both axes.

My queston is:

How do I translate a curve onto the other so that they get "stacked", to be 
easily compared at a glance ?

Any VBA function or sub is welcome...

Thx
0
Patachoup (3)
4/19/2007 1:38:00 PM
excel.charting 18370 articles. 0 followers. Follow

5 Replies
433 Views

Similar Articles

[PageSpeed] 50

This post might help.  It shows how to calculate a proportional axis on a 
dual-axis chart.  The effect is to plot the movement of one series against 
another to see how well they are correlated.  Because each axis is calculated 
as proportional to the other, both series are viewed in the same plane i.e. 
no one series over or understates the visual effect of one against the other.

http://www.cellmatrix.net/index.php/proportional_axis_calculations

-- 
John Mansfield




"Patachoup" wrote:

> hello!
> 
> To compare financial market assets moves I use a single chart with 2 series.
> 
> The chart has 2 vertical axes and the scales are pretty different in term of 
> values.
> 
> The autoscale works fine on both axes.
> 
> My queston is:
> 
> How do I translate a curve onto the other so that they get "stacked", to be 
> easily compared at a glance ?
> 
> Any VBA function or sub is welcome...
> 
> Thx
0
4/19/2007 3:12:00 PM
Of course I can use proportionality ;-)

BUT I do not want to change the value of my currency! So no way to have an 
axis with wrong figures, even if I can still fiddle something and mimic 
displaying good figures on my chart! 

I am looking forward applying distorsion to the second axis so that the 
curves fit, without changing the values ;-)

Bloomberg HMS function does it very well, but the method is known to be a 
non linear algorithm...fussy isn't it ?!

Give me your mail and I'll send you a screenshot if you want ;-)

Would you dare taking up this challenge ? LoL

++

"John Mansfield" wrote:

> This post might help.  It shows how to calculate a proportional axis on a 
> dual-axis chart.  The effect is to plot the movement of one series against 
> another to see how well they are correlated.  Because each axis is calculated 
> as proportional to the other, both series are viewed in the same plane i.e. 
> no one series over or understates the visual effect of one against the other.
> 
> http://www.cellmatrix.net/index.php/proportional_axis_calculations
> 
> -- 
> John Mansfield
> 
> 
> 
> 
> "Patachoup" wrote:
> 
> > hello!
> > 
> > To compare financial market assets moves I use a single chart with 2 series.
> > 
> > The chart has 2 vertical axes and the scales are pretty different in term of 
> > values.
> > 
> > The autoscale works fine on both axes.
> > 
> > My queston is:
> > 
> > How do I translate a curve onto the other so that they get "stacked", to be 
> > easily compared at a glance ?
> > 
> > Any VBA function or sub is welcome...
> > 
> > Thx
0
Patachoup (3)
4/19/2007 3:54:05 PM
Patachoup:

Panel charts are one technique for showing stacked charts. this link gives 
you several examples. If you decide to go the panel chart route, let me know 
if you need help.

http://processtrends.com/toc_panel_charts.htm

Kelly

http://processtrends.com



"Patachoup" <Patachoup@discussions.microsoft.com> wrote in message 
news:BAC3E776-0A71-4C5D-BF35-DA4211B1028A@microsoft.com...
> hello!
>
> To compare financial market assets moves I use a single chart with 2 
> series.
>
> The chart has 2 vertical axes and the scales are pretty different in term 
> of
> values.
>
> The autoscale works fine on both axes.
>
> My queston is:
>
> How do I translate a curve onto the other so that they get "stacked", to 
> be
> easily compared at a glance ?
>
> Any VBA function or sub is welcome...
>
> Thx 


0
dkod (205)
4/19/2007 5:31:26 PM
Hi,

Indeed panel charts let us display stacked series onto a single chart, nice 
technique I knew as well but that does not fit my needs! ;-)

Look at this picture and you will see what I mean:

[URL=http://img219.imageshack.us/my.php?image=eurofuturesav6.gif][IMG]http://img219.imageshack.us/img219/5291/eurofuturesav6.th.gif[/IMG][/URL]

[IMG]http://img219.imageshack.us/img219/5291/eurofuturesav6.gif[/IMG]


"Kelly O'Day" wrote:

> Patachoup:
> 
> Panel charts are one technique for showing stacked charts. this link gives 
> you several examples. If you decide to go the panel chart route, let me know 
> if you need help.
> 
> http://processtrends.com/toc_panel_charts.htm
> 
> Kelly
> 
> http://processtrends.com
> 
> 
> 
> "Patachoup" <Patachoup@discussions.microsoft.com> wrote in message 
> news:BAC3E776-0A71-4C5D-BF35-DA4211B1028A@microsoft.com...
> > hello!
> >
> > To compare financial market assets moves I use a single chart with 2 
> > series.
> >
> > The chart has 2 vertical axes and the scales are pretty different in term 
> > of
> > values.
> >
> > The autoscale works fine on both axes.
> >
> > My queston is:
> >
> > How do I translate a curve onto the other so that they get "stacked", to 
> > be
> > easily compared at a glance ?
> >
> > Any VBA function or sub is welcome...
> >
> > Thx 
> 
> 
> 
0
Patachoup (3)
4/20/2007 7:54:02 AM
Patachoup:

Have you considered a custom Y Axis (aka dummy axis)?

This link will get you to a tutorial on custom axes. 
http://processtrends.com/pg_charts_custom_axis.htm

Kelly O'Day

http://processtrends.com



"Patachoup" <Patachoup@discussions.microsoft.com> wrote in message 
news:EA6BAB23-F4F1-4DD5-BF76-24B1BBDE941F@microsoft.com...
> Hi,
>
> Indeed panel charts let us display stacked series onto a single chart, 
> nice
> technique I knew as well but that does not fit my needs! ;-)
>
> Look at this picture and you will see what I mean:
>
> [URL=http://img219.imageshack.us/my.php?image=eurofuturesav6.gif][IMG]http://img219.imageshack.us/img219/5291/eurofuturesav6.th.gif[/IMG][/URL]
>
> [IMG]http://img219.imageshack.us/img219/5291/eurofuturesav6.gif[/IMG]
>
>
> "Kelly O'Day" wrote:
>
>> Patachoup:
>>
>> Panel charts are one technique for showing stacked charts. this link 
>> gives
>> you several examples. If you decide to go the panel chart route, let me 
>> know
>> if you need help.
>>
>> http://processtrends.com/toc_panel_charts.htm
>>
>> Kelly
>>
>> http://processtrends.com
>>
>>
>>
>> "Patachoup" <Patachoup@discussions.microsoft.com> wrote in message
>> news:BAC3E776-0A71-4C5D-BF35-DA4211B1028A@microsoft.com...
>> > hello!
>> >
>> > To compare financial market assets moves I use a single chart with 2
>> > series.
>> >
>> > The chart has 2 vertical axes and the scales are pretty different in 
>> > term
>> > of
>> > values.
>> >
>> > The autoscale works fine on both axes.
>> >
>> > My queston is:
>> >
>> > How do I translate a curve onto the other so that they get "stacked", 
>> > to
>> > be
>> > easily compared at a glance ?
>> >
>> > Any VBA function or sub is welcome...
>> >
>> > Thx
>>
>>
>> 


0
dkod (205)
4/20/2007 12:26:51 PM
Reply:

Similar Artilces:

show another column when hovering on a point in a chart
Is it possible to show another column when you hover on a point? I have 3 columns, A, B, and C for a scatter plot I have B and C as x and y coordinates of a point, and I'd like to have Excel show A when I hover on the point. thanks, Wei ...

Has Anyone ever seen a chart like this?
Looking for some help. I need to know what this column chart is called? Or how to make it? 'CLick Here to View Chart' (http://www.f150online.com/galleries/pictureview.cfm?pnum=163585&anum=11244) I can't find any option in Excel to make this chart. Maybe it was not made in Excel? Or I need a specific plugin? -- TsunamiBob ------------------------------------------------------------------------ TsunamiBob's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=36202 View this thread: http://www.excelforum.com/showthread.php?threadid=559874 Hi, It...

charting newdata
Formula or VBA? I am trying to create a formula to assign a region to populate a chart. I know there is a way to use the OFFSET function to point ot a certain section of a data sheet, but what if i insert a line between the title and the data.(done programmactically)? the problem is that inserted lines do not automatically include into the data range for a chart. The data wants to be sorted from newest to oldest. Would it be easier to create a formula to assign a 'chart data area' or simply reassign the data area every time the table is updated Thanks for the input. ...

chart label reference based on the column number
In a worksheet with an embedded chart, I have a cell, S4, where I enter the number of the column I want to chart (these numbers are listed as labels in cells A2:R2). I can't figure out how to translate the number in S4 into the corresponding column letter. For example, if S4 contains "3", the chart title should be =$C$2. I think working with absolute references, R1C1, might be easier here, but somehow what I've tried, didn't work (the entire spreadsheet is based on relative addresses (A1). z.entropic in a cell (Z100) put =index(A2:G2,1,S4,1) Change G2 to whatev...

DEADLINE... PLEASE HELP! Stacked Bar chart?
I'm not even sure how to ask the question so here's what I have... 2003 2004 2005 Actual/Goal Actual/Goal Actual/Goal Me 1009/1061 591/866 658/897 Comp. A 966/1012 633/811 624/808 Comp. B 699/744 450/593 480/607 Comp. C 957/1005 642/821 665/838 I wanto to show a bar for each competitor, for each year, so there will be 4 bars for each year. Each bar showing Actual performance & Performance Goal...

Core Chart
Need a picture of an opened sphere showing core and layers. Can you help? After managing to set up OE-QuoteFix on his new PC, Ed reads a message from Isabel <ialduen@catholiccharitiesden.com>... > Need a picture of an opened sphere showing core and layers. a) The core of what? A black hole? An apple? A nuclear power plant? b) This is a Microsoft Publisher group. Your question is hardly relavent to the topic of discussion really. Why did you choose this group? -- Ed Bennett - MVP Microsoft Publisher http://www.mvps.org/the_nerd/ Before reading this message, view the disclaimer...

z-order of various lines and bars in an Excel 2007 chart
What is the procedure for changing the z-order of various lines and bars in an Excel 2007 chart, such as drop lines, high-low lines, up/ down bars, and error bars? I observed that when I add drop lines to an Excel 2007 chart, they appear in front of the underlying data -- in my case, the underlying data is displayed as a plotted area. I want the plotted area to be fully visible with no lines over it. I would like to send the drop lines to the back and keep the plotted area in the front, but there is no option to do this. So, is there a procedure for adjusting the z-order of drop...

Hierarchy Pyramid Chart
Is there a way (Excel 02), to create a chart that is in the shape of a pyramid/triange (i.e. like a pie graph, but in a triangle instead)??? It doesn't seem to be an option, but maybe some type of addin??? Thanks in advance Nevermind - I realized that the pyramid is actually a diagram. So I inserted a Diagram - Organizational Chart instead and it's just what I needed!!! "Keep It Simple Stupid" wrote: > Is there a way (Excel 02), to create a chart that is in the shape of a > pyramid/triange (i.e. like a pie graph, but in a triangle instead)??? > It doesn't ...

How do I break URL address over two lines?
I am trying to enter website addresses into footnotes and Word automatically forces the footnote onto a separate line. I need to conform to certain aditing requirements and want the url to start right after the citation and continue on to however many lines are necessary, but not leave a gap because it starts the url on a separate line in the footnote. Help please. In the edit hyperlink dialog, put the URI in both the Address and Text-to- display boxes. That way you can use a space or linebreak to wrap the display text to the next line without affecting the hyperlink ability. Hyp...

Only one line in Money invoice
I have been using Money 2007 Home & Business to create and print invoices. This program has run with no problems for some time. However, just recently, it will only allow one item to print on the invoice. I can enter more than one item, and the invoice entry will show the additional items, but when printed only one item will show up. I have tried backing up my Money file to a spare disk, deleting the program and the associated mny and mbf files from the computer, reinstalling the program and reloading the backup. The same fault immediately occurs. Strangely, I have now installed the sam...

How do I lock a chart so it will not update?
That's the question. I have my data in Excel and the chart in Excel but not all the data cells are used. Everytimg I open the chart it wants to update and I want it to stay the same. Any ideas on how to lock the chart? Hi Just a few ideas: You could lock the cells that are shown in the chart. Or you could copy the cells and paste as values (assuming formulas were used that update when other cells change). -- Wigi http://www.wimgielis.be = Excel/VBA, soccer and music "Locking a Chart" wrote: > That's the question. I have my data in Excel and the chart in ...

How can I reset the default pie chart label "Other" to "Equity"?
I show Cash, Equities, and Fixed Income in the large pie to the left. The equity slice is further split into large, medium, and small. The problem is that the Equity slice gets relabeled as "Other". When I manuallly change it back to Equity, I lose the ability to link to any new data...so I can't use the pie chart as a template that would allow me to put new data in the table and have the pie chart adjust automaticially. How can I reset the default pie chart label to "Equity"? or is there another solution. Instead of making pie charts (which are notor...

what is straight line depreciation and double line depreciation?
See http://en.wikipedia.org/wiki/Depreciation In article <928DC52C-D390-45C6-9847-693648A1A5DA@microsoft.com>, MISSY <MISSY@discussions.microsoft.com> wrote: "JE McGimpsey" <jemcgimpsey@mvps.org> wrote in message news:jemcgimpsey-F62312.22355624092007@msnews.microsoft.com... > See > > http://en.wikipedia.org/wiki/Depreciation > Quite a good article for wikipedia, but it misses one very important point. The original purpose of charging depreciation to the P&L was to build up a reserve in order to replace the asset when it became obsol...

Open Continuous form with a few lines showing
Hi I have successfully implemented a continuous form to open at a new line with RunCommand acCmdRecordsGoToNew in the Load Event However, you have to manually scroll a little to see a few of the previous records. It would be nice to see up to 5 of these previous lines on Opening the form Is there a way to modify this code to do that? Thanks Neil Change the form's underlying record source to a Top 5 query. In a query's design view, right click in a blank area, and choose properties. Set the Top Values to 5, then adjust your criteria and order by clause to ret...

Bug in Grid Lines in CListCtrl Report View
Hi, This thing is again annoying me! whenever I display the grid lines using LVS_EX_GRIDLINES , there are problems when scrolling the CListCtrl at once , the grid lines seem to loose their position and thus generating a blurred view or sometimes just disappear temporarily. Have anyone else faced the same thing ? Thanks >whenever I display the grid lines using LVS_EX_GRIDLINES , there are >problems when scrolling the CListCtrl at once , the grid lines seem to loose >their position and thus generating a blurred view or sometimes just disappear > temporarily...

Chart Wizard #5
Does excel 2007 have a chart wizard? If so, how do I access it? The chart wizard no longer exists in Excel 2007, RIP. You create a chart using the various chart buttons on the Insert tab, then struggle through the three Chart Tools contextual tabs looking for the controls to set up the chart. - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ "Liberty" <Liberty @discussions.microsoft.com> wrote in message news:7C1186EF-4C15-4E3D-A642-D0EA334C1192@microsoft.com... > Does excel...

Easy dynamic chart.
Creating a dynamic chart is difficult, there should be a dynamic chart wizard that will create it with you. Also if my serie value is a name range, it would be nice to just right the Name range directly not with the complex formula. ='xyz.xlsx'!name range but just name range. xyz and name range is a fictive value. ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, follow this link to open the...

Custumize Chart Tittle Description
How can I make the Chart Tittle to be equal to a specific cell located in one sheet. For example, I have a description on the cell "D10" located in the sheetname "Black" . I want this description to be the Chart Tittle because is a number that will change in the daily basic. I have been using this formula "=Black!D10"; however, is not working. Thanks in advance. Maperalia Click on the title, type =, and then click on D10 on Black. Or click on the title, and type =Black!$D$10 -- Rob Powered by Creative Laziness "Maperalia" wrote: > How can I...

My charts do not show when moved as a separate chart tab...
In excel 2007 my charts are displayed when inserted in a a sheet but they do not show when moved as separate tabs ...

Scatterplot with a best-fit line
How do i create one scatterplot with three separate data sets and a best-fit line to go with each set? Make an XY chart with the first set of X-Y values. Copy the second set of X-Y values, select the chart and use Edit menu > Paste Special, and add the data as a new series, categories in first column (row). Repeat for the third set of X-Y values. Add the trendline to each series by selecting the series, right clicking, and choosing Add Trendline. - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions http://PeltierTech.com _______ "neally" <neall...

Creating a chart with varying data value ranges
I have a chart that I want to show home price data with. The chart will have data that has very significantly varying data values. For example, one line graph will be 50-100, one will be 400,000 to 500,000, one will be 90% to 110%. I would like to show these line graphs on one chart. How to I make a chart with differing ranges on the x (vertical) axis? Take a bit of seutp work, but the end result here is impressive: http://peltiertech.com/Excel/ChartsHowTo/PanelUnevenScales.html BTW, the vertical axis is the y-axis. -- Best Regards, Luke M "Anthony Blackburn&quo...

revision lines on Word
Version: 2008 Operating System: Mac OS X 10.6 (Snow Leopard) Processor: Intel I need to show when text is revised by inserting a line in the left margin. This works ok on text, and has worked with tables and ToC in past versions (2004 for Mac). Now in the new version it causes formatting chaos. <br> I draw a line in the left margin of a page with a table and when the mouse is released the line a) disappears, or b) jumps up to the previous page, or c) jumps over into the table and displaces the contents downward off the page. <br> I am not using the Track Changes feature. &l...

Copy & Paste Chart
I can Copy & Paste an Excel Chart from one Workbook to another. However, another User, when copying an Excel chart, and Pasting into another Workbook, gets the Chart essentially as a "Snapshot" with "EMBED". Ideas??? TIA - Bob What's your question? You don't say what you're looking for. What does the formula bar say in parentheses after "EMBED"? You can copy the chart as a picture: hold Shift while selecting the Edit menu, choose Copy Picture, and choose the On Screen and Picture options. - Jon ------- Jon Peltier, Microsoft Excel MVP Tutor...

How do you change data values by dragging graph lines in excel 07?
Firstly, please ask your question in the body of the message, not the subject line. Secondly, you don't in Excel 2007; that was one of the "improvements" they put in. Most experts prefer the charting facilities in Excel 2003 over those in Excel 2007. -- David Biddulph "Mannymeister" <Mannymeister@discussions.microsoft.com> wrote in message news:3616E56A-A8BF-44D4-9E38-8746B39DCD7E@microsoft.com... > ...

Bubble Charting
Hi There Hope you all can help, i've looked everywhere but have found it very difficult to achieve what I want. I'm not familiar with VB but i know how to copy and paste into modules! Here is the data (column by column): Name --- X --- Y --- Bubble Size --- RAG Status OK, here are my requirements... - Each bubble to be labeled with column name - RAG status to conditionally format the colour of the bubble. Thanks in advance! Hi, You can do the colour coding by using multiple series and some simple formula to create conditional charts, this will give you more detail on the subj...