Most Valuable Professional

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

Subscribe to our mailing list

* indicates required

Audit !!!

Check out our RefTreeAnalyser
the ultimate Excel formula auditing tool.


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

Third party tools

Speed up your file

The best tool to optimise your Excel model!

Repair your file

Stellar Phoenix Excel Repair
Best tool to repair corrupt Excel sheets and objects
Home > English site > Articles > Chart an Equation > y-values
Deze pagina in het Nederlands

Charting a mathematical equation using Excel and defined names

Getting y values

Now that we have gotten a dynamic set of x values it is time to derive results. Again we'll define a named range:

Name Refers To
Formula =Sheet1!$B$1

What we need is a mechanism to evaluate the formula in cell B1 using the x values we have available. For this we'll use the fact that Excel accepts ancient xl4 macro functions inside defined names. The function needed is the EVALUATE function, which we use in the name y, local to worksheet Sheet1:

Name Refers To
Sheet1!y =EVALUATE(Formula)

Strange enough, this version of y only seems to work for functions that do not use built-in Excel functions like SIN or COS, using those in the function will cause Excel to compute all constant values, regardless of the x's!

Stephen Bullen has created an almost identical version of this workbook a long time ago, using Excel 5. Look for a download called He used a trick to make these functions work by adding "0*x" to the set of y values:

Name Refers To
Sheet1!y =EVALUATE(Formula&"+0*x")

Suddenly, including Excel functions has become possible!