Secondary Y axis remains when data series is not plotted

[DashboardNovice]

I have a graph of stock prices plotted on the primary Y axis. The stock prices range from $517 to $600.96. The values on the primary Y axis range from 0 to 700. I also have volume plotted on the secondary axis with a max value of 30,000,000.

I have a check box which will allow me to display volume or not. When I uncheck it, to remove volume from the graph, the secondary Y axis still remains for some reason, but not only that, its values change. The first 5 values are all zero for some reason (lower half). The next five digits are all 1 (the upper half). It's as if Excel thinks a very small value is being plotted against the secondary Y axis. What is causing this? I would like for the secondary Y axis to disappear when volume is not being graphed.

Additionally, when I remove volume from the graph, the stocks plotted are in the $500 range (minimum Y axis value is 0, max value is 700) with a bunch of space below it going down to zero. This causes all the stock prices to be squished together. What I would like is for the minimum Y axis value to be around $500 so that the stock prices will take up more of the graph. I suspect this problem is connected somehow to the presence of the secondary axis with the values 0 and 1.

 

[NARAYANK991]

Hi ,

Can you upload your file ?

Narayan

 

[Luke M]

For the first problem, you most likely correct, sounds like xl is plotting the numbers 0, 0.1, 0.2, ...0.9, 1 on the chart, and displaying them rounded to 0 decimal places. While we can't have the chart turn on/off the axis with a switch, you might be able to fake it by using a custom number format of:
[<=1]" ";0
which would hide the small numbers when the secondary axis shrinks.

For the latter, you should be able to set the min value of y-axis to a fixed value of 500. If this doesn't work, I'd have to see a sample file, as I'm not sure what else might be going on.

 

[DashboardNovice]

Hi ,

Can you upload your file ?

Narayan

Narayan and Luke M. I have the file uploaded. The chart is on the first tab. All other tabs have the raw data and calculations.

I think the original problem might have cleared up, but I noticed something similar. If you leave the settings as they are, on the first tab under the group called Bollinger Bands there is a box called Standard Deviation.

If you increment this to 2, notice how the lowest value of the Y axis suddenly changes to zero. Do you know what is causing this?

Additionally, I still have that secondary axis showing, even though the checkbox for Volume is unchecked.

 

[NARAYANK991]

Hi ,

I think one workaround is to use the following format for the secondary vertical axis :

[>1]#,##0;

Select None for the Major tick marks.

Narayan

 

[DashboardNovice]

Hi ,

I think one workaround is to use the following format for the secondary vertical axis :

[>1]#,##0;

Select None for the Major tick marks.

Narayan

I tried that with and without the semi-colon on the end. I couldn't get it to work either way.

Do you have any idea what Excel is trying to plot? The only thing set up to be plotted on the secondary Y axis is volume. But with volume unchecked (cell M13) I can't determine what Excel is attempting to plot. It just bothers me that this is occurring when it shouldn't.