How To Chart A Formula in Excel 2007

[Kris_Wright_77]

I'm looking to plot multiple formulas on a single chart, and came across a
thread from 8/12/2005 for earlier versions of Excel for a single function,
which causes errors when recreated in 2007.

Error is
A Formula in this worksheet contains one or more invalid references
Verify that your formulas contain a valid path, workbook, range name, and
cell reference

I presume its due to changes in 2007, and possibly the Evaluate formula as I
cant find it listed in 2007.

Is there a new technique that avoids the error?

Thanks Very Much for any help.

Kris

 

[Bernard Liengme]

In column A we have x-values
In column B we have a formula such as =A1^2
In column C we have the other function such as =2*A1+15
We select all the data and make a chart.

Or did you want to make a chart without using columns of values?
Please give us the reference to what you found
best wishes

 

[Kris_Wright_77]

Bernard

I was looking to create charts without using columns as I found in
techniques by
Jan Karel Pieterse @ http://www.jkp-ads.com/articles/ChartAnEquation00.asp
and Stephen Bullen @ http://www.oaltd.co.uk/Excel/Default.htm ChtFrmla.zip

I'm not a 100% certain that it will suit my needs, and I may have to go down
the route of having to use rows and columns anyway, or scale back my
aspirations.
Either way I would like to know why the techniques error in 2007.

I created something similar to what I am trying to achieve now, using
formulas in rows and columns but found that after some time the amount of
number crunching took 30mins each time it was asked to calculate.

Alternatively, is there a VBA way to mimic the Evaluate formula, as I could
then use that to populate rows and formulas with Values rather than
repetitive formulae.

Thnaks Very Much.

Kris

 

[Lori Miller]

Is there a new technique that avoids the error?

I'd just use names for this, then you don't need Evaluate and
avoid associated macro security settings.

Create a new XY chart (F11), then in Sheet1, for example, define:

n:=100
t:=(ROW(INDIRECT("sheet1!1:"&n+1))-1)/n
x:=-PI()+2*PI()*t
y:=COS(x)

where n is number of intervals to plot and t is a parameter in [0,1].

Now enter in the chart formula bar: =SERIES(,sheet1!x,sheet1!y,1)

For parametric equations replace x with s and plot x(s) and y(s),
eg for a circle:
s:=-PI()+2*PI()*t
x:=cos(s)
y:=sin(s)

You should be able to add new series to the chart in a similar way,
or using the VBA window: Names.Add "z","=x^2" etc.

HTH. Lori

 

[Peter T]

I also struggled with both JKP's and Stephen Bullen's examples, even in
earlier Excel versions. Even editing Stephen's series formula from original
names to cells then back to the original again errored. Sometimes all seems
OK, then get the message about an invalid formula. I'm almost sure the
culprit is the excel4 "EVALUATE". (Very) vaguely in the back of my head I
have an idea there's a registry fix, could be wrong.

Even if it is possible to get all working in any version, there's still a
bit of work to do to get things just right. If the overall objective is
merely to be able to type in a formula and set the parameters (x min/max &
qty of points) I suspect much easier all round to get VBA to due the work.
IOW, use VBA's 'Evaluate' to calculate the formula and return the x/y
values.

The x/y values could be written to a pair of names for 'permanent' use in
the series formula (special attention required to avoid the 256 limit and
enable unlimited values/elements in a name). However probably simpler to
dump the values into cells. The Series formula could either be (relatively)
simple dynamic names, or the VBA could update the series formula to the new
cell range (which would only change if the number of points changes)

Final thing, a worksheet change event could trigger the code to update
if/when the string formula or any of the parameter changes.

And that's all there is to it....!

Regards,
Peter T