+ Reply to Thread
Results 1 to 4 of 4

One variable X Axis, y Axis, with color coding legend?

  1. #1
    Registered User
    Join Date
    03-10-2020
    Location
    San Diego, California, USA
    MS-Off Ver
    Office360
    Posts
    2

    Unhappy One variable X Axis, y Axis, with color coding legend?

    Hi,

    I'm trying to reproduce a chart that would look similar to this using Excel:

    TestChart.PNG

    I expect this is a scatter plot. But whatever I try doesn't seem to be workable, and I honestly can't figure out how to collapse it into one field for the X-Axis. I'm really struggling here.
    • I've tried using standardize to create some kind of spread to space the points out -- doesn't work
    • I've tried swapping rows and columns only to get series data which I can't work with


    I have 3 columns of data:
    ID (sample identifier)
    pg/mL Value (0-1000) (y axis would be log10)
    Grading (0-3)

    I'd also like to color each data point based on the grading column also, using that as a legend of sorts. The horizontal bar indicating mean isn't required also.

    Any help?
    Attached Files Attached Files
    Last edited by Scrummo; 03-10-2020 at 02:13 PM. Reason: More Detail, Adding sample file

  2. #2
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    15,802

    Re: One variable X Axis, y Axis, with color coding legend?

    I'm not sure I understand exactly what you are trying to do, but here's what I did to create something similar to your picture.

    1) I see no indication what the scatter chart should use for x values, so I made up a column of random numbers. D2=10+RAND()-0.5 and copy/fill down. Substitute what you actually want to use for the x values in this step.
    2) To get different colors/symbols for the different grades, I use a "conditional formatting" technique like this https://peltiertech.com/conditional-...-excel-charts/ Mostly it's about making each grade/category/condition its own column and data series. I enter 0,1,2,3 into E1:H1, then enter =IF(E$1=$C2,$B2,NA()) into E2 and copy/paste/fill down and across (note the mix of relative and absolute references for easy copying).
    3) From columns D:H, I create a scatter chart. If I don't like the limits Excel chooses for the horizontal axis, format the axis to use values I prefer. Then make any other formats I want (like changing the Y axis to a logarithmic axis).
    4) Adding the average bar is as easy as adding another data series of one point and adding horizontal error bars to that series. In a convenient cell, enter 10. In an adjacent cell, enter =AVERAGE(...). Then add these two cells as an additional data series (with the "10" cell as the x values datum and the "average" cell as the y values datum). Add horizontal error bars to this chart (you have to add horizontal and vertical error bars then delete the vertical error bars).

    Does that help?
    Quote Originally Posted by shg
    Mathematics is the native language of the natural world. Just trying to become literate.

  3. #3
    Valued Forum Contributor
    Join Date
    01-14-2013
    Location
    Austria
    MS-Off Ver
    2016 / 2019
    Posts
    339

    Re: One variable X Axis, y Axis, with color coding legend?

    manual work required
    look into the attached sample file
    Attached Files Attached Files

  4. #4
    Registered User
    Join Date
    03-10-2020
    Location
    San Diego, California, USA
    MS-Off Ver
    Office360
    Posts
    2

    Re: One variable X Axis, y Axis, with color coding legend?

    Thank you! You really helped me think about it a different way and the conditional formatting was exactly what I needed. I'm including an example of what I had in mind using random values.

    Attachment 666692

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Dynamic x axis and legend
    By VBANovice46 in forum Excel Charting & Pivots
    Replies: 5
    Last Post: 09-06-2015, 10:17 PM
  2. Help with Legend on X Axis
    By zwheeler in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 05-21-2014, 01:27 PM
  3. Color coding the X axis labels in a Bar or Line chart
    By OlivierP in forum Excel Charting & Pivots
    Replies: 0
    Last Post: 02-17-2013, 04:09 PM
  4. Replies: 4
    Last Post: 08-31-2012, 08:02 AM
  5. Legend Only Shows Used Axis on Dynamic Chart (X, Y and Y2 axis)
    By TEBrown in forum Excel Charting & Pivots
    Replies: 6
    Last Post: 06-21-2012, 10:15 AM
  6. Replies: 8
    Last Post: 02-12-2009, 10:12 AM
  7. How do I set Line chart y Axis variable X axis value?
    By John T in forum Excel Charting & Pivots
    Replies: 1
    Last Post: 04-18-2005, 12:07 AM

Tags for this Thread

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1