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

Programming and Web Development Forums - MS EXCEL - Microsoft Office Excel

Skip to content

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

MS EXCEL
Post a reply

    Next

  • 1. Macros
    Hi, Can anyone suggest how to link a macro to picture file. I am trying to link data to picture files using macro and would greatly appreciate a little help. Thanks in advance Cheers Roy
  • 2. division by zero error
    I have data where there is a division by zero error because nothing is entered, but that will change when the data is entered. Is there a way I can suppress the chart from putting 0's in its place so that there is a gap in the chart when there is a division by zero error?
  • 3. how to create a jpeg file from Excel Chart
    Hello How can i create a jpegfile from an excel chart quickly. All ideas much appreciated!! A despairing Texas Tonie
  • 4. Change Line Width based on formula
    Is it possible to change the line width (or color) based on a if-then statment? For example: if the value of one line if hitting new lows for the day but the value of another line is not hitting new lows for the day is it possible to change the width or color of the first line because this condiition is false? Thanks!
  • 5. Real-time update of chart using a Spin Button
    Excel 2003: If I use a spin button (form control) to update a cell in the series of a line (or bar) chart, the chart does not update until I 'let go' of the spin button. i.e. I click and hold on the spin button and the data is updated in the relevant cell in the spreadsheet, but the chart will only update when I stop 'spinning'. Today, I have seen the updating of a bar/line chart as described above, but the chart updated in real-time whilst the spin button was held down. The author of that demo was not aware that he had done anything special to achieve that effect, and when we compared the spin button control settings in both his and my worksheets, they both appeared identical. Can anyone offer any suggestions as to what I have to do to achieve real-time updating of a chart whilst the spinner is held down? Thanks in advance.

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

Postby Tmljb2xhaQ » Sat, 06 Sep 2008 02:34:01 GMT 

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
Top

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

Postby Jon Peltier » Sat, 06 Sep 2008 03:30:20 GMT 

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://www.**--****.com/ 
_______
Top

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

Postby Tmljb2xhaQ » Sat, 06 Sep 2008 04:33:00 GMT 

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
Top

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

Postby Jon Peltier » Sat, 06 Sep 2008 12:20:02 GMT 

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

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

Postby Tmljb2xhaQ » Sat, 06 Sep 2008 21:31:01 GMT 

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
Top

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

Postby Jon Peltier » Sat, 06 Sep 2008 22:00:31 GMT

hich 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" < XXXX@XXXXX.COM > wrote in message
news: XXXX@XXXXX.COM ...


Top

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

Postby Tmljb2xhaQ » Sat, 06 Sep 2008 22:30:22 GMT

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

Top

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

Postby Jon Peltier » Sun, 07 Sep 2008 01:02:21 GMT

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

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


"Nicolai" < XXXX@XXXXX.COM > wrote in message
news: XXXX@XXXXX.COM ...


Top

Similar Threads:

1.Need PivotChart x axis to cross Y axis at minimum Y axis value 

I see an option to have x cross y at max y, but how can I say min y?

2.Value (Y) axis crosses between categories and Secondary Value (Y) Axis 

Hi all -

I have 7 series on my chart
Series 1 is column chart - may be area chart eventually (2007 data)
Series 2 is column chart 2008 data
Series 3 is column chart 2007 avg
Series 4 is a column chart 2008 avg
Series 5 is a line chart (32,0 benchmark)
Series 6 is a line chart (points to column chart for 2007 avg, I then
add text to right to explain the column)
Series 7 is a line chart (points to column chart for 2008 avg, I then
add text to right to explain the column)

I would like the column charts for 2007 and 2008 data to be flush to
the origin (12 data points each year)
I would like the benchmark line chart of 32 to span the chart area
from left to right (16 data points)

>From what I've read, the way to accomplish this, is to clear the tick
mark from Value (Y) axis crosses between categories when formatting
the x axis.

However, when I do that, the data point for 2007 closest to the origin
appears cut-off on left side of column

I more or less posted this information on Ozgrid a few days ago
(Ozgrid is now down until Nov. 13 according to the site). Andy Pope
replied that if I move the line charts to the Secondary Value (Y)
Axis, that would resolve the problem.

I must be doing something wrong. I did assign the line chart to
Secondary Value (Y) Axis and then cleared the tick mark on the x axis:
Value (Y) axis crosses between categories. However whatever datapoint
is closest to the Primary Y Axis disappears entirely

What am I doing wrong here?
Send a confidential e-mail for a sample copy of the workbook
Best regards,
-markc

3.Using a cell reference as the axis value for Horizontal axis crosses 

Is there any way of referencing a cell as the value in the "axis
value" area of the "Format Axis dialog box. I am currently entering a
number i.e. 2.5. I would like to be able to have this number change
when a user enterer a value in a cell?  Would it have to be VBA code
or is there another way?

4.Can't get the x-axis to cross at value 0 for both y-axes 

Hello - 

I am working on an Excel chart with both a primary and secondary y-axis and 
only one x-axis.  The primary has positive and negative values.  The 
secondary has all positive values.  I am using the secondary as a rank (by 
selecting the box to invert values so the highest rank has a value of 1 and 
appears at the top of the secondary y-axis, where rank 25 is the lowest 
possible rank and appears at the bottom of the secondary y-axis), in theory 
where the x and y axes meet.  The x-axis is different points in time.  So 
this chart shows trends over time.

The issue I am having is the primary x-axis crosses the primary y-axis at 0 
(y-axis value), which is what I want.  However, the primary x-axis crosses 
the secondary y-axis at a value that is not the lowest value on the secondary 
y-axis.  In this case, since I am using it as a rank, it is not crossing at 
the lowest rank.  I am not showing the secondary x-axis associated with rank.

I don't have this issue if all of the values on the primary y-axis are 
positive.  In other words, the primary x-axis crosses at 0 on the primary 
y-axis and at the lowest rank on the secondary axis.

My question is, is there a way to fix my problem?  Is there an option where 
I can set the value where the primary x-axis crosses a value on the secondary 
y-axis?  I know I can set the value for Primary x-axis to primary y-axis - 
and do the same for the secondary x and y axes, but I'm not aware of a way to 
cross this primary - secondary boundary.

I imagine the graph's axes looking like a backwards 4 - the primary y-axis 
goes above and below the primary x-axis.  The primary x-axis is approximately 
in the middle.  The secondary y-axis is as high as the primary y-axis but 
only goes down to the x-axis (i.e., it does not extend below the x-axis).  Is 
this possible?

Something like this:

|            |
|_______|
|
|

Thanks!

- Derek

5.why can't Y-axis cross the X-axis at zero 

Hi,

The problem I have is that the y axis crosses the x axis 
at 1 and when I try to change that to 0 under format 
axis>scale it returns a message saying "The Number must 
be between 1 and 31999, Try again bla bla.."

Is there anyway to change that?


Thanks,

John

6. chart secondary axis crossing with x axis

7. How to insert X axis scale values next to axis and X axis grid lin

8. Predicting new Ys given new Xs using known relationship for X

Post a reply

Return to MS EXCEL

 

Who is online

Users browsing this forum: No registered users and 17 guest

Powered by phpBB © 2000, 2002, 2005, 2007 phpBB Group