## Proportional Scaling of XY Plot in Excel

## Proportional Scaling of XY Plot in Excel

(OP)

An XY Plot in Excel can be easily created. The problem I have encountered is that I cannot force Excel to maintain a 1:1 aspect ratio between the x and y axis on the chart under the following conditions

1)the input data can vary

and

2) the monitor where the chart is viewed can have different pixel proportions which will disrupt the aspect ratio

Any suggestions?

Is there a macro that can fix this?

1)the input data can vary

and

2) the monitor where the chart is viewed can have different pixel proportions which will disrupt the aspect ratio

Any suggestions?

Is there a macro that can fix this?

## RE: Proportional Scaling of XY Plot in Excel

This will force the x and y axes to have the same limits.

Cheers

Greg Locock

## RE: Proportional Scaling of XY Plot in Excel

This still doesnt solve the problem of the effect monitor pixel proportions on the aspect ratio.

Any suggestions for that?

## RE: Proportional Scaling of XY Plot in Excel

ActiveChart.PlotArea.Select

Selection.Width = 400

Selection.Height = 400

You then need to figure out the pixel dimensions of the display and scale accordingly

TTFN

## RE: Proportional Scaling of XY Plot in Excel

I have tried that in the past. The problem still is in the proportioning to the actual display somehow.

Thanks for your help.

## RE: Proportional Scaling of XY Plot in Excel

Declare Function GetSystemMetrics32 Lib "User32" _

Alias "GetSystemMetrics" (ByVal nIndex As Long) As Long

Function display_X()

display_X = GetSystemMetrics32(0)

End Function

Function display_Y()

display_X = GetSystemMetrics32(1)

End Function

Mirekp

http://www.mitcalc.com

Mechanical calculations

## RE: Proportional Scaling of XY Plot in Excel

## RE: Proportional Scaling of XY Plot in Excel

You can set XY scales proportionally without macros, but using additional chart series consisting just of two data points to plot a diagonal line with equal tangents. The tangent length shall be dx=max(x)-min(x) or dy=max(y)-min(y) whichever is larger. Based on tangent length Excel will automatically set equal (or almost equal) limits to X and Y axes.

Step 1: Plot area shall be square (you can do it manually or in VBA (see IRstuff postage)

ActiveChart.PlotArea.Select

Selection.Width = 400

Selection.Height = 400

Step 2: Reserve 2x2 range for new series. Using IF function enter formulas for xo and yo

if dx >= dy

xo(1)=min(x) yo(1)=(max(y)+min(y)-dx)/2

xo(2)=max(x) yo(2)=(max(y)+min(y)+dx)/2

if dx < dy

xo(1)=(max(x)+min(x)-dy)/2 yo(1)=min(y)

xo(2)=(max(x)+min(x)+dy)/2 yo(2)=max(y)

Step 3: Add new series to the chart. Set line and marker width to none to make chart invisible.

On my website I have a file implementing this technique: www.yakpol.net/sectprop.zip

Hope I was clear!

## RE: Proportional Scaling of XY Plot in Excel

Very interesting Webpage. Nice to see the sharing of information. Keep up the good work!

## RE: Proportional Scaling of XY Plot in Excel

could you simply add two cells to the spreadsheet and have the user type in his screen resoultion, then scale the plot using that info?

daveleo