# 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.

Audit !!!

Check out our RefTreeAnalyserthe ultimate Excel formula auditing tool.

Trainings

Excel VBA Masterclass (English)Excel VBA for Financials (Dutch)

Third party tools

Speed up your file

FastExcelThe best tool to optimise your Excel model!

Repair your file

Stellar Phoenix Excel RepairBest tool to repair corrupt Excel sheets and objects

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)

"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)

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

Comment by: Plodder (23-6-2009 16:13:39)

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)

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)

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)

Comment by: Jan Karel Pieterse (11-11-2010 08:40:34)

Which Excel version are you using?

Comment by: John (11-11-2010 18:43:06)

Comment by: Jan Karel Pieterse (11-11-2010 22:53:10)

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

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

Comment by: Jan Karel Pieterse (13-11-2010 11:57:44)

Comment by: John (13-11-2010 17:44:19)

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)

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)

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)

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)

Â¿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)

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 (4-3-2011 09:31:24)

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)

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

Comment by: Alan Forster (7-3-2011 09:06:41)

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)

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)

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)

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)

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)

Comment by: Laertes Boechat (28-11-2011 04:08:58)

Thanks for the tip. Great idea.

Comment by: Jared (7-3-2012 10:30:56)

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)

Comment by: Graeme (9-8-2012 16:31:29)

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)

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)

=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)

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)

Comment by: Brian (20-2-2014 17:49:05)

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)

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)

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)

y=evaluate(formula)+0*x

and saving workbook in xlsm format (Macro Enabled Work Book)

Comment by: Shubhangi (23-6-2015 21:11:43)

Comment by: Jan Karel Pieterse (24-6-2015 11:30:06)

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)

Comment by: Jan Karel Pieterse (30-11-2015 12:14:20)

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)

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)

xStart=-4

xEnd = 4

xNoOfPoints=99

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

## 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.