My posts often contain charts where the data for the series is generated on the fly, but not in worksheet cells. Rather they are generated from named formulas. The technique deserves some explanation.
I put together a Lissajous Curve Explorer and Gallery to demonstrate how this works. Lissajous curves
make a good subject matter for this demonstration because minute changes in their parameterized variables can produce an infinite variation as the output.
Each plot in the Explorer contains 4,000 data points. Using named formulas to generate the data saves us the hassle and tedium of maintaining 8,000 cells of chart data (4,000 for x and 4,000 for y). Many of my projects contain dozens of series for a chart. In fact the Optical Illusions #4
had nearly the maximum of 255 series in the chart. So you can see how the number of cells to drive a chart could really add up. This technique avoids all of that.
What I do is create a named formula, n, to represent the number of points I want (this can be a constant or a formula).
The next step is to create a named formula, t:
In the Lissajous Curve Explorer, we are going to feed the trig function SIN so our t needs to vary between zero and 2π. This produces, in this case, 4,000 equally spaced values for t. Using ROW/INDIRECT is the same technique that we use in normal array formulas in worksheets to produce a loop – remember that all named formulas are in fact array formulas.
Next we define the names, a and b, and link them to two worksheet cells so our users can enter values for a and b.
Next we define the named constant, δ, as π/2; and set _A and _B both equal to one.
The last thing is to create our named formulas for x and y:
That’s it. All we need to do now is create an XY (Scatter) chart with smooth lines and define a series:
Series name: =”Curve”
Series X values: =1!x
Series Y values: =1!y
Now we can plot an infinite number of Lissajous Curves using essentially no worksheet cells whatsoever. As part of the Excel Lissajous Curve Explorer I have included a gallery that you can scroll through to see a couple hundred of the best variations (in my opinion) that I’ve discovered thus far. You can edit the gallery by supplying values for a and b over in columns AB and AC on the worksheet.
I’ve provided a scroll control to allow for easy navigation through the gallery. You can also toggle to User Select for direct input of a and b values. There are scroll controls there as well.
If you find a Lissajous curve that you really like, tell me the values of a and b in the comments and I’ll try to add a web gallery to this post!
Here’s the workbook: