Automating chart scale limits – update

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

This entry was posted in Charts, Excel, UDFs, VBA and tagged , , , , . Bookmark the permalink.

13 Responses to Automating chart scale limits – update

  1. jp says:


    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?


  2. metroxx says:

    Hello, I have the same proble🙂


  3. Pingback: Charts and Charting | Newton Excel Bach, not (just) an Excel Blog

  4. Pingback: Automating chart scale limits | Newton Excel Bach, not (just) an Excel Blog

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s