Chart: "Line-Column on 2 Axes"... but...

[(PeteCresswell)]

I don't even know enough to compose a meaningful Subject line for this one.

I have an existing routine (MS Access VBA) that takes an investment instrument's
payment stream over time and graphs two figures: something called Payment Amount
and something called "Percent Of Notional".... bottom line, it's two values.

I put them on a "Line-Column on 2 Axes" chart and all is well. The payments
come up as bars and the percents come up as a line.

All was well and life was good.



But now the same user wants to compare two or three different instruments on the
same chart. Same look/feel - just three different entities with two value
streams each instead of one entity.

Presumably, I'm going to wind up with two or three bars for each payment and two
or three lines for the percents.



Beyond that, I don't have a clue.

Fooled around with "Line-Column on 2 Axes" but it seems like I'm trying to
shoehorn something into it that it's not made for.

True?



I don't even know what "2 Axes" means for sure. Seems like it's two types of
representation: bar and line in this case... but that's only a guess.

Can somebody wind me up and put me on the right path?

Even the proper chart type would be a big help.

 

[Jon Peltier]

Two Axes means primary and secondary axes, for the case where the column and
line have completely different values.

The built in types are not too flexible. If the chart is in Excel, use the
following procedure. I've had mixed results with MS Graph, which is the
charting applet used by other programs.

Create the chart with all columns. Select each column series you want to
convert to a line and go to Chart menu > Chart Type, and select a suitable
line style. Repeat as needed.. If you want to use primary and secondary
axes, double click each series you want on the secondary axis, and on the
Axis tab, select Secondary. Repeat as needed.

- Jon

 

[(PeteCresswell)]

Per (PeteCresswell):

Fooled around with "Line-Column on 2 Axes" but it seems like I'm trying to
shoehorn something into it that it's not made for.

I think I'm getting somewhere.

Looks to me like "2 Axes" refers to two different Y value systems and this is
still the appropriate chart type except that I just need to add more series to
it.

And, to coerce the representation of each series to bar or line, I just set
SeriesCollection(i).ChartType=XlLineMarkers or XlColumnClustered.

Am I on the right track?

 

[Jon Peltier]

Looks good.

- Jon