Prev: Charting with empty columns in between
Next: vba to hide two grouped charts in 2003 - doesn't work
From: Kris_Wright_77 on 17 Dec 2009 11:01
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
From: Bernard Liengme on 17 Dec 2009 12:06
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
--
Bernard Liengme
http://people.stfx.ca/bliengme
Microsoft Excel MVP

"Kris_Wright_77" <KrisWright77(a)discussions.microsoft.com> wrote in message
news:91EA3E0E-4E82-4AD5-A7D1-A12E8D3602B6(a)microsoft.com...
> 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

From: Kris_Wright_77 on 18 Dec 2009 11:04
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

"Bernard Liengme" wrote:

> 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
> --
> Bernard Liengme
> http://people.stfx.ca/bliengme
> Microsoft Excel MVP
>
> "Kris_Wright_77" <KrisWright77(a)discussions.microsoft.com> wrote in message
> news:91EA3E0E-4E82-4AD5-A7D1-A12E8D3602B6(a)microsoft.com...
> > 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
>
> .
>
From: Lori Miller on 19 Dec 2009 08:49
> 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

From: Peter T on 19 Dec 2009 17:20
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



"Kris_Wright_77" <KrisWright77(a)discussions.microsoft.com> wrote in message
news:05BD79C4-C764-4137-B3A0-768EB919229B(a)microsoft.com...
> 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
>
> "Bernard Liengme" wrote:
>
>> 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
>> --
>> Bernard Liengme
>> http://people.stfx.ca/bliengme
>> Microsoft Excel MVP
>>
>> "Kris_Wright_77" <KrisWright77(a)discussions.microsoft.com> wrote in
>> message
>> news:91EA3E0E-4E82-4AD5-A7D1-A12E8D3602B6(a)microsoft.com...
>> > 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
>>
>> .
>>


 | 
Pages: 1
Prev: Charting with empty columns in between
Next: vba to hide two grouped charts in 2003 - doesn't work