Making a Bubble Chart based on n-values matrix

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


-- 
Haydar
------------------------------------------------------------------------
Haydar's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=26041
View this thread: http://www.excelforum.com/showthread.php?threadid=393877

0
8/8/2005 2:57:55 PM
excel.charting 18370 articles. 0 followers. Follow

1 Replies
558 Views

Similar Articles

[PageSpeed] 2

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/
_______


Haydar wrote:

> 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
> 
> 
0
8/8/2005 10:16:07 PM
Reply:

Similar Artilces:

Stacked Charts
Hi all, i need some help creating a stacked bar chart in excel. I want to lay this out in quarters so Quarter 1 will be Turnover and Target two charts but next to each other and same applies to all other quarters. Currently they are all spaced and individually spaced. I have tried changing the gap and it does it for each one. Please help!! Thanks. Hi Neil. OK, let’s start drawing what you have done so far. Please look at:- http://www.pierrefondes.com/ On the home page the first item is number 18. Please open this item. This is what I have done for you s...

Charting information from nultiple work books
I have 4 different workbooks of information, with 12 (1 per month) tabs in each workbook. On each tab I have about 40 lines of information that need to be graphed. I am having great difficulty doing this. I am relatively new to excel, so it's a little over whelming. Each tab has 11 expenses, 7 income revenues for the current year, the previous year, YTD current year, YTD last year, Variance and YTD variance. There a lot of informatin. i'm trying to graph the information on a pie chart, trailing for 12 months. I need help with how to display the right information in the be...

Creating a chart with values from two columns
I have two columns with numbers and I am trying to make a very easy chart but I don't know how. I want the numbers from column A to be on the X axis and the numbers from column B to be on the Y axis and to create a line between theese points. http://peltiertech.com/Excel/ChartsHowTo/index.html should help you. "Ivan" <Ivan @discussions.microsoft.com> wrote in message news:6DB36425-A222-4A36-BD92-9B7BBEF9F60A@microsoft.com... >I have two columns with numbers and I am trying to make a very easy chart >but > I don't know how. I want the numbers from column...

how to create a chart of a sheet from a xsl file that is on a url
Hi. I would like to create a chart. The "problem" is that the file, that already is a XSL file is on a URL and i what the chart to be update from that file each time i open my xsl file. In other words, i have a file that each time i open is going to do an update on a chart based on data that is on a static URL. Thank you. ...

Comparing a formated result with a harcoded value
Hello. I have a cell containing the numeric value. I use cell formatting to present the numeric value as which month it is. The formats value is "april" In another cell I have the hardcoded text value "april". How do I compare these two cells and find out if they are the same? /Anders -- a94andwi ------------------------------------------------------------------------ a94andwi's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=21077 View this thread: http://www.excelforum.com/showthread.php?threadid=532567 One way: Assume A1 is the cell...

Populate subform based upon a query from listbox
I have a listbox that has it's masterfield and child field set to one query, and my subform set to another (while both queries are linked to the same table) Once I select an item in the listbox, how can I get it to populate the subform fields for another iteration of the main table data? I hope this makes sense. Thanks, John Petty Allow me to reword and expand a bit. I have a Master form that houses two tab controls and a series of command buttons. One tab control holds a login screen and a listbox. The other tab controls holds a series of subforms that are used for data entry...

Chart Wizard #4
I'm in MSAccess, using MSChart V9. When I create a new chart I usually use the wizard. One of the wizard screens is what I call the "Axis Dialog Box. It says... "How do you want to layout the data in your chart?" There is a Preview Chart button, and I can select values from my table fields, and paste them into the X Axis or Y Axis, or the Value box. You can also apply aggregate functions to the fields (Avg, Sum, Min, etc..) How can I access this screen after the wizard process? If that dialog box is only available in wizard mode... can I duplicate it's function with ...

Integration Manager Excel 2007 Based Source
Has anyone succesfully created a source for Integration Manager (Dynamics GP v9 or v10) using an Excel 2007 source file? I am able to create a new DSN using the Microsoft Excel driver (*.xls, *.xlsx, etc.), however the moment I then attempt to select the "Table" drop down for my named range Integration Manager provides a dialog box to Debug or Close Program. If all else fails, save the file as an Excel 2003 file and try that. Frank Hamelly MCP-GP, MCT East Coast Dynamics ww.eastcoast-dynamics.com Have you created a named range for your data (including the header) within y...

