+ Reply to Thread
Results 1 to 13 of 13

Is there any way to make a chart matrix like this (tried scatter, bubble, and radar)?

  1. #1
    Registered User
    Join Date
    01-28-2020
    Location
    UK
    MS-Off Ver
    Excel for office 365 MSO
    Posts
    14

    Is there any way to make a chart matrix like this (tried scatter, bubble, and radar)?

    I don't know if this is possible, or if anyone could recommend a better way to visualise this.

    I have over 500 rows of data to show in it, but here is a small sample to show what I am trying to do - with an assumption I will be able to show a gender and race disparity of hourly wage from a data set, that the circles will be larger, more numerous, and lean more towards white male as over-represented. (*Note, this is a study exercise, the data set isn't real or from any actual place of employment).

    Any ideas?

    Thanks,

    Steve

    pay-equality-disparity-matrix.PNG

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

    Re: Is there any way to make a chart matrix like this (tried scatter, bubble, and radar)?

    Short, useless, optimistic answer -- yes, it is possible to do this in Excel.

    I am not certain I understand exactly what you are trying to do or exactly what you have tried. My first instinct is that your chart looks like a bubble chart, but you indicate that you have already tried a bubble chart, so I don't know what to recommend. I would be curious exactly what you tried when you tried using a bubble chart. I would have expected something like:

    1) in the chart's data table, the first column would calculate the horizontal position of each data point.
    2) the second column would calculate the vertical position of each data point.
    3) the third column would calculate the size of each data point (I assume you know the formulas you need for each of these columns).
    4) Select those three columns -> insert bubble chart.
    5) Format the chart as desired.
    6) Add data labels or text boxes at each corner to show race/gender for each corner of the chart.

    What did you do differently when you tried a bubble chart? What more do you need this bubble chart to do?
    Quote Originally Posted by shg
    Mathematics is the native language of the natural world. Just trying to become literate.

  3. #3
    Registered User
    Join Date
    01-28-2020
    Location
    UK
    MS-Off Ver
    Excel for office 365 MSO
    Posts
    14

    Re: Is there any way to make a chart matrix like this (tried scatter, bubble, and radar)?

    Thanks for your swift response MrShorty!

    I basically just selected all three columns, clicked add Bubble Chart, and saw it didn't make sense.

    It de-selects almost all of the data, and chooses only the last entry of hourly wage to show.

    When you say the first and second columns calculating the positions, do you mean as they are now, in the above table example?

  4. #4
    Registered User
    Join Date
    01-28-2020
    Location
    UK
    MS-Off Ver
    Excel for office 365 MSO
    Posts
    14

    Re: Is there any way to make a chart matrix like this (tried scatter, bubble, and radar)?

    I have:

    series name
    series x values
    series y values
    series bubble size

    which I can enter if I go from scratch, but if I add any of the columns to the latter two (which have ={1} already showing in the field) then I get an error message.

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

    Re: Is there any way to make a chart matrix like this (tried scatter, bubble, and radar)?

    A bubble chart needs three numeric columns -- It doesn't know what to do with text strings. If you selected those three columns, then it failed because it doesn't know what to do with the "male", "female", etc. text strings. You cannot use those three columns, so you need three new columns that effectively converts those three columns into something numeric.

    This is probably not exactly right, but it should at least show the steps:

    1) I need a column of values for horizontal position. Your chart shows male to the left and female to the right, and the axes suggest that the values extend from (-)10 to (+)10. Assuming your picture starts in A1 at the upper left, D2 might be =IF(B2="Male",-1,1)*A2 and copy down. This should return a negative hourly wage value for males and a positive wage value for females. I don't know exactly how you want gender to figure into horizontal position, so replace this calculation with whatever makes sense to you.
    2) I need a column of values for vertical position. Your chart shows non-white at the bottom (at -10 again) and white at the top (+10). In E2, I enter a similar formula =IF(C2="White",1,-1)*A2 and copy down. Again, replace this formula with whatever makes sense to you for the vertical position.
    3) Size, I assume is just a copy of the hourly wage in column A. To simplify creating the chart, I will make a copy of column A in column F =A2 in F2 and copy down.
    4) Creating the chart should be as simple as selecting D2:F5 and inserting a bubble chart.

  6. #6
    Registered User
    Join Date
    01-28-2020
    Location
    UK
    MS-Off Ver
    Excel for office 365 MSO
    Posts
    14

    Re: Is there any way to make a chart matrix like this (tried scatter, bubble, and radar)?

    Thanks, it's sort of worked... but will only allow one of the two columns, column E in this case, as a series.

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

    Re: Is there any way to make a chart matrix like this (tried scatter, bubble, and radar)?

    I don't understand. Your picture in the OP only shows one data series, so I'm not sure what you need as far as additional data series. With a bubble chart, you will add additional y/vertical position and size columns for each data series that you want to show (as well as an additional x/horizontal position column if the additional series will use different values for the horizontal position). Can you help us understand what you are wanting to do with the additional data series?

  8. #8
    Registered User
    Join Date
    01-28-2020
    Location
    UK
    MS-Off Ver
    Excel for office 365 MSO
    Posts
    14

    Re: Is there any way to make a chart matrix like this (tried scatter, bubble, and radar)?

    This is what it looks like - I think I must be missing something

    Attachment 659950

    Attachment 659952

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

    Re: Is there any way to make a chart matrix like this (tried scatter, bubble, and radar)?

    Attachments are invalid. Can you try attaching them again (make sure you are adding them from the manage attachments window)?

  10. #10
    Registered User
    Join Date
    01-28-2020
    Location
    UK
    MS-Off Ver
    Excel for office 365 MSO
    Posts
    14

    Re: Is there any way to make a chart matrix like this (tried scatter, bubble, and radar)?

    Ah the attachments are images, they open in the browser for me when I click the link... not for you?

  11. #11
    Registered User
    Join Date
    01-28-2020
    Location
    UK
    MS-Off Ver
    Excel for office 365 MSO
    Posts
    14

    Re: Is there any way to make a chart matrix like this (tried scatter, bubble, and radar)?

    Just seeing if this works, attaching individually with insert image:

    Attachment 659980

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

    Re: Is there any way to make a chart matrix like this (tried scatter, bubble, and radar)?

    I cannot open any of the images. The picture(s) in post #1 are the only pictures I can see.

  13. #13
    Registered User
    Join Date
    01-28-2020
    Location
    UK
    MS-Off Ver
    Excel for office 365 MSO
    Posts
    14

    Re: Is there any way to make a chart matrix like this (tried scatter, bubble, and radar)?

    It actually seems to work a bit better in radar, with your bubble chart advice, so I will go with that... it's close enough. I would put an image up of it, but it seems I have some competency issues around that!

    Anyway, thank you MrShorty, you have been a great help and it's much appreciated.

+ 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. Bubble chart / scatter plot problem
    By diamond chap in forum Excel Charting & Pivots
    Replies: 2
    Last Post: 11-24-2019, 04:01 PM
  2. Scatter/Bubble chart for Financial Trend with multiple series
    By Raviprasad.k in forum Excel Charting & Pivots
    Replies: 1
    Last Post: 11-16-2018, 11:37 AM
  3. Matrix bubble chart conditional formatting
    By ROHANROKS in forum Excel Charting & Pivots
    Replies: 1
    Last Post: 04-10-2017, 11:26 AM
  4. Bubble or Scatter chart
    By vjharry in forum Excel Charting & Pivots
    Replies: 3
    Last Post: 12-08-2015, 04:06 PM
  5. Replies: 1
    Last Post: 06-18-2014, 04:32 PM
  6. Convert scatter chart to bubble chart
    By JayUSA in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 07-01-2009, 01:49 PM
  7. Making a Bubble Chart based on n-values matrix
    By Haydar in forum Excel Charting & Pivots
    Replies: 1
    Last Post: 08-08-2005, 07:05 PM

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