Can you OffSet the X Y Center axis of a scatter Chart?

[whywhybother]

I have a scatter chart and I would like to offset the Center of the X and Y Axis.
Can this be done with a formula int he cell?
Can this be done using a little VB?

Thank You,

WhyWhy

[MrShorty]

It's not clear to me exactly what you are trying to accomplish. If I had to guess, I would suggest you look at the "axis crosses at" property of each axis. By default, Excel's automatic choice is to have the axes cross at 0,0, but this option (which can be accessed in the "format axis" on the "scale" tab) can be used to force the axes to cross at whatever point you would like.

[whywhybother]

Yes I am trying to figure ot how to setup a formula in a cell that would change the center of the chart from 0,0 to say X,Y. And X,Y would be the average value from my data set.

[MrShorty]

To my knowledge, there is not a built in way to link the "axes cross at" parameter with a cell. It should be acheivable using VBA if you are prepared to go there. If you are relatively unfamiliar with VBA, start by recording a macro while you manually change the "axes cross at" parameters. That should give you a basic structure to work from for writing the procedure (which you may want associated with the change and/or calculate event so it occurs automatically).

Another option, especially if you are inexperienced with VBA, might be to create "dummy" axes. In an out of the way place in your spreadsheet, use your averages to create a couple of data series that you can plot as a "crosshair" on your scatter plot. Then you can hide the actual axes.

[whywhybother]

Mr Shorty, Many Thanks that worked. You rock!!!

I was able to record a new macro and pull the code from manually setting the new Cross Axis.

Thank you

WhyWhy

With ActiveChart.Axes(xlCategory)
.MinimumScale = -130.789054757829
.MaximumScale = 130.789054757829
.MinorUnitIsAuto = True
.MajorUnitIsAuto = True
.Crosses = xlCustom
.CrossesAt = Range("B5")
.ReversePlotOrder = False
.ScaleType = xlLinear
.DisplayUnit = xlNone
End With