## Subscribe to our mailing list

* indicates required

Audit !!!

Check out our RefTreeAnalyser
the ultimate Excel formula auditing tool.

Trainings

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

Third party tools

Speed up your file

FastExcel
The best tool to optimise your Excel model!

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 ChtFrmla.zip. 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!

### Note:

A Help file containing all xlm functions can be found here:

Dutch (Office 2000 only)

English (All Office versions)