Pages in this article
-
Introduction
-
Create the Chart
-
x-values
-
y-values
-
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:
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:
Sheet1!y
=EVALUATE(Formula&"+0*x")
Suddenly, including Excel functions has become possible!