X-axis to cross Y-axis at avrg Y-value

[Nicolai]

Have been trying for ages to get the X-axis to cross the Y-axis at exactly
the avrg Y-value - to see results above and below average. But I can't find a
way to do it. Anyone?
(Tried recording a macro that takes the avrg value and pastes it into the
field in the "Value (X) axis crosses at" field, but it pastes the formula
rather than the number, and Excel can't handle a formula in that field...)

Thanks for any help

 

[Jon Peltier]

The code is as simple as this:

With ActiveSheet.ChartObjects(1).Chart.Axes(xlValue)
.Crosses = xlCustom
.CrossesAt = dAverage
End With

dAverage is a variable that assumes the average value. If the average is
calculated in a cell, then before the code above, set dAverage's value like:

dAverage = ActiveSheet.Range("B2").Value

- Jon

 

[Nicolai]

Thanks a lot for the answer, but I still can't make it work. I inserted the
code, but it returns an error. I have now recorded a macro where I insert
manually the value "150". That works. Only 150 is not the average. Replacing
that value with "dAverage" doesn't work.

What am I doing wrong?

With ActiveChart.Axes(xlValue)
.MinimumScale = 0
.MaximumScaleIsAuto = True
.MinorUnitIsAuto = True
.MajorUnitIsAuto = True
.Crosses = xlCustom
.CrossesAt = 150
.ReversePlotOrder = False
.ScaleType = xlLinear
.DisplayUnit = xlNone
End With

 

[Jon Peltier]

You need a line of code that assigns a value to dAverage

with ActiveChart
dAverage = WorksheetFunction.Average(.SeriesCollection(1).Values)
With .Axes(xlValue)
.Crosses = xlCustom
.CrossesAt = dAverage
End With
end with

- Jon

 

[Nicolai]

Sorry, but my code looks exactly like yours, and it still doesn't work. It
says "Object or With block variable not set".

With ActiveChart
DAverage = WorksheetFunction.Average(.SeriesCollection(1).Values)
With .Axes(xlValue)
..Crosses = xlCustom
..CrossesAt = DAverage
End With
End With
End Sub

/Nicolai

 

[Jon Peltier]

Which line is highlighted? Have you activated a chart?

- Jon

 

[Nicolai]

No line is highlighted, actually. Yes, a chart is activated. I can manually
make the X-axis cross Y at the average Y-value, but the Macro doesn't do the
trick. Is it because I am not telling it which values to calculate the
average for? Does the code do that?

It looks like this (you can tell, I have tried 32 times...)

Sub Macro32()
'
' Macro32 Macro
' Macro recorded 05-09-2008 by Nicolai Kristiansen
'

'
With ActiveChart
DAverage = WorksheetFunction.Average(.SeriesCollection(1).Values)
With .Axes(xlValue)
.Crosses = xlCustom
.CrossesAt = DAverage
End With
End With
End Sub

/Nicolai

 

[Jon Peltier]

You get an error, but no line in the code is highlighted? It should tell us
which object or block variable is not set.

- Jon