Most Valuable Professional


View Jan Karel Pieterse's profile on LinkedIn subscribe to rss feed
Subscribe in a reader

Audit !!!

Check out our RefTreeAnalyser
the ultimate Excel formula auditing tool.

Speed up your file

FastExcel
The best tool to optimise your Excel model!
Home > English site > Articles > Chart an Equation > Sample Equations
Deze pagina in het Nederlands

Charting a mathematical equation using Excel and defined names

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 (2/28/2009 2:49:19 PM)

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 (3/1/2009 11:31:19 AM)

Hi Russ,

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

 


Comment by: Plodder (6/23/2009 4:13:39 PM)

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 (6/23/2009 11:47:04 PM)

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 (9/2/2009 1:32:59 AM)

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 (11/10/2010 10:31:43 AM)

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 8:40:34 AM)

Hi John,

Which Excel version are you using?

 


Comment by: John (11/11/2010 6:43:06 PM)

I'm using Excel 2003.

 


Comment by: Jan Karel Pieterse (11/11/2010 10:53:10 PM)

Hi John,

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

 


Comment by: John (11/12/2010 10:46:34 AM)

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 (11/13/2010 11:57:44 AM)

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 (11/13/2010 5:44:19 PM)

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 (11/13/2010 6:02:24 PM)

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 (11/14/2010 7:03:33 AM)

Hi John,

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

 


Comment by: John (11/17/2010 6:47:09 PM)

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 (2/12/2011 1:21:52 PM)

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 (2/13/2011 8:37:42 AM)

Hi Carlos,

On the previous page :
http://www.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 (3/4/2011 9:31:24 AM)

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 (3/7/2011 6:29:00 AM)

Hi,

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

 


Comment by: Alan Forster (3/7/2011 9:06:41 AM)

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 (3/7/2011 10:41:15 AM)

Hi Alan,

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

 


Comment by: Alex Young (7/5/2011 6:13:19 AM)

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 (7/5/2011 6:37:12 AM)

Hi Alex,

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

 


Comment by: Alex Young (7/5/2011 6:52:17 AM)

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 (7/6/2011 4:44:18 AM)

Can you perhaps post the formula?

 


Comment by: Laertes Boechat (11/28/2011 4:08:58 AM)

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 (3/7/2012 10:30:56 AM)

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 (3/8/2012 2:54:16 AM)

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 (8/9/2012 4:31:29 PM)

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 (8/9/2012 7:37:22 PM)

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 (8/31/2012 12:23:36 AM)

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 (8/31/2012 11:37:54 AM)

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 (11/15/2012 10:41:30 PM)

Wow, this is awesome. Thanks for the tutorial.

 


Comment by: Brian (2/20/2014 5:49:05 PM)

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 (2/20/2014 6:41:20 PM)

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!!

 


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.

Please enter your name (required):

Your e-mail address (optional but if you want me to respond it helps!; will not be shown, nor be used to send you unsolicited information):

Your request or comment:

To post VBA code in your comment, use [VB] tags, like this: [VB]Code goes here[/VB].