Plot a line? Set dymanic axis Max min

[Stan Altshuller]

Hi all,
two questions:

1. I have a scatter plot of returns and I want to add not a trend line but
simply x=y line to my plot (and other equations)
Any way to do this?

2. I need to control both my x and y axis, I want to max and min and scale
numbers to be linked to a cell in my spreadsheet instead of simply being
static values or auto.

THANKS!
Stan

 

[Guest]

Stan,

Regarding question 1, you can add equations to charts by plotting points in
an Excel worksheet and then charting those points. For more detailed
information, Stephen bullen has some excellent examples of how to plot
functions . . .

http://www.bmsltd.ie/Excel/Default.htm

Regarding question 2, please refer to Jon's site for how to create dynamic
charts . . .

http://peltiertech.com/Excel/Charts/DynamicColumnChart1.html

http://peltiertech.com/Excel/Charts/Dynamics.html

The entry below might help with setting a dynamic Y axis via code . . .

http://www.pdbook.com/index.php/excel/tie_y_axes/

 

[Don Guillett]

Maybe this idea will help.

Private Sub Chart_Activate()

ActiveChart.Axes(xlValue).Select
With ActiveChart.Axes(xlValue)
.MinimumScale = RANGE("B4")'bottom
.MaximumScale = RANGE("B5")'Top
.MinorUnitIsAuto = True
.MajorUnitIsAuto = True
' .MajorUnit = 500
.Crosses = xlAutomatic
.ReversePlotOrder = False
.ScaleType = xlLinear
.DisplayUnit = xlNone
End With
ActiveChart.Deselect
Application.ScreenUpdating = True
End Sub

 

[Jon Peltier]

Stan -

1. Plot a formula

You could put some X values into a column, use the formula to compute Y values in
the next column, then copy this range, select the chart, use Paste Special from the
Edit menu to add a new series, with categories in the first column.

Here are some fancier examples that bypass the worksheet:

http://oaltd.co.uk/DLCount/DLCount.asp?file=ChtFrmla.zip
http://tushar-mehta.com/excel/software/plot_manager/index.html

2. Linking axis parameters to cells

How to:

http://peltiertech.com/Excel/Charts/AxisScaleLinkToSheet.html

Add-in that does it for you:

http://tushar-mehta.com/excel/software/autochart/index.html

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