You've probably all seen the Batman plot that can be generated by entering a formula into google Search. Their instant graphing capability turns this complex formula into a scatterplot of 4 series.
Copy this formula into Google Search
And you are going to getinformit article by Rob Bovey, Stephen Bullen, and John Green and discovered that thay had already solved this general problem. They have created a detailed explanation of hiw to do it in that article, and I've simply used the same approach here. You can find all the examples mentioned here in Downloads in the funnycharts.xlsm workbook.
For preparation, we need to deconstruct the 4 series in the google formula. I've put these in cells b8-b11
Next we need to create some parameters - what is the range of x for the plot? I've put these in cells b4-b6
Finally, we'll make a title for the chart, in B7.
To create this plot, we'll need to user array formulas, the old Evaluate function (still present in Excel 2010 .. haven't tried 2013 yet), and named functions. As described in the source informit article, we need to create a valid chart first, and then tweak the series values. However, since I've already created this chart for up to 4 series already, all you would need to do is to tweak the parameters already mentioned. If you need less than 4 series, then just leave the formula blank and change the series line style to no line.
Excel normally needs a table of data to plot, but we don't want to actually create a table of data - rather we want to create an array of data on the fly, based on the range of x, and the f(x) equations that generate associated values for y for each series. The x values are generated with this named range (named x).
This generates an array of 1000 values for x from -7 to 7, starting like this
We then need another 4 named ranges, ySeries1..4, which will evaluate, for each value of x, a y based on the equation for each series. This looks like this and uses the old Excel4 evaluate formula. It's this that applies each value in the array x against the formulas in b8..b11. Here's an example for ySeries1.
a section of the calculated data looks like this.
And we end up with something not too bad.. not as good as the google one, but it'll do
So now we have this chart, we can plot any formulas, for example, these parameters,
Give this chart
But let's stick with some odd charts. How about the well known heart plot in google...
Or this simple one
Finally, here's some odd ones for you to try http://www.talljerome.com/NOLA/images/ti/grapherpics.pdf. If you can, then post the parameters on our forum and I'll publish them here.
Why not join our forum,follow the blog or follow me on twitter to ensure you get updates when they are available, or take a look at what else is on this site.
Services > Desktop Liberation - the definitive resource for Google Apps Script and Microsoft Office automation >