Automating chart scale limits – update

Posted on November 25, 2012 by dougaj4

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:
    November 25, 2012 at 8:38 pm

    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

    Like

    Reply
  2. metroxx says:
    November 25, 2012 at 10:02 pm

    Hello, I have the same proble 🙂

    Like

    Reply

  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 comment

This site uses Akismet to reduce spam. Learn how your comment data is processed.