Constant scale both axes??

[pgseye]

Hi,

I'm doing a research project which involves analysing variation in the shape of the optic nerve (so, oval/circular shapes). I want to use Excel to make a scatterplot of each shape from a set of coordinates generated by a specialised imaging utility. The scatterplot would represent each individual shape. I then want to save each scatterplot as an image to use with shape analysis software.

The problem is, Excel doesn't seem to generate a scatterplot with constant scaled x and y axes. Instead, I get disparate unit scaled axes. I want one x axis unit to equal one y axis unit (so effectively a square plot), so that when I save the image, the shape is true (ie Excel induces distortion in the image which affects shape).

I can't seem to find a way to generate a constant scaled scatterplot (only adjust min/max values etc).

Can this be done??

Many thanks.

[shg]

The dimensions can't vary much -- pick an adequate, identical scale for both axes and apply it manually.

If is really does vary that much, you need VBA.

[pgseye]

Thanks for that.

It really does matter, but I'm not especially familiar with VBA. Would you be able to suggest where I could find such a macro??

[shg]

How about you start:

Create formulas for four cells containing the min and max x and y values appropriate to a given chart based on the data.

Create another two formulas for cells to contain the desired (common) min and max scale values for x and y based on the four cells calculated above and some rounding (all worksheet stuff so far).

Record a macro that sets the x and y scale values of the chart (necessarily, without regard for the calculations above, since Excel doesn't let you assign scale values based on a cell).

If you don't see a path to finish from there, post the workbook.

[pgseye]

Thanks shg,

It's not so much an issue of setting min and max values. All co-ordinate measurements are in mm and a span of 3 (mm) on the scale (ie -1.5 -> 1.5 for x and y) on either axis would cover all plots. I really just need the scaling to be the same for each axis.

I'm beginning to think Excel can't do this. I guess the other thing I could do is find screen ruler/caliper software and adjust somehow that way (still not accurate).

I've attached a workbook with example data and a scatterplot. I haven't altered the plot - standard output. The true shape should be vertically oval (if axes were the same), not horizontally oval.

Thanks a lot.

[shg]

I took your plot and adjusted the scales to be the same (+/- 1.5mm with 0.5mm major divisions), and resized it so the plot area is square. Looks good to me.

Any measurements you needed to do (e.g., cross-sectional area, best-fit oval, whatever), you would do on the data, not on the plotted output.

Excel is not a precision tool for doing this, but it's close enough for most applications. Perhaps you need to use a CAD tool if you need precisely scalable output.

[pgseye]

Thanks for your help.

[shg]

De nada ...

[Andy Pope]

You could plot a dummy series consiting of 2 points. This will then force the auto scaling to be the same on both the x and y axis.

For you data set place this formula in D4

=MAX(ABS(MIN($A$2:$B$73)),ABS(MAX($A$2:$B$73)))

then the new series would be based on D6:E7 with the x values in column D and the formula for those cells being.

D6: =D4
D7: =D4*-1
E6: =D4
E7: =D4*-1

Format the series to have not markers or line.