Results 1 to 3 of 3
  1. #1
    2 Star Lounger
    Join Date
    Aug 2002
    Location
    Adelaide, South Australia, Australia
    Posts
    146
    Thanks
    5
    Thanked 5 Times in 5 Posts

    Showing two data series on a continuum (Excel 2003 SP-2)

    Living and working as I do in the driest state of the driest (and currently especially drought-ridden) inhabited continent in the world, I am trying to help a client with a requirement to chart rainfall, water depth and salinity levels at a number of locations. This information is for distribution to farmers, and needs to be presented clearly and simply so that trends can be correctly interpreted at a glance. (Each type of information is viewed on a separate graph, to avoid confusion between the lines of data.)

    The preferred option is to show a continuous line graph from the earliest date for which readings are available, through to the current date. They would like the current year's data points to show as a different colour and/or shape from all previous years. I know that we can manually select the data points for each month of the current year and make this change; the catch is that there are over 40 spreadsheets, each with between 2 and 4 graphs, to be updated on an ongoing basis.

    I have a feeling that the only solution might be to write a VBA routine (and I am already anticipating some of the difficulties likely to be encountered in formulating that solution), but thought I should check first if anyone knows of a chart type that will allow two data series on the same continuum, or alternatively an add-on graphing package that might offer more flexibility than Excel?

    Thanks!

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Showing two data series on a continuum (Excel 2003 SP-2)

    How about something like this?

    I used random numbers to generate the data. The "current year" is a different series which overwrites the original in a different color. It uses an if statment to determine if it should be plotted or not.

    Steve
    Attached Files Attached Files

  3. #3
    2 Star Lounger
    Join Date
    Aug 2002
    Location
    Adelaide, South Australia, Australia
    Posts
    146
    Thanks
    5
    Thanked 5 Times in 5 Posts

    Re: Showing two data series on a continuum (Excel 2003 SP-2)

    Hi Steve -

    I'm sorry to take so long to respond to your suggestion, but was unable to get back to this job until now.

    However, I'm pleased to say that your solution is much better than the option I was considering of trying to write a VBA program that would automate what would have otherwise been a manual task of changing individual data points.

    Thank you very much for your prompt and effective response!

    Cheers

Posting Permissions

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