Charting a mathematical equation using Excel and defined names

Pages in this article

Sample Equations

I'd like to invite the readers to provide nice sample equations!

Contact me when you have found some nice examples to add here.

A sample file can be downloaded here.

Comments

All comments about this page:

Comment by: Russ Kelley (28-2-2009 14:49:19) deeplink to this comment

Interesting tutorial. I followed through and achieved a worksheet that will display function curves. I have discovered that after saving this workbook, when it is opened again I get and error to the effect:

"Worksheet contains one or more invalid references. Verify that formulas have valid path, workbook, range name and cell refrence."

I get this error when opening some of the provided links in your tutorial to some examples. I am using excel 2007.

Comment by: Jan Karel Pieterse (1-3-2009 11:31:19) deeplink to this comment

Hi Russ,

Could you email me a copy of your file please? (See address below this page)

Comment by: Plodder (23-6-2009 16:13:39) deeplink to this comment

Hi,
Thats a very useful article.
But when I was trying it in practice, I seem to have difficulty entering the name sheet1!x as an offset function (Sheet1!$A$12,0,0,xnumberofpoints,1) - it does not seem to stay as an array and hence the result is not calculated.
I use MS excel 2007 - could hat be the cause of the problem?

Comment by: Jan Karel Pieterse (23-6-2009 23:47:04) deeplink to this comment

Hi Plodder,

Excel 2007 is a bit picky about setting this up for a chart and I must admit I have not tried to set the chart up from scratch in 2007 yet. Something for my list of things to do I guess!

Comment by: Ernst van der Merwe (2-9-2009 01:32:59) deeplink to this comment

Jan Karel,

I have used different angles of charting for various reasons and purposes. The two aims I always have in mind were to reduce memory use on the one hand, and speeding up processing.

I have build many models involved graphs included VB and VBA coding. This method of graphing equations have enormous potential. I am thinking in terms of plotting algorithms.

Thanks and I will stay in touch.

Ernst

Comment by: John (10-11-2010 10:31:43) deeplink to this comment

Even though it's the file I downloaded from this web, nothing is shown in the chart after I opened it except the first point.

Comment by: Jan Karel Pieterse (11-11-2010 08:40:34) deeplink to this comment

Hi John,

Which Excel version are you using?

Comment by: John (11-11-2010 18:43:06) deeplink to this comment

I'm using Excel 2003.

Comment by: Jan Karel Pieterse (11-11-2010 22:53:10) deeplink to this comment

Hi John,

Odd, it works fine for me. Did you enable macro's and such?

Comment by: John (12-11-2010 10:46:34) deeplink to this comment

Yes, when I opened the sample file, I got an meesage: "This workbook contains one or more Microsoft Excel 4.0 macros. These macros may contain virus or other harmful code. Open this workbook only if you trust the source. Do you want to enable these macros?" I clicked "Yes". The chart has only one point (-10,1), which obviously is not the function point. When I saved this file or saved as in another name, the curve and points appeared immediately.

Comment by: Jan Karel Pieterse (13-11-2010 11:57:44) deeplink to this comment

I expect if you click control_alt+F9 the points will also appear. Looks like the file isn't properly recalculated when you open it.

Comment by: John (13-11-2010 17:44:19) deeplink to this comment

No, I tried F9, Ctr+Alt+F9, select the chart then F9 or Ctr+Alt+F9, nothing changes. I also tried to print it, nothing changes.

I tried to genrate another x-y chart. A message came out: "A formula in this worksheet contains one or more invalid referenced. Verify that your formulas contain a valid path, workbook, range name, cell reference." when tried to type "=Sheet1!y" in the field of Y values. It's no problem when I entered "=Sheet1!x" in X values field.

Comment by: John (13-11-2010 18:02:24) deeplink to this comment

Ctr+Alt+F9 doesn't work.

Sorry I made a mistake when I tried to create another x-y chart: I forgot to enable macro.

After I enabled the Macro, I could create another x-y chart by entering "=sheet1!x" in the field of X Values and "=sheet1!y" in the field of Y Values. And the curve appeard in the new chart, but not in the old chart.

