Area chart fill above line

[DashboardNovice]

I am plotting column E (stock price) as an area chart, but I want the shading to apply above the stock price. I tried adding this to a secondary axis and reversing the axis order but that didn't work.

Any suggestions?

 

[vletm]

DashboardNovice​

I am plotting column E (stock price) as an area chart, but I want the shading to apply above the stock price.
You want to have shading above own value ... hmm?
... You stock values are 'fixed' 500.
Could You try to explain one more time ... what is Your idea?
... could You do manually ... what do You would like to get?

 

[DashboardNovice]

DashboardNovice​

I am plotting column E (stock price) as an area chart, but I want the shading to apply above the stock price.
You want to have shading above own value ... hmm?
... You stock values are 'fixed' 500.
Could You try to explain one more time ... what is Your idea?
... could You do manually ... what do You would like to get?

1. The stock value is 500. Instead of shading below 500, I want to shade above 500. This attached pic shows what I was hoping to achieve. I used a green background in this pic. So just shading above 500, instead of below 500.

2. Another problem is that I would like to expand the lower and upper values of the Y-axis. Under the Format Axis window, there are fields called Minimum and Maximum. Is it possible to somehow link these fields to a cell?

3. If the answer to #2 is no, is there anyway to automatically change the upper and lower values of the Y-axis without changing the values in column B?

 

[vletm]

Something like this ...?

 

[DashboardNovice]

Something like this ...?

Yes, that's what I was trying to do. I studied your spreadsheet. I have some questions.

1. You are plotting the data series 'Stock' as a stacked area chart with 'no fill' for the background. Then you are plotting the data series 'Shade' as a stacked area chart with a value of 50. It looks like the 50 refers to 50 above 500, i.e. 500 to 550, but I don't see how you got the data series "Shade" to shade above the 500 mark. I replicated what you did and when I plotted 'Shade' as a stacked area chart, the shading filled below 500, instead of above 500.

2. I took a look at the VBA code behind the button you added for the Y-axis scale. Suppose I want to set this up so that after somebody pastes in a new set of values in columns B:G, that will trigger a macro to run which will redefine the scale of the Y-axis values based on the new set of values pasted into column B. Instead of pressing a button to set the Y-axis, I would like for the axis to rescale automatically when the user pastes in a new set of data. I'm sure there is a way to do this. Wouldn't this involve using an event called change or something like that? The change should trigger recalculation of the Y-axis, only when new data is pasted into column B, and not when a change occurs anywhere else in the spreadsheet.

 

[vletm]

#1 order of series matter.

#2 It's a rough one sample - one possible way to do it ... with more realistic file that solution would be totally different.
As You've asked something ... expected somewhere else something else .. I've guessed too many things.
If You've one day idea to combine all those to one file then
... there will be 'some' challenges, I would call that the 'domino effect'.
You should have a clear logic - how to build something like that.

 

[GraH - Guido]

One of the most important principles of charting is that a chart is supposed to convey a clear message, bring some sort of data insight.
Looking at that chart (in several threads) I do not get the message. Hence the question comes to mind: "Does the data need a chart at all?".

To be clear, my comment is not mean as a critique, it's more a concern. For one, I'm not familiar at all with stock data, so I lack context, know how, experience, you name it. So, it can be that chart brings a lot of insights I'm simply not seeing, but others will. That's fine then.
The concern is that a lot of "technique" seems to be required to make the chart, and maybe people need to study it to make sense out of it. Generally speaking that's a bad practice.

Stephen Few has a couple of great books on the topic, I got to know him through Chandoo's blog, and I recommend them to anyone that needs to visualize data in a professional context. Having a better and deeper understanding of the principles behind charting is a must have.