Page 1 of 2 12 LastLast
Results 1 to 15 of 18
  1. #1
    2 Star Lounger
    Join Date
    May 2005
    Location
    Vancouver, Br. Columbia
    Posts
    223
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Advanced Charting 2 y axis with text scale (Excel 2000)

    Hello Woodys Lounge,

    I am seeking a way to build what I beleive is an advanced chart what some call a stiff diagram.

    Basically the chart is a water analysis of a river and I would like to the plot the various elements over a power scale.
    attached is a picture I drew of the what I would like to achieve.

    My graph will use a data range J1:Q300 (aatached workbook in next post)

    J1:Q1 is my y axis,

    Futher I would like to split the y axis into Y1 on the left and Y2 on the Right side of the graph.
    Y1 = K Ca Mg Fe
    Y2 = Cl HCO3 SO4 CO3

    The data making the line would be what ever is selected in J2:Q300.Further I think a macro would need to trigger this.
    The reason is to make the graph work dynamically with a selected data range between J2:Q300

    The data selected would be plotted over a x axis power scale. > .1 , 1 , 10 , 100 , 1000 to the left and to the right as shown in the diagram.

    Anyone have an idea how I can make a chart like this?
    Attached Images Attached Images

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

    Re: Advanced Charting 2 y axis with text scale (Excel 2000)

    Most of your values are numeric, but those for Fe are mostly text. What's up with that?

  3. #3
    2 Star Lounger
    Join Date
    May 2005
    Location
    Vancouver, Br. Columbia
    Posts
    223
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Advanced Charting 2 y axis with text scale (Excel 2000)

    Yes sorry, I used a scanner to get the data and a text file to reconize the data to import it into excel.

    the letters can be 0

    I will fix the post file

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

    Re: Advanced Charting 2 y axis with text scale (Excel 2000)

    You mention that the chart should use whatever the user has selected. What if the user selects more than one row within the data area? Should only the first row be plotted, or should the data be added together by column, or do you want to see multiple lines? (The latter would result in an unreadable chart if more than, say, 10 rows are selected).

  5. #5
    2 Star Lounger
    Join Date
    May 2005
    Location
    Vancouver, Br. Columbia
    Posts
    223
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Advanced Charting 2 y axis with text scale (Excel 2000)

    for more then one selected row the average number would be taken from the each column and the row in it.

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

    Re: Advanced Charting 2 y axis with text scale (Excel 2000)

    Since almost all values for Fe and CO3 are 0, the average for the selected rows will most probably be 0 too. It isn't possible to plot 0 on a logarithmic scale.

  7. #7
    2 Star Lounger
    Join Date
    May 2005
    Location
    Vancouver, Br. Columbia
    Posts
    223
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Advanced Charting 2 y axis with text scale (Excel 2000)

    attached is the workbook with data
    Attached Files Attached Files

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

    Re: Advanced Charting 2 y axis with text scale (Excel 2000)

    It would take me too much time to create a macro for this. Perhaps someone else can help.

  9. #9
    2 Star Lounger
    Join Date
    May 2005
    Location
    Vancouver, Br. Columbia
    Posts
    223
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Advanced Charting 2 y axis with text scale (Excel 2000)

    Then instead of 0 i will convert to .01.

    I have updated my second post with the fixes

  10. #10
    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: Advanced Charting 2 y axis with text scale (Excel 2000)

    How about this?

    It is not code, it is done with formulas (which seemed much more straightforward to me).

    In the column labeled "Plot" to the right of the data, enter an "x", all the points with an "x" will be plotted as the average. The calculations are in the 2nd sheet.

    It calculates and converts the log X scale to a value between 0 and 1, the Y are values between 0 and 3. I used datalabels to indicate the X and 2 sets of Y values

    The X-Axis will adjust based on the max of the each "half" of the data.

    Steve

    PS I noticed a problem with the items <1 in my scheme and will try to fix it when I get the chance....
    PPS I got the chance and here is the updated version...
    Attached Files Attached Files

  11. #11
    2 Star Lounger
    Join Date
    May 2005
    Location
    Vancouver, Br. Columbia
    Posts
    223
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Advanced Charting 2 y axis with text scale (Excel 2000)

    Hi Steve, Thank you for spending time on the problem.

    is there an easy way to remove the .001 and only have one .01 on the X axis

    ex.
    1000, 100, 10, 1, .1, .01, 1, 10, 100, 1000

  12. #12
    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: Advanced Charting 2 y axis with text scale (Excel 2000)

    Try this

    Steve
    Attached Files Attached Files

  13. #13
    2 Star Lounger
    Join Date
    May 2005
    Location
    Vancouver, Br. Columbia
    Posts
    223
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Advanced Charting 2 y axis with text scale (Excel 2000)

    Thanks Steve works good,

    An issue is that I have about 40 sheets that need this graph, So I started to make an access database, Anyone know if building this type of graph in access is possible?

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

    Re: Advanced Charting 2 y axis with text scale (Excel 2000)

    Although it's possible to create the same kind of chart in Access, it is *much* more difficult to massage the data the way you can in Excel.

  15. #15
    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: Advanced Charting 2 y axis with text scale (Excel 2000)

    The chart is made from just 2 lines of data (the 8 item names and the average values)

    Instead of making 40 charts, an option could be to create the one chart and just change the dataset for the various 40 items. This would be relatively easy if all had 4 items for each side, but that could be worked around.

    How this is done would depend on how your data is setup. It could be a matter of using indirect or offset information to read the correct sheet into the data sheet

    Steve

Page 1 of 2 12 LastLast

Posting Permissions

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