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
Doug
When I try and open the file, I get a message
“A formula in this worksheet contains one or more invalid references ….”
Any idea why this might be the case?
Cheers
James
LikeLike
Probably I uploaded the wrong file, I’ll check now.
LikeLike
I have uploaded the latest version, but the previous version was working OK on my machine, so that may not fix it.
Please let me know.
Note that the ChangeChartAxisScale function only works on Excel 2007 and later (I should have mentioned that)
LikeLike
Doug
Thanks, I am using xl2007.
Tried to download again, same problem.
Don’t worry, I can probably get the gist of it and try and recreate.
Cheers
James
LikeLike
I’m not getting any warning in Excel 2010. I have uploaded the same file, but saved as .xlsm.
http://www.interactiveds.com.au/software/SetScale.xlsm
Could you let me know if that works.
LikeLike
I have also just uploaded another one with the names for the Y values amended:
=EVALUATE(Sheet1!$B$17&”+Sheet1!x*0″)
=EVALUATE(Sheet1!$B$18&”+Sheet1!x*0″)
http://www.interactiveds.com.au/software/SetScale2.xlsm
LikeLike
Sorry Doug … same problem … and with file saved as XLSM … James
LikeLike
James – I have just downloaded both files from the net and tried them on a different computer running XL 2007 and Windows Vista and had no problem with either of them.
Have you got the latest service pack for Office?
LikeLike
Doug
Thanks, I have automatic updates, but have also downloaded latest SP.
I am still on XP …
Please don’t worry, I can still understand the post without the detailed workbook.
Cheers
James
LikeLike
Hello, I have the same proble 🙂
LikeLike
*problem …
LikeLike
Pingback: Charts and Charting | Newton Excel Bach, not (just) an Excel Blog
Pingback: Automating chart scale limits | Newton Excel Bach, not (just) an Excel Blog