XY Chart with 2nd Category Lables
I have an XY chart with several series of y-data. My x-axis has two sets of data which vary by a constant, ie X2 = 12.393/X1. I want to have the data for X1 on the bottom of the chart and X2 on the top of the char but cannot figure out how to use the Second Category (X) Axis that appears in the Chart Options. Michael - In order to use the secondary axis, you need to have one or more series assigned to the secondary axes. You could use one of your data series for this (or any dummy axis). Alternatively, you could use a dummy series to simulate another axis as described here: http://pe...

Money 2006 Trial: Investment activity on price history and chart
I've been using Money 2002 and 2004 for about 3 years. Just downloaded Money 2006 trial and installed it. I have not yet tried it for much time, but found one thing that doesn't work. In Money 2004, if I have an investment transaction on a particular date, I could delete the price of an investment updated online for that day, and go back to the investment activity, manually input the transaction, or change the transction date back and forth if the transaction was downloaded. Then the price history of that investment will show the price and the action (such as Buy or Sell). In additio...

How do i not plot values as zero?
I am trying to create a running chart whereby information is added daily. The information, as added, is run though a formula and is added to the chart. I want only the new information to plot on the chart, not the cells where information has not been added. The problem I am having is that the cells where the information has not been added are plotting as "zero". I have the option checked where it will "not plot empty cells", but since the cells where data has not been entered contains a formula, it is not recognizing this as an empty cell and plots it as zero. I...

Display formulas instead of values
Hi. I'm working on a sheet, and sometimes, after modyfing a formula in a cell, excel (XP) displays me the formula instead of the result.... I have to close, reopen workbnook, or copy-paste form another formula to solve this.... any ideas? I think you place a space before the = sign by accident Excel think it is text now. -- Regards Ron de Bruin (Win XP Pro SP-1 XL2002 SP-2) www.rondebruin.nl "Andrew Ofthesong" <Andrew@NoSpam.net> wrote in message news:uO1Ct6dVDHA.652@tk2msftngp13.phx.gbl... > Hi. I'm working on a sheet, and sometimes, after modyfing a for...

delete 0 values
Hi I am trying to delete cells with 0 values, even though i type in $0.00 in find, it finds nothing. i get the message find under values not formulas, but there is no option for this in the find options. thanks tim If you ask find to look for $0.00, then it will search for the text string "$0.00". If you want to find numeric zeros (regardless of format), then just type 0 in find. If that fails to find any, then I suspect that the values you see as zero are not exactly zero (try formatting in scientific notation). You can use the ROUND() function to force the results of ...

Default Value + Date Format + Check Box
I could use some help... I have a workorder form. On the form, I have a "FieldRepair" check box. If the check box is selected, it should lock down another field called "Date Picked Up." The "Date Picked Up" field is for equipment repaired in house. Not only must the "Date Picked Up" field be locked down if the equipment is to be repaired off-site, I also need the "Date Picked Up" field's default value to be the "Date Finished" field. Hope that all makes sense. I have the check box working as far as locking it down if the ...

Change source data in a pivot chart
Hi All I have a stack of pivot tables each with a pivot chart. I now need to move the location ot the Pivot tables which is the source data to the charts. But in Chart=>Source data.. is dimmed out and i can't find a way of changing the pivot chart's pivot table location. Any suggestions?? TIA jpohnb Did you try moving the pivot table to see what would happen? I just did a quick test with a pivot table and standalone chart. I cut the pivot table and copied it to a different sheet, and the pivot chart knew where the pivot table ended up. - Jon ------- Jon Peltier, Microsoft...

line chart question
I am making some line charts and am wondering if anyone knows a simple way to have the lines transition from data point to data point in crisp 95-degree angles, rather than direct lines as is the default. Thanks for any suggestions. Mike, I think your requirements need clarification. If it's a line chart, then the items on the x (category) axis will be equally spaced, and the angles at each point will only be 95 (or any) degrees for certain values of the data points. What do you want to adjust to force these angles? -- Regards from Virginia Beach, Earl Kiosterud www.smokeylake....

