A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Excel » Charts and Charting
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

How To Chart A Formula in Excel 2007



 
 
Thread Tools Display Modes
  #1  
Old December 17th, 2009, 05:01 PM posted to microsoft.public.excel.charting
Kris_Wright_77
external usenet poster
 
Posts: 7
Default How To Chart A Formula in Excel 2007

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
  #2  
Old December 17th, 2009, 06:06 PM posted to microsoft.public.excel.charting
Bernard Liengme
external usenet poster
 
Posts: 4,085
Default How To Chart A Formula in Excel 2007

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


  #3  
Old December 18th, 2009, 05:04 PM posted to microsoft.public.excel.charting
Kris_Wright_77
external usenet poster
 
Posts: 7
Default How To Chart A Formula in Excel 2007

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


.

  #4  
Old December 19th, 2009, 02:49 PM posted to microsoft.public.excel.charting
Lori Miller
external usenet poster
 
Posts: 62
Default How To Chart A Formula in Excel 2007

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

  #5  
Old December 19th, 2009, 11:20 PM posted to microsoft.public.excel.charting
Peter T
external usenet poster
 
Posts: 127
Default How To Chart A Formula in Excel 2007

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


.



 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 01:25 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.