Making a Bubble Chart based on n-values matrix

[Haydar]

Hello everybody

I have the following question for U:

I have a table with lots of values in rows and lines.
Imagine a matrix with 10 rows and 10 lines that represent customers and
products sold to them. The values in this table represent the earnings,
like:

12,59 1,94 345,46
16,97 136,48 -256,87
1,92 356,9 -658,9...

As you see there are also negative values.
Now I want to have bubbles that represent these values.
Red ones for the negative values and green ones for the positives.

Does any body have an idea what I could do?
I tried the bubble chart but as you know it just works with x,y,and
bubblesize values and not more.
I don´t want to reorganize the table and just a macro that could be
applied to the values.

I thankk you for your help in advance

greets
haydar

 

[Jon Peltier]

The simple way to address your issue is, yes, to rearrange your data.
Although everyone seems to have such a mental block against doing so, it
will immensely simplify your life.

In your case it is even easier than all that. Your values look like X,
Y, and Z, where Z could be positive or negative. Add two columns, one to
hold the positive values, another for the negatives. Usa a formula like
this:

=IF(C2>0,C2,0)

for the positive column and

=IF(C2<0,-C2,0)

for the negative column (where the specific bubble size is in cell C2).

Select a blank cell away from your table, then create the chart using
the chart wizard. In step 1 select the Bubble type, in step 2, click on
the Series tab. If there are any series in the list, select and delete
each one. Click Add, then populate the boxes to the right. For Name,
enter "Positive" (or other meaningful name); for X and Y values, use the
first two columns in your table. For bubble size, use the first added
column of data. Click add again, enter "Negative" for name, use the same
ranges for X and Y, and for bubble size, use the second added column.

Your result is a chart with two series, one for positive and one for
negatives. Color each series appropriately.

If you really hate the idea of messing up your worksheet with added
columns, put the formulas onto another sheet or a range of this sheet
which you're not using. The chart wizard will allow you to use ranges
from different sheets.

What some people like to do is to put the "real" data onto one sheet,
then make a pretty sheet for display data, linked by formulas to the
important sheet. The chart is also linked to the "real" data sheet.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______