Seondary axes line-chart not showing as per data

[Asif]

I have a plotted a periodic 100% stacked column chart with 6 data rows
(meaning 6 components of data for each period). I have a secondary periodic
data with 5 compenents that I have plotted on secondary axis. Both the data
are in per cents. I have equalized the scale on both axes.

The problem is that the lines plotted on secondary axes are not plotted in
accordance with the values. For example, one data row with values 2% to 6% is
appearing near 100% line (100% on both axes are aligned across the same
line). Even strange is that when I change the "Series Order" of the same lime
to move it up, it'd appear correctly but then the other lines would appear
against wrong values.

Primary Data
APR-07 MAY-07 JUN-07 JUL-07 AUG-07 SEP-07 OCT-07
29% 28% 23% 15% -30% 13% 19%
55% 40% 44% 50% 83% 51% 46%
7% 6% 6% 13% 15% 11% 10%
4% 20% 19% 15% 22% 18% 15%
2% 3% 2% 1% 7% 4% 6%
4% 4% 5% 6% 4% 5% 5%

Secondary Data
APR-07 MAY-07 JUN-07 JUL-07 AUG-07 SEP-07 OCT-07
24% 32% 43% 53% 61% 43% 35%
41% 26% 23% 24% 96% 37% 36%
15% 19% 17% 8% -95% 2% 13%
15% 16% 11% 10% 27% 14% 10%
2% 3% 3% 2% 6% 5% 5%

Please help.

 

[John Mansfield]

Although the two axes might be equalized, you need to perform the
calculations to make them proportional to each other in order to align the
tick marks. These two posts show how to align the tick marks by creating
proportional primary and secondary axes:

http://www.cellmatrix.net/index.php/site/analysis/proportional_axes_calculations

http://www.cellmatrix.net/index.php/site/analysis/proportional_axis_calculations_unit_conversions

 

[Asif]

Hi John,

Thank you for your help. I'm having a problem with macro. It's giving me a
"Run-time error 91: Object variable or With block variable not set". I
inserted the macro as a module and assigned it to the applicable chart. Here
is my macro:

Sub SetAxis()
ActiveChart.Axes(xlValue).Select
With ActiveChart.Axes(xlValue)
.MaximumScale = Range("U50").Value
.MinimumScale = Range("U52").Value
.MajorUnit = Range("U53").Value
End With
ActiveChart.Axes(xlValue, xlSecondary).Select
With ActiveChart.Axes(xlValue, xlSecondary)
.MaximumScale = Range("V50").Value
.MinimumScale = Range("V52").Value
.MajorUnit = Range("V53").Value
End With
ActiveChart.Deselect
End Sub

 

[Asif]

I have fixed the macro error. However, the initial problem (dis-alligned
charting against the values on secondary column) remains the same. Let me
articualte the problem again.
Lines plotted on secondary axis, do not match with the points on the
secondary axis, i.e. if Apr value, in the last line of secondary data below,
is 2%, it's appearing near 100% point on the secondary axis.

I will much appreciate any help.