Proportional Axis Calculations - Unit Conversions
A recent post to the Microsoft Excel Charting Discussion Group asks "how can I show pounds on the primary axis and kilograms on the secondary axis with tick marks that correspond to each other?" I'm thinking the chart should look something like the one below:
The solution requires calculating a primary and secondary axis that are proportional to each other. The illustration below shows those calculations:
Finally, I've found that the VBA procedure below is the easiest and most accurate method to set each axis. Add the procedure to a regular module, select the chart, and run the macro to set the axis.
Sub SetAxis()
ActiveChart.Axes(xlValue).Select
With ActiveChart.Axes(xlValue)
.MaximumScale = Range("C26").Value
.MinimumScale = Range("C28").Value
.MajorUnit = Range("C29").Value
End With
ActiveChart.Axes(xlValue, xlSecondary).Select
With ActiveChart.Axes(xlValue, xlSecondary)
.MaximumScale = Range("D26").Value
.MinimumScale = Range("D28").Value
.MajorUnit = Range("D29").Value
End With
ActiveChart.Deselect
End Sub
You end up with four series when you really only want to show two. At this point double-click on a series. In the Format Data Series dialog box, you can hide two of the four series via the Patterns tab.
