I wrote about this function 2 1/2 years ago, and promptly forgot about it, but a few days ago brian provided an answer to a question that was raised shortly after it was first posted, concerning how to apply the function to secondary chart axes.
I have updated the spreadsheet to incorporate brian’s code (slightly modified), and the new version (including full open source code) can be downloaded from: SetScale.xls
I have also added an example of the technique for plotting a function entered as text on the spreadsheet. The procedure is:
- Name cells containing the lower and upper limits for the X range: “xstart” and “xend” respectively.
- Create a range “nsteps” with the value 1000 (or however many steps you would like in your graph)
- Create a name “x” that will contain a rnge of x values, between the specified limits by entering: =xstart+xrange/(nsteps-1)*(ROW(OFFSET(Sheet1!$A$1,0,0,nsteps,1))-1) in the name “refers to” box (see picture below).
- Create names “Y1vals” and “Y2vals” with the formulas: =EVALUATE(Sheet1!$B$17&”+x*0″) and =EVALUATE(Sheet1!$B$18&”+x*0″) (adjusting the cell references to the location of your function(s).
- Create an XY (scatter) graph and set the data ranges to =SetScale.xls!x for the X range for both series and =SetScale.xls!Y1vals and =SetScale.xls!Y2vals for the two Y series