Results 1 to 14 of 14
  1. #1
    5 Star Lounger
    Join Date
    Mar 2001
    Posts
    989
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Extending a Bubble Chart (2000)

    I'm about to increase the number of 'bubbles' for someone from 20 to 100. I'm going to write a macro to do this, but I want to anticipate problems before hand, so I'm hoping someone has done something similar before.
    Will Excel run out of colours/patterns and can they be customized? Once completed, the bubbles will expand as I increase the Chart Area. Can I prevent this so that it will be easier to read the chart, otherwise I suspect I'll end up with a few large bubbles that obscure the others? Thanks, Andy.

  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: Extending a Bubble Chart (2000)

    I'm not sure why you need a macro to add more bubbles: you should just be able to insert more rows (or columns) into the data set. The bubble is just an XY chart with a third variable representing the relative area of the "marker"

    If the markers get too large, you can adjust all the relative sizes by formatting the data series (dbl-click a bubble) and goto options to set the relative scale.

    Steve

  3. #3
    5 Star Lounger
    Join Date
    Mar 2001
    Posts
    989
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Extending a Bubble Chart (2000)

    Maybe the chart wasn't built correctly? Each buble is treated as a data series, with the Name, X, Y and size values in different columns. So inserting extra rows doesn't create additional bubbles. How can I add 80 extra 'Series' without a macro?

  4. #4
    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: Extending a Bubble Chart (2000)

    If you want them all the same series (same color) then add them to a column and copy and paste into the picture
    1st col X, 2nd Y, 3rd Bubble size

    You can manually (or via code set each color individually (though you will only 40 colors to chose from), you can use different fill effects and patterns to darken and lighten)

    If you want excel to chose the colors put it into a "matrix" of 161 columns and 80 rows:
    Col 1 is all the x's
    Then put Y's in even columns and sizes in odd columns: 1 per row
    row 1 col 2 is Y1
    Row 1 col 3 is Size 1
    row 2 col4 is Y2
    row 2 col 5 is Size 2

    You could set up in 3 columns like for all and use a formula to break up the list into the array You can compare the row to the column and read the appropriate column
    See the attached file. I only did 30 points since with all the formulas the file got too large. once you put them in the array, you could pastespecial to get rid of the formulas.

    Steve

  5. #5
    5 Star Lounger
    Join Date
    Mar 2001
    Posts
    989
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Extending a Bubble Chart (2000)

    Thanks for the attachment and for all the effort! However, I don't see the need for all these advanced formulas in what I was trying to achieve.
    There's a column of names, columns representing X and Y values and one for the size(s). Under Chart/Source Data each single 'bubble' represents a single series. So I recorded a macro of my Adding a new series (defining the new Name, X, Y and Size value - obtained from the next row down). I then put this in a loop and created the 100 series that I need. It's difficult to tell, but I think Excel has included different shadings so that each bubble is distinct. I'm still interested in your example though - can you describe again the need for the array formulas? Andy.

  6. #6
    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: Extending a Bubble Chart (2000)

    1 chart just has 1 range of bubbles so all are single color.
    the second creates a chart with each range individual. To do this manually, you need your Y values on the diagonl. The formula I used creates the range of values from the other dataset.

    They are not "array formulas". It is just 1 formula copied into the range to fill values in the diagonal and ignore all the others.

    after you get the diagonals, you could paste special the values, then select edit - goto -special, chose formulas and errors <ok> and then delete all the NAs. (I would usually copy the formula someplace so I could use it again to regenerate or add more items: It is only 1 formula)

    I am too lazy to create all of these on my own and for a 1 time deal using the (1) formula approach is faster than writing and debugging code, less prone to errors, and you can correct mistakes easier.

    But whatever approach you like...

    Steve

  7. #7
    5 Star Lounger
    Join Date
    Mar 2001
    Posts
    989
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Extending a Bubble Chart (2000)

    Help. I've added the 80 extra bubbles to my chart, and the legend shows all different colours for them. But when I add sample data they appear on the chart with no fill colour.. just as dots. Why would this be?! Andy.

  8. #8
    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: Extending a Bubble Chart (2000)

    1) Are they uncolored bubbles or just markers? If markers they are not part of a bubble chart (you can add multiple types to a chart)

    2) Are they just small compared to the others so you can not see the color of the inside?

    3) uncolored bubbles will also come when the size is negative and you tell excel to plot negative sizes

    Steve

  9. #9
    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: Extending a Bubble Chart (2000)

    I was looking at the bubblechart again, and you don't to use the "diagonal route" at all.

    If you put them all into 3 columns and make the bubble chart, they will all be the same color.

    But all you have to do is dbl-click the range and in the options tab click the "vary colors by point" selelction and they will all be different colors.

    Steve

  10. #10
    5 Star Lounger
    Join Date
    Mar 2001
    Posts
    989
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Extending a Bubble Chart (2000)

    They are markers but there is a colour-coding for them in the legend so this makes me think that they are treated as part of the Bubble chart. I've attached the file - when this opens it will offer to link to a file, respond No to ignore this. You can see that I've added a test row, but this doesn't display in the larger of the three charts.

    When I recorded a macro and then edited to run it repeatedly in a loop, the recording repeatedly set the Chart Type to Bubble chart. Do you think I should re-create the macro and leave this (apparently redundant) line in? Thanks, Andy.

  11. #11
    5 Star Lounger
    Join Date
    Mar 2001
    Posts
    989
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Extending a Bubble Chart (2000)

    I've just noticed if I sort the list (column A) then my new series miraculously appears on the chart! Bizarre, but I think this solves my problem. Andy.

  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: Extending a Bubble Chart (2000)

    I am not sure what the "problem is".

    Those unlisted ranges have y's and sizes of zero since they are empty items in the list. If they should not be listed in the legend you will have to remove them from the range (this would be 1 advantage to making it 1 series and just change the color of the various ones. This could be dynamic. The number of series is not dynamic.

    Steve

  13. #13
    5 Star Lounger
    Join Date
    Mar 2001
    Posts
    989
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Extending a Bubble Chart (2000)

    I thought the file I sent included a series called "test"?! The rows following this are all blank in 'anticipation' of addiional series being added. When a new row is added a new bubble should appear.

  14. #14
    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: Extending a Bubble Chart (2000)

    I don't know how your macro created the series, but the sizes for the rows from 32 on are not what you want them to be.
    The size for your test (in row 32) is not cell C32 it is cell C3232.

    All the ones in the later rows have 32 prefixing the row:
    C3232 instead of C32
    C3233 instead of C33
    ...
    C32110 instead of C110

    and the "oddball"
    C32093 instead of C111

    You might to redo or even just move these cells to proper location.

    Steve

Posting Permissions

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