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

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
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
_______


"Nicolai" wrote in message
...
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

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" wrote:

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
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
_______


"Nicolai" wrote in message
...
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

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

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

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
_______


"Nicolai" wrote in message
...
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" wrote:

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
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
_______


"Nicolai" wrote in message
...
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

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).Val ues)
With .Axes(xlValue)
..Crosses = xlCustom
..CrossesAt = DAverage
End With
End With
End Sub

/Nicolai

"Jon Peltier" wrote:

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

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

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
_______


"Nicolai" wrote in message
...
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" wrote:

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
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
_______


"Nicolai" wrote in message
...
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

Which line is highlighted? Have you activated a chart?

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
_______


"Nicolai" wrote in message
...
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).Val ues)
With .Axes(xlValue)
.Crosses = xlCustom
.CrossesAt = DAverage
End With
End With
End Sub

/Nicolai

"Jon Peltier" wrote:

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

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

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
_______


"Nicolai" wrote in message
...
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" wrote:

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
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
_______


"Nicolai" wrote in message
...
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