After I saved as, both charts displayed correctly. But when I quitted EXCEL and reopen the file, both charts have no cuves, only one point (-10,1).

It seems the chart couldn't get y. When I typed "=y" in a cell, it showed correct value: 126

Comment by: Jan Karel Pieterse (14-11-2010 07:03:33) deeplink to this comment

Hi John,

OK, thanks for trying. I'll investigate this as soon as I have a bit of time.

Comment by: John (17-11-2010 18:47:09) deeplink to this comment

Finally, I solved the issue:

Change the definition of Y from =EVALUATE(Sheet1!$B$1 & "+0*x") to ==EVALUATE(Sheet1!$B$1)+0*x

After save, close, then open the file again, the curve appear beatifully. It seems EXCEL 2003 doesn't support the old macro function EVALUATE perfectly.

Comment by: Carlos (12-2-2011 13:21:52) deeplink to this comment

Hola.
¿Cómo se puede graficar dos o más ecuaciones matemáticas en un mismo plano cartesiano.
Gracias

Hello.
How do you graph two or more mathematical equations in a single Cartesian plane.
Thanks

Comment by: Jan Karel Pieterse (13-2-2011 08:37:42) deeplink to this comment

Hi Carlos,

On the previous page :
https://jkp-ads.com/Articles/ChartAnEquation04.asp
I describe how to define the Y range name. For each equation you want to display, you have to create one separate Y range name and a separate Formula range name:

Sheet1!Y_1, Sheet1!Y_2
and
Sheet1!Formula1 and Sheet1!Formula2

Comment by: Alan Forster (4-3-2011 09:31:24) deeplink to this comment

After an hour of searching I found this page. I had seen this technique many years ago but could not remember how to do it.

I would like to use a user defined function in the Evaluate function, is this possible?

Thanks,

Alan.

Comment by: Jan Karel Pieterse (7-3-2011 06:29:00) deeplink to this comment

Hi,

I think the best way to find that out is by trying!

Comment by: Alan Forster (7-3-2011 09:06:41) deeplink to this comment

Hello,

I did try, but it did not work. I wondered if there was another little trick like adding the + 0*x.

Evaluate is a poorly documented feature and I have struggled to learn much about it. As I type I wonder if it requires the function to be volatile? I will give it a go and report back if it works.

Thanks,

Alan.

Comment by: Jan Karel Pieterse (7-3-2011 10:41:15) deeplink to this comment

Hi Alan,

OK. I guess you should make the UDF volatile.
I'm interested to learn your result!

Comment by: Alex Young (5-7-2011 06:13:19) deeplink to this comment

Hi Jan,

How complex can the formula be? I have a situation with plenty of logic arguments, IF & ANDs - I can make it work using two IFs but any more than that and it fails. Its a long formula but it is correct, I've substituted the x for a value and calculated it and it comes up with the correct answer.

Any help you can offer would be appreciated,

Thanks,

Alex

Comment by: Jan Karel Pieterse (5-7-2011 06:37:12) deeplink to this comment

Hi Alex,

I don't really know, maybe the formula cannot be longer than about 250 characters?

Comment by: Alex Young (5-7-2011 06:52:17) deeplink to this comment

Thanks for the prompt reply, my longest formula has 216 characters...

Is there any way to introduce other variables through the EVALUATE function? Or otherwise split up long formulas? I have worked out basic addition/subtraction already.

Regards,

Alex

Comment by: Jan Karel Pieterse (6-7-2011 04:44:18) deeplink to this comment

Can you perhaps post the formula?

Comment by: Laertes Boechat (28-11-2011 04:08:58) deeplink to this comment

Certain things do not come into existence by trial and error alone. The method of using the scattered chart for plotting an equation is already very creative.
Thanks for the tip. Great idea.

Comment by: Jared (7-3-2012 10:30:56) deeplink to this comment

Jan,

This is a very creative solution to graphing equations in excel. I have been attempting (unsuccessfully) to build a graph that plots two (2) equations using your trick. I'm not a pro with using the defining tools so I don't know if what I am trying is even theoretically possible.

