Dynamic Histogram - Series Plots OFF Primary Axis? Help

[ironfelix717]

Hi,

I have created a macro with a dynamic histogram. Histogram dynamically changes based on which data set the user selects. The histogram includes 2 key features: A vertical line for MEDIAN and a separate for MEAN. The way I do this is with a Scatter Plot with Smooth Line - I only compute the min + max values for the Y-axis, and then the MEAN and MEDIAN are used for the X-axis.

Problem I am having is that when I dynamically change the data set, the vertical lines are no longer correctly on the PRIMARY axis. However, if i hover my mouse over them, they appear to be correct.

Please see the attached images. The first image depicts the one data set, where i designed the chart. The next data set is dynamically changed and results in improperly placed vertical lines.

Thank you!!

P.S. would prefer not to post my macro, as i'd like to keep it private.
Right.PNG

Wrong.PNG

Chart Types.PNG

[Andy Pope]

Are you updating both the primary and secondary axis with your macro?

[ironfelix717]

Hi, yes the macro updates the primary and secondary axis. I have tried nearly everything from using secondary axis to using non-secondary and still have no luck!

[ironfelix717]

Anyone?

ALso unsolved here:
https://www.excelforum.com/excel-gen...bar-chart.html

[Andy Pope]

You need to create a sample file with enough data and code to illustrate the problem.

[ironfelix717]

Hi,

You can download the file here:

https://www.dropbox.com/s/bro6660ngn...ting.xlsm?dl=0



Please let me know what you find. Im quite convinced this issue is not solveable.

[Andy Pope]

You need to recalculate the x values and they are not plotting as true x points.

All series are now on the primary axis. The middle of each column goes 1, 2, 3 etc. even though the category label suggests otherwise.

Range M24:N28 contains the new X values for the vertical lines.
The freq curve also needs new X values, in range F16:F29, in order to be positioned in center of column.

[ironfelix717]

So basically you just created an x-axis with a normal scale of 1,2,3... OK confusing, but I follow what you did. I honestly had a similar idea but I just couldnt quite put it down on paper (or spreadsheet in this case)... Thanks for the help. I greatly appreciate it!!