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

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:

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:

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:

Suddenly, including Excel functions has become possible!