Any thoughts?

Jared

Comment by: Jan Karel Pieterse (8-3-2012 02:54:16) deeplink to this comment

You will have to define a new name for the second function, e.g. Sheet1!Y_2 that holds the second equation. Note that a name like Y2 is not allowed because it duplicates a valid cell address, hence the underscore I used.

Comment by: Graeme (9-8-2012 16:31:29) deeplink to this comment

I have a simple plotting function set up as below to plot a function specified by three cells. It all runs perfectly.

The inputs are in three cells:

B1     log(2*x) [ Formula: the function being plotted ]
D1     -10         [ xStart: the independent variable starting value ]
E1     10         [ xEnd: the independent variable ending value ]

The named ranges parameters are as follows:

     Name                 Value             Refers To

1:     Formula             log(2*j)         =Sheet1!$B$1
2:     x                     {...}         =xStart+xRange/(xNumberOfPoints-1)*(ROW _
                                                 (OFFSET(Sheet1!$A$1,0,0,xNumberOfPoints,1))-1)
3:     xStart                 -10             =Sheet1!$D$1
4:     xEnd                    10             =Sheet1!$E$1
5:     xNumberOfPoints        100             =100
6:     xRange                 20             =xEnd-xStart
7:     y                     {...}             =EVALUATE(Sheet1!$B$1 & "+0*x")

All the above works perfectly.

However, I wish to be able to modify the above so it is able to plot functions with the independent variable other than x, say j, so I add an eighth named parameter Var, which holds the variable j for use in the formulas.

Name Value Refers To

8: Var j =Sheet1!$C$1

I then set the four input cells as follows:

B1     log(2*j) [ Formula: the function being plotted ]
C1     j         [ Var: the independent variable ]
D1     -10         [ xStart: the independent variable starting value ]
E1     10         [ xEnd: the independent variable ending value ]

To date, I haven't been successful in getting the Excel plotting tool to plot the function when the independent variable is specified in cell C1.

I would be most grateful for any assistance.

Comment by: Jan Karel Pieterse (9-8-2012 19:37:22) deeplink to this comment

Hi Graeme,

Can you email the workbook to me along with an explanation inside? (email address near bottom of this page)

Comment by: Jean Blair (31-8-2012 00:23:36) deeplink to this comment

I have followed all of the steps on this page and have the x values coming out nicely. However, I cannot get the y values to change - the calculation is correct for x=1 but it is holding x constant. I tried the trick suggested on this site but have not had any luck. My parameter for y is:

=EVALUATE('cost curves'!$B$17&"+x*0")

Any suggestions as to what I might be doing wrong? I am using excel 2011.

Thanks!

Comment by: Jan Karel Pieterse (31-8-2012 11:37:54) deeplink to this comment

Hi Jean,

If I look at that formula, the value of x will not matter, since it is multiplied with zero.

Comment by: Jake Warren (15-11-2012 22:41:30) deeplink to this comment

Wow, this is awesome. Thanks for the tutorial.

Comment by: Brian (20-2-2014 17:49:05) deeplink to this comment

Hi.

I was excited to find this tutorial. Unfortunately I can't make it work. However, the downloaded zip works fine.

The problem is that when modifying the chart line SERIES, an error popups informing that there are invalid references or formulas.

I've taken care to ensure the defined names and formulas are exactly the same as those found in the zip. I've go so far as to change the file type from 2010 to 97-2003.

Please help - I'd like to learn this technique.

Thank you,

-brian

Comment by: Jan Karel Pieterse (20-2-2014 18:41:20) deeplink to this comment

Hi Brian,

Unfortunately, Excel 2003 worked just fine, but later versions seem not to like this technique very much.

I lack time to try and figure out what is wrong though, sorry!!

Comment by: Raj (4-6-2015 07:30:39) deeplink to this comment

Sir the following equation used in google graph also worked which include cos etc functions with following dataset

xStart=-4
xEnd = 4
xNoOfPoints=99

