y-values

JKP Application Development Services.

Microsoft Office Application Development

y-values

Up • Introduction • Create the Chart • x-values • y-values • Sample Equations

• •

Search

Zoeken

Go to	Ga Naar
Home Up

Donate	Donaties
Did you find something helpful on my site? Consider a donation! Heeft u iets gevonden waar u wat aan had? Overweeg dan een donatie!

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)

<<Previous Next>>

Subscribe in a reader