Extracting the Month value from a fully qualified date column
Hi, I am trying to extract the month value from a date column (clsddate) that is refreshed from a database on open. This is to create a pivot table report (seprate sheet) that specifies number of WO_NUM by month. (using count function in the pivottable for total of WO_NUM) WO_NUM CLSDDATE 19330 11/03/2004 10:46 20704 10/03/2004 09:43 22978 17/03/2004 16:09 23530 23/03/2004 10:10 23653 13/04/2004 15:29 24263 05/01/2004 14:28 24514 05/01/2004 10:58 25952 13/01/2004 14:28 26079 05/01/2004 11:17 26117 12/03/2004 14:33 26311 11/02/2004 11:17 26325 16/02/2004 16:13 26349 26/01/2004 07:51 26367 17/...

New chart type
I want to present my data in a chart that is completely of my own design - not a bar chart or pie chart, etc, something other than the standard chart types. Does Excel allow me to design my own totally new chart type. I'm not talking about formating options for existing chart types, I'm talking about a whole new chart design and presentation paradigm for my data other than the ones provided with Excel. Thanks, Darryl There are *many* visual representations that one can create from the creative use of various XL/VBA capabilities in conjunction with existing charting elements...

Combination charts #9
When I add a line graph to a bar chart, the bar width goes to zero and Excel will not allow me to readjust the width. Is there a way to fix this? I'm using a secondary axis for the line graph. Does the line chart use a time-scale axis? Are the points separated by a number of days? Probably Excel is leaving room in between the existing bars for nonexistent bars on the in-between days. You can switch to a category type axis. Go to Chart menu > Chart Options > Axes, and select Category where Automatic is now selected. In Excel 2007, right click the axis, choose Format, and cho...

chart data line in two colors
My boss wants a data line in my chart be green when abouve goal and red when below goal. I do not think it is possable, but I thought I would check out here first. You could try making a second series column containing only the values that are above target, leave the other cells blank. Check under Tools>Options in the Chart section to make sure blank cells are not plotted and plot both series, you should be able to get the 'above target' series to sit on top of the 'all values' series and give it a different colour to achieve the effect you want. Andrea Jones &quo...

Knowledge Base in Outlook Client 08-09-06
Does anyone know how to get the Knowledge Base into Outlook Client? I tried removing the Client="Web" in Sitemap.xml...did not work. Thanks, -- -Nick Nick, This won't help but it seems to act in the same wasy as the Settings section?! What I mean by this, is that it doesn't appear in Outlook (I have the desktop version installed) at all, when I have Outlook running and I use IE then I can't even see Knowledge Base, but when I close Outlook and then use IE then there is Knowledge Base in the Service section under contacts but above contracts. Never noticed it b...

Using Range Names in Charts
I just converted from Excel 2003 to 2007. Whwn I try to use Range Names in charts I get an error "Formula contains invalid references" Please Help -- Eli Kedar Israel If the name begins with "chart", you may have trouble implementing it. - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ "Eli Kedar" <EliKedar@discussions.microsoft.com> wrote in message news:86605B4C-C3DE-4871-A484-B966585AA434@microsoft.com... >I just converted from Excel 2003 to 2007. >...

create chart with data from across seperate worksheets
i am currently trying to create a line chart using data found in different worksheets, but in the same workbook. although i can carry this out by cutting and pasting all the required data into the same worksheet whenever i try to pick the data from several or even two sheets i get a error message. i would've thought i'd be able to pick this up seperately...as you can for any other formula. Hi, See Jon's page for information. http://peltiertech.com/Excel/ChartsHowTo/ChartFromDiffSheets.html Cheers Andy Diz wrote: > i am currently trying to create a line chart using data...

Chart filling in dates that aren't supposed to be there
I have created a table where the values are measured only during week-days. The problem is that the Excel line chart created from the table's data wants to automatically fill in values for the weekend as well. Thus, if the data range is from columns I through N, representing May 30, June 2, June 3, June 4, June 5, June 6 and June 9, the chart automatically fills in values for May 31, June 1 and June 7 and June 8. How do I get the chart to just represent the dates I have entered, and not extrapolate for every day of the week? -- Geoff Hi, You could right-click the chart, choose Cha...

How to Draw a chart line connecting 2 points in the middle of the series
Hi........I'm fairly new to VBA coding, so forgive me if I'm missing something very basic! I've created an embedded chart on my sheet called "ClickChart" that's using data on another worksheet (that has other embedded charts on it). The ClickChart's Logarithmic Line chart plots a stock's price over the user-inputted date range. My goal is to plot user-inputted High, Low, and Average GrowthRate lines for that stock. So far, using Jon Peletier's instructions (thanks!),I have captured the endpoint info (date, price) with the MouseUp event and placed it o...