Formula=(sqrt(cos(x))*cos(500*x)+sqrt(abs(x))-0.4)*(4-x*x)^0.1

Comment by: Raj (5-6-2015 15:25:51) deeplink to this comment

tested on excel 2007 & worked with taking
y=evaluate(formula)+0*x
and saving workbook in xlsm format (Macro Enabled Work Book)

Comment by: Shubhangi (23-6-2015 21:11:43) deeplink to this comment

Sir can you please tell how can I perform slicing of a sine wave at 1000 time instants using Charting a mathematical equation using excel?

Comment by: Jan Karel Pieterse (24-6-2015 11:30:06) deeplink to this comment

Hi Shubhangi,

Enter sin(x) in cell B1
Enter 0 in b3
Enter 6.28 in B4
Enter 2000 in B5

Comment by: CoolKoon (28-11-2015 22:38:32) deeplink to this comment

I've tried to do this in Excel 2013, but to no avail: the offset function gave me an error for the formulation outlined in the article and it seems that Microsoft has removed the "evaluate" function completely (it doesn't show up even in the complete function list or anywhere else). I don't suppose you have a workaround for Excel 2013, do you?

Comment by: Jan Karel Pieterse (30-11-2015 12:14:20) deeplink to this comment

Hi CoolKoon,

The download file works just fine in Excel 2016, so I figure something else must be wrong with your attempt to replicate.

Comment by: Alfred (5-8-2016 05:14:47) deeplink to this comment

I just set this up using Excel 2010. It works if the following rules are followed.
1. The scope of each and every named range MUST be the sheet that the chart is on.
2. Each and every formula that references the named range, MUST include the sheet name eg. Sheet1!Formula
3. The suggestion to add +0*Sheet1!x to the EVALUATE function will allow the workbook to open without producing an error message about one or more names not being correct.
4. If the formula to chart starts with a negative the addition of ' at the beginning will prevent Excel from trying to turn the entry into a formula for cell B1 eg. '-2*x^2+1

Comment by: Chuck James (27-7-2019 16:21:00) deeplink to this comment

Raj is a genius! When I plotted this I started laughing so hard! How in the heck did you come up with this?
xStart=-4
xEnd = 4
xNoOfPoints=99

Formula=(sqrt(cos(x))*cos(500*x)+sqrt(abs(x))-0.4)*(4-x*x)^0.1

Comment by: Peter Atherton (21-7-2020 13:26:00) deeplink to this comment

Jan, Thanks for your tutorial, it helped me a lot. I tried writing my own from scratch but kept getting 'There is an error in the formula' message. It turned out it was in the X value, I'd inserted a a blank column in A so I had to change the formula to:
=xStart+xRange/(xNumberOfPoints-)*ROW(OFFSET(Chart!$B$1,0,0,xNumberOfPoints,1))-1).
I have also tried to plot two equations without success and think I'll have to use the old sheet method for this. Has anyone already solved this?
A couple of formulas I like are:
Bellcurve: NORM.S.DIST(x,FALSE)
Falling Feet: 269-16*x^2, (0,4.1, 6)
Falling Meters: 83-9.81/2*x^2 (0,5.5,15)

Comment by: Leland Taylor (5-12-2020 20:18:00) deeplink to this comment

getting list of X values...Why is column Q shown when we seem to be talking about A1:A20? Sheet1!x has already been named to A11:A15, so are we changing it? Going to =row(1:20)? So, we highlight column 1 down to row 20 and then type =x ... so we get #s 1-20 in the cells? Then do we re-insert within each cell row(offset(sheet1!$a$1,0,0,Sheet1!$b$5,1))? Wouldn't this delete Formula, xStart, xEnd, xNumberOfPoints etc. which are located in column A? Can you explain?

Comment by: Jan Karel Pieterse (7-12-2020 11:10:00) deeplink to this comment

Hi Leland,

You are right. I have changed the screen-shot so this becomes less confusing.
Thanks!

Have a question, comment or suggestion? Then please use this form.

If your question is not directly related to this web page, but rather a more general "How do I do this" Excel question, then I advise you to ask your question here: www.eileenslounge.com.