Charting a mathematical equation using Excel and defined names

Pages in this article

  1. Introduction
  2. Create the Chart
  3. x-values
  4. y-values
  5. Sample Equations

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

Showing last 8 comments of 46 in total (Show All Comments):

 

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.




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