Bubble Chart Set up

I have the following data:

Supplier,  Brand,  Retail Price, Volume

I would like to chart it as follows:

Suppliers across the bottom, with their respective brands above.  The
position on the Y axis should be determined by the retail price.

Each brand would be a bubble.  The size of the bubble should be
determined by the brand's volume.

I can not figure out how to get this chart done in Excel 2007.  (I
have also tried powerpoint)  Any help would be appreciated.

Regards

John

0
jrfauteux (1)
2/8/2008 4:47:45 PM
excel.charting 18370 articles. 0 followers. Follow

1 Replies
781 Views

Similar Articles

[PageSpeed] 7

On Fri, 8 Feb 2008, in microsoft.public.excel.charting,
jrfauteux@gmail.com said:

>Suppliers across the bottom, with their respective brands above.  The
>position on the Y axis should be determined by the retail price.

Excel offers two kinds of line-and-marker graphs:

   Line Chart = Interval vs. Category
   XT (scatter) Chart = Interval vs. Interval

But a bubble chart is *only* offered as a type of scatter chart
(Interval vs. Interval), and the chart you're trying to design is a sort
of Interval vs. Category.  So you need to be a bit sneakier than usual.

Create a range including a column that assigns each of your Suppliers a
number, and use that number as the X series on your graph,

Supplier  Brand  SupplierNo  RetailPrice  Volume
Smith     XYZ    1           $1.00        1,000
Smith     ABC    1           $2.50        2,000
Jones     XYZ    2           $1.00        9,000

Now, using *only* the last three columns, use the chart wizard to make
your bubble chart. It would probably be best to remove the "SupplierNo"
label from the top of the column temporarily, to clue the wizard in to
the fact that this is the column you wish to be the X axis.

Now create another range of columns so that the SupplierNo is one of
three columns like so:

Suppliername     SupplierNo  Dummyprice   DummyVolume
Smith            1           0            1
Jones            2           0            1
etc.             3           0            1

And create another series on the same chart, again using only the last
three columns. Each supplier appears just once in this series, and the
data point is at the bottom of the chart. Format this series so the
bubbles are invisible, and use one of the existing Excel Add-in
utilities to give them the labels defined in the first column. (Google
"Excel chart labeler tools" to find Add-ins) You can use the same
utility to label the bubbles with their brand.

Now you have a custom-made Category scale made from a dummy series!
(Google "Excel custom axis dummy" for worked examples)

The first two columns of this range can also be used as a lookup table
to decide what the SupplierNo values in the first range are going to be.

If you want each brand to have a different color bubble, separate the
products out into their own ranges by brand, and add them each as a
different bubble series, one range at a time (I have never succeeded in
arranging them all in a block so that the Chart Wizard makes them all
into bubbles in a well-behaved and predictable way).

-- 
Del Cotter
NB Personal replies to this post will send email to del@branta.demon.co.uk,
   which goes to a spam folder-- please send your email to del3 instead.
0
del1907 (586)
2/8/2008 5:57:58 PM
Reply:

Similar Artilces:

Controlling Gap Width for BAR Chart
Hi, I have the following data for example. A = 2 B= 3 C = 4 I can create a 2 D Bar chart from this quite easily. Now If I make the data to A = 2 , 3 B= 3 , 4 C = 4 , 5 I do want to plot the data on the basis of first two values as mentioned in the first example, but I want to control the width of the Bar from the value given after comma. Please help. On Sat, 24 Feb 2007, in microsoft.public.excel.charting, Fazi <farazahmedtoor@gmail.com> said: >I have the following data for example. >A = 2 , 3 >B= 3 , 4 >C = 4 , 5 > >I want to plot the data on the basis of firs...

Multiple charts in one big chart -- how to rotate to vertical position?
I have 3 small charts set into one larger landscape chart. I can manipulate the small charts, but if the page were oriented vertically instead of horizontally I could expand the small charts to reveal more detail. Anyone have a clue what I'm talking about, or how to achieve this result? from this +------------------------------------------------------------------+ |+---------------------------+ +--------------------------------+| || | | || || | | ...

Charting
I am trying to figure out a way to add an additional Y-Axis line to a chart. Current chart is a standard bar chart with about 10 rows of data (cost breakdown). I want to add a cost per square foot, but when I try to add it to the Y-Axis by creating a custom bar line chart, it wants to add other lines of data to that axis. In addition, I can't get rid of the cost per square foot in the data table. It is not a per project total like the bars in the graph, but a cumulative cost per foot for all bars in the graph. In addition, I want to show the totals of all the data per project wit...

Question about charts
If I have two lines in a line graph and they criss-cross (like a supply and demand curve), how can I find the exact values on the x and y axis where the two lines cross? Is this possible by using Excel? Let's hope you made XY chats (no Line charts) - change chart if necessary Insert Trendlines for both (better yet use SLOPE and INTERCEPT functions) So Now you know y1=m1x1+b1 and y2=m2x2+b2 At point of intersection y1=y2 and x1=x2 m2x+b2 = m1x+b1 x= (b1-b2)/(m2-m1) y = m1x + b Its all rather simple algebra. Bernard (you can call me Prof <grin>) www.stfx.ca/people/bliengme &quo...

How to label bubbles in bubble chart
I'm trying to graph multiple rows, each of which has 3 data values. I want to graph a bubble chart whose X-axis, Y-axis, and bubble size represent these 3 data values. But I also want the bubbles to be different colors so I can distinguish them by row. Finally, I'd like to label the colors with row names in the legend. Is this possible? Kathy wrote: > I'm trying to graph multiple rows, each of which has 3 > data values. I want to graph a bubble chart whose X-axis, > Y-axis, and bubble size represent these 3 data values. > But I also want the bubbles to be dif...

Dynamic chart #9
is it possiable to make a chart like this, I got the dates in the first row, that is B01:NC01 the data is in B25:NC01, this row has a forumla in it, which gets updated each day, what I want is a dynamic chart that, will only show the current month, so when the first comes around the chart will again be blank, I know that the number of days in the month will change with each month. Is there any way to create a chart like this, I know how to use, the name range. Thanks ...

how do you add names on a bubble chart?
hi does anyone know how to add names on a bubble chart? In article <D3AD4FAC-A0D3-4C77-AF73-72313357E6CF@microsoft.com>, Ravi@discussions.microsoft.com says... > hi does anyone know how to add names on a bubble chart? > What do you mean by "names?" Series names? Chart or axis title? Data labels? Custom text? Something else? -- Regards, Tushar Mehta www.tushar-mehta.com Excel, PowerPoint, and VBA add-ins, tutorials Custom MS Office productivity solutions I bet he wants custom data labels. Here are two great (and free!) utilities for labeling Excel charts: Rob Bo...

Excel 2007 Chart in VB6 Program
I have another thread in the VB group and was told to post to these two groups so sorryfor the multi-post. Ill post there again just in case folks want to follow the thread. So I've had a Visual Basic 6 app (as an EXE) that has worked great for the last 9 years or so. Up until Excel 2003 I have had to make minimial changes for it to work properly. The app has an OLE control which acts as a container for many different external sources .In this control I embed an Excel workbook and display a chart sheet. I can interact with the workbook through the normal means of Excel programming objects...

Provide the ability to add a horizontal line to a chart in Excel
At times an event happends for which a horizontal line is a good visual indicative of the expected change in a timeline chart. Hi, Try this sites for some examples of adding lines to charts http://tushar-mehta.com/excel/charts/straight_lines/index.html http://peltiertech.com/Excel/Charts/AddLine.html http://www.andypope.info/charts/averageline.htm Cheers Andy Avi wrote: > At times an event happends for which a horizontal line is a good visual > indicative of the expected change in a timeline chart. -- Andy Pope, Microsoft MVP - Excel http://www.andypope.info ...

Chart in Excel
Is it possible to convert a Chart in Excel so that it is "fixed", ie. still looks the same, but is no longer connected to the source data ? I know that I could save it as a jpg, and then insert it as a picture, but is there a way to do it more directly. Thanks, Rob I have a note in my notes after drawings chart in conventional way 1.click the chart 2.highlight the formula in the fomrula bar 3.hit F9 4.hit enter now the chart is based on actual data and not cells. try a few small experiments. "OM" <OM@youknowwhere.com> wrote in message news:44f57d15$0$11970...

change bubble order in a bubble chart
I have inherited a company bubble chart that compares the outcome of a calculation with 3 variables with an ideal. Sometimes project value - the one that sets the size - is smaller than ideal. In this case the bubble for the project is eclipsed by the ideal. How do I modify "bubble order" to decide which is in-front and which is behind? This is accomplished by sorting the data high to low, or by sorting the series in the chart, larger values to smaller. - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services, Inc. http://PeltierTech.com/WordPress/ _______ &...

X-Axis Units Disappear in Stacked Area Chart (Excel 2007)
I have a stacked area chart that charts percentages over 12 months (Jan-Dec). This chart also includes a data series of 100% (as a line chart). Both series, one plotted as a stacked area, the other plotted as a line chart type are on the primary axis. The x-axis is set to display Jan-Dec. The y-axis is set to display the percentages. In the axis option I have set the unit choices to auto. When I save and close the file that contains the chart and then re-open, the x- axis only shows Jan on the x-axis. When I go back into the Axis Options dialog box, the major unit, minor unit, and base unit op...

Bubble Sizes in Bubble Chart
Hi, I'm having issues with sizing the bubbles in my bubble chart. I want them to be uniform, but when I enter the size number for the first one, it changes the size for all other bubbles within the chart. So either there is one HUGE bubble and several small ones on the chart, or there are several huge bubbles and one small one. I've tried starting from scratch, using other numbers for the size; nothing is working. You want the bubbles to be uniform? As in, all the same size? Then use the same value in the bubble size column for all of the points. They will all be the same size...

Local Data Settings in 3.0
Within the CRM 3.0 Outlook client, I've instructed my users to add a rule that syncs down all Accounts in their Territory rather than use the My Accounts default rule which is there by default. Users are complaining that they don't have a New button to create the rule? What permissions do I need to grant them in order for that to happen and where do I set those? TIA. Check the "Saved View" privilege: Security Role >> Core Records Tab >> Saved View Frank Lee Workopia, Inc. http://www.workopia.com/Links.htm "Bobby" wrote: > Within the CRM 3....

Charts sums 2 data series
Have 2 col of data and names 2 data series. But the second series is a total of the first no. plus the second no. I want each col to be a separate line on the chart. Right click on the chart, choose Chart Type (or Change Chart Type depending on Excel version), pick the first subtype, which is just arbitrary series, not the second, which is stacked, or the third, which is stacked to 100%. - Jon ------- Jon Peltier Peltier Technical Services, Inc. http://peltiertech.com/ Sig74462 wrote: > Have 2 col of data and names 2 data series. But the second series is a total > of the fi...

Public Folders -- Unable to set permissions and replicate them to subfolders
I'm trying to give permission to users to read and write to a public folder. I keep getting Access Denied(logged in as Administrator) and Invalid Window Handle. I am running Exchange 2003 SP2 on a Win 2k3 server. Any help would be appriciated. /aerogel On 10 Nov 2005 08:16:14 -0800, "aerogel" <lundholmster@gmail.com> wrote: >I'm trying to give permission to users to read and write to a public >folder. I keep getting Access Denied(logged in as Administrator) and >Invalid Window Handle. I am running Exchange 2003 SP2 on a Win 2k3 >server. >Any help ...

location of chart
The option to have the chart on the same sheet is not available. Why not? will need more information to even have a guess. "bludik" wrote: > The option to have the chart on the same sheet is not available. Why not? Is the sheet/workbook structure protected? -- Regards, Tushar Mehta www.tushar-mehta.com Excel, PowerPoint, and VBA add-ins, tutorials Custom MS Office productivity solutions In article <9A832669-4C00-40CE-A265-E4A9772CF20B@microsoft.com>, bludik@discussions.microsoft.com says... > The option to have the chart on the same sheet is not available. Why no...

Bubble Charts
When I try to chart data using bubble charts, the resulting chart doesn't appear to represent the data at all. Am I missing something? Hi, It's hard to say... the bubble chart will want X and Y values, along with a bubble-size value (the 3rd value). Do you have that kind of data? If not, check the data sources (right click on chart) to make sure the values you want are being used in the manner you're thinking. jeff >-----Original Message----- >When I try to chart data using bubble charts, the resulting chart doesn't appear to represent the data at all. Am I miss...

Setting up email account
Version: 2008 Operating System: Mac OS X 10.6 (Snow Leopard) Email Client: imap I am trying to set up MobileMe on Entourage. I entered smtp.me.com as the outgoing server, but Entourage cannot connet to the server. Please someone HELP! On 2010-04-09 06:17:11 -0400, Rosely@officeformac.com said: > I entered smtp.me.com as the outgoing server, but Entourage cannot > connet to the server. Please someone HELP! Have you enabled SSL and used Pasword authentication?? Corentin -- --- Office:Mac MVP http://www.cortig.net/wordpress/ --- http://www.mvps.org...

How do I name the Bubbles on my Bubble chart.
I created a Bubble Chart using the help feature but I need to name the three Bubbles. I don't know how to lay it out in excel and then copy the range to accomplish this. The easiest way to give the bubbles different names and have the names appear in data labels is to create three different bubble series. The data I used in a little demo is like this: Bubble A 1 1 1 Bubble B 2 2 2 Bubble C 3 3 3 Each mini range contains the series name in the middle cell of the top row, with X, Y, and bubble size values in the bottom row. Select...

limiting chart source data with criteria?
I know for a series you choose: Values: =A2:A10 is there anyway to limit it further by saying something to the effect of Values: =A2:10 but not equal to 0, like Values =A2:A10 <> 0 (I know that doesnt work, already tried it, but its kinda what I want to accomplish) I can go in and manually ctrl click through to limit the data source, but thats a pain, I'd rather choose 1 big block and have some statement exclude 0 values. I could sort the data....but according to my boss thats not an option, because she wants the order to be the same quarter to quarter no matter what the values....

Charting dynamic range
Ok let me say i fully understand how to name a range and in the source data use the formula =sheetname!rangename Problem arrises when my macro that runs an advanced filter with the source data and add data to the bottom of the range the chart reverts back to it's initial data source =sheet1!$a$4:$b$15 so even though it accept the named range intitailly once the advanced filter runs it almost ommits the named range. I am using 2007 and i never had this problem in 2003. What suggestions do you have for me? Thank you!!!!! -- Thank you, Jennifer ...

Shadow Chart
Can we creat Shadow/Duplicate Chart in 2nd Workbook. for example in Workbook1 i have charts, I want these chart to appear in Workkbook2 as well, when i change data source or apply someformatting on charts in Workbook 1, Charts in Worksheet 2 reflect the changes automatically. Regards Hi, You can copy the chart and paste it in the other workbook or sheet and if the data changes for one the other chart will also change. However, the is no built-in way to get the second chart to change formats as the first chart is formatted. But you could use the Camera tool to copy a picture of th...

Word 2007
I’d like to have Word always open with a flush (left margin) indents. Every time I start word I get the default indent settings, and I’d like to change these to become the settings that I had the last time I was using the program. or if I have to take the template approach, how I can have it always open my a certain template. I’m using Word 2007, and I almost always start up Word from the Run command. Keyboard Shortcut ‘Windows Button’ + ‘R’ Run: winword I don’t know if this always keeps causing Word to revert to the default settings. But I just thought that I sh...

Bubble Charts in Excel
I am trying to map the position of plants using X and Y coordinates and the area cover by each plant using the diameter in a bubble chart of Excel, but the graphs look unrealistic. I would like to know what is the equation Excel uses to calculate the size (area) of each point in the bubble charts so that I can adjust my size parameter. Thank you so much, Juan As I understand it, the bubble sizes are relative sizes. If you want the data to be smaller, you'll have to put in a data point with a "large" bubble size that you can make invisible. This will rescale the bubble ...