Results 1 to 4 of 4
  1. #1
    Bronze Lounger
    Join Date
    Jan 2001
    Posts
    1,418
    Thanks
    1
    Thanked 0 Times in 0 Posts

    update a scale on a chart (2000)

    Problem: I track 60+ prices for various commodities on a daily basis, and chart them on the same worksheet. On some days, the lowest/highest price may be "off the scale" of the chart from the previous day. Any way the chart's Y scale can be automatically "rescaled" so that the minimum value is the next lowest $.10 from the lowest price for that day, and the maximum Y value is the next highest $.10 above the highest price for that day, once the current prices are plugged in?
    Thanks,
    Jeff

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: update a scale on a chart (2000)

    Jeff,

    If you double click the Y axis of the chart, you can modify several aspects of it, for instance the scale. By default, all scale values will be computed automatically by Excel. But yu can enter your own values. For instance, you can force the Major Unit to be 0.1, while leaving the Minimum and Maximum values to be calculated automatically. Perhaps that will be enough.

    If you want the scale to be adapted according to some kind of rule, you might use a macro. Take a look at <post#=142006>post 142006</post#> and the attached spreadsheet. In the example, the minimum and maximum cells contain constants, but in your case, you could use formulas that compute the values based on the lowest and highest price for the day.

    Regards,
    Hans

  3. #3
    5 Star Lounger
    Join Date
    Apr 2001
    Location
    Arriving Somewhere but not Here
    Posts
    698
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: update a scale on a chart (2000)

    Searching for a solution to my current problem has lead me here. The code in <post#=142006>post 142006</post#> will probably be useful somewhere down the line meanwhile...

    In Excel 97, SR2 on NT4/ Win2000, can I leave *just* the y-axis of a chart unlocked so the scale can be set via the usual Format Axis command BUT the rest of the chart is protected and so can't be changed by users?

  4. #4
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: update a scale on a chart (2000)

    Welcome back!
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •