Showing Significance Testing in Bubble Charts

We are preparing a bubble chart in which the x-axis shows scores from a 
survey, the y-axis shows relative importance, and the z-dimension shows the 
percentage point change in the score from the previous period. We have set 
the default color of the bubbles to grey (which means no significant change), 
and would like to make the significant changes colored in red (negative) or 
green (positive). We have already done the stat testing, and can mark the 
significance testing with a -1 (negatively significant), 0 (insignificant), 
and +1 (positively significant). We would like to be able to automate this 
(i.e., not do manually), as we have at least 100 different charts we need to 
create. Is there any way to automate this? The data array looks like this:

2006 Score            Importance             %point change         
Significant?
54                            .27                          -2                
         0
77                            .59                          8                 
         1
82                            .47                           3                
         0
68                            .78                          -5                
         -1

So we would have a grey bubble for row 1 and 3, a red bubble for row 4, and 
a green bubble for row 2. Any suggestions?                          
0
SteveChap (3)
6/13/2006 12:49:02 PM
excel.charting 18370 articles. 0 followers. Follow

1 Replies
737 Views

Similar Articles

[PageSpeed] 43

Hi,

This is what I suggested to a very similar post a couple of days ago.

The normal work around for making a charts formatting conditional is to 
use multiple series. Then via formula only plot those points 
(bubble/columns/markers/bars) that are valid.

See Jon's page for examples.
http://peltiertech.com/Excel/Charts/ConditionalChart1.html

Cheers
Andy

SteveChap wrote:
> We are preparing a bubble chart in which the x-axis shows scores from a 
> survey, the y-axis shows relative importance, and the z-dimension shows the 
> percentage point change in the score from the previous period. We have set 
> the default color of the bubbles to grey (which means no significant change), 
> and would like to make the significant changes colored in red (negative) or 
> green (positive). We have already done the stat testing, and can mark the 
> significance testing with a -1 (negatively significant), 0 (insignificant), 
> and +1 (positively significant). We would like to be able to automate this 
> (i.e., not do manually), as we have at least 100 different charts we need to 
> create. Is there any way to automate this? The data array looks like this:
> 
> 2006 Score            Importance             %point change         
> Significant?
> 54                            .27                          -2                
>          0
> 77                            .59                          8                 
>          1
> 82                            .47                           3                
>          0
> 68                            .78                          -5                
>          -1
> 
> So we would have a grey bubble for row 1 and 3, a red bubble for row 4, and 
> a green bubble for row 2. Any suggestions?                          

-- 

Andy Pope, Microsoft MVP - Excel
http://www.andypope.info
0
andy9699 (3616)
6/13/2006 1:09:27 PM
Reply:

Similar Artilces:

line chart base to a pivot chart
Hi All, I've just made a pivot table with two pages,28 data,and one row heading. I want to create some line charts on another sheets.how to do it?. I want every time I select a page from my pivot table,the chart will display data form the selected pages.If one chart can display all the data, the rest data will be displayed on another chart> Is it possible?Please advise me how to do it step by step. Thank's ...

Showing HH:MM
Hi, Is there any way to show HH:MM on the date fields? The database is registering the time and minutes but it is not showing on the application. Thanks, tirso ...

Need shaded area in gantt chart to show lines around each grid squ
Hi - I have created a gantt chart using excel 2002. I have a shaded area on my chart from 9/1-9/10/07. Can I make the shaded area show lines through it? This would make it much easier to follow down the page on the date selected. I know it is easily done in excel when you fill in say 3 squares in yellow and then highlight the area and select borders. I tried doing this in my chart, but can not make it work. Any help would be appreciated. -- Karen W. ...

Run program without it showing in Taskbar
I have an archive program that runs all the time in the background. It listens on a port for an occasional requests from another computer and then the receives files. The problem is, it sits in the taskbar whereas I'd like it to run invisibly. Is there any way to run a program on Windows XP so it doesn't show in the taskbar? I'd really be happy if it appeared as an icon in the bottom right corner area (where the clock is) but I'd also be happy just to remove it from the taskbar. Start it from a different account with Task Scheduler, and don't elect to run it in...

Old bill shows up instead of new bill
Good morning... when i download my new transctions from my bank money 2003 canadian seems to assign my auto insurance payments to previous insurer, not the one currently setup with automatic bills in Bills & Payments. so everytime i do a download i have to go and delete all the instances (usually the last four) that got imported. any ideas how i can get money to recognize these payments in the downloaded transactions should be associated with my current auto insurer; not the previous one? and then not re-enter old transactions and associate new transactions correctly? -- ...

A Square is Showing Instead of a Period for a Currency.
In my spreadsheet, a square is showing in the currency amount rather than a period. For instance, if I need to have $400,000.00 in the cell, it gives me $400,000Square00 in the cell. I can’t draw a square from the keyboard here, so I typed in the word SQUARE here instead of drawing it out. This only happens to one user’s login with admin rights. If I login as local admin to the PC, to access the same excel file, it doesn’t happen. Anyone any idea? I will appreciate it. I'd look in windows regional settings. Windows start button|settings|control panel|regional settings applet. Look ...

Dynamic Charting Question
I'm attempting to set up a dynamic chart. So far, I've defined a name for cells B33:M33 (using the following offset equation). =OFFSET(Sheet1!$B$33,0,0,1,COUNTA(Sheet1!$33:$33)-1) I'd like to be able to add more columns after M and the chart will automatically pick it up. We have a graph that currently includes B33:M33 as the X axis values for a line chart. How do I change the information in the chart to include the determined dynamic range for X? Thanks in advance, Barb Reinhardt I've figured out what I need to edit to get the series to change, but is there ...

Importing data from several sheets, to one chart
I need data from many sheet into one chart (In its own sheet). I know how to do it when its from from sheet (In this example, sheet named Pernille) : =Pernille!$AS$10 I tried this : =Pernille!$AS$10;Aase!$AS$10 But the reference was not valid. Please help, I havent been able to find anything, not even in my E-learning. Martin, Probably the easiest way to chart data from multiple sheets is to create a summary range in a single sheet that references the other sheets with formulas. If you use this technique then all you have to do is generate the chart using the summary range as your sourc...

Publisher 2003 is not showing graphics or wordart
when importing (or draging from one *.pub to another) the graphic goes 'missing'. The box is still there as is the little white drag anchors. The graphic manager shows the graphic but it's not on the screen. It's also not in print preview even though the graphic does print out with all the rest of the page. Wordart also suffers the same fate - I guess it's a little thing - but I can't find it. View, pictures, detailed display. -- Mary Sauer MS MVP http://office.microsoft.com/ http://msauer.mvps.org/ news://msnews.microsoft.com "Nightnurse" <Nightnur...

test
jblkjdvbadsv ...

Chart making
When I select a column of data to make a chart. It only takes first cell and leave all others. In chart shows only one value, why? Arun, Please try this first - if you are working with the Chart Wizard, make sure that you have the option "Series in Columns" selected as opposed to "Series in Rows". This input is available in step number 2 of the wizard under the Data Range tab. If this doesn't work can you post back with a sample of your data? -- Regards, John Mansfield http://www.pdbook.com "Arun Bhadoria" wrote: > When I select a column of da...

Show progress bar during Serialize
Hi,all I want to show a progress bar during Serializtion of a large file, how can I get the progress of Serialize? Any comment is very appreciated! B/R Daric "Daric" <anonymous@discussions.microsoft.com> wrote in message news:<04bb01c39d49$edd21f10$a001280a@phx.gbl>... > Hi,all > I want to show a progress bar during Serializtion of a > large file, how can I get the progress of Serialize? > Any comment is very appreciated! > B/R > Daric May be easier to use the wait cursor, search help on CWaitCursor If you really want a progress bar, you could crea...

How can I set margins for headers on charts. The margin option ap.
I need indent chart headers to coincide with margins selected fopr the chart itself. I can not find any control in Excel or printer softeare that will indent a left-justified header. As you noted, there is no built-in chart option to indent a left justified header. I would suggest replacing the header with a text box. You can then use character spacing within the text box to create the look of an indented header. ---- Regards, John Mansfield http://www.pdbook.com "sayles1986" wrote: > I need indent chart headers to coincide with margins selected fopr the chart >...

Charting a linear equation
I have a chart in excel 2007, the X series is edited to be: ={0,150000} The Y series needs to be a formula, =2X+1000 How can I enter this in so it leverages X in its calculation. I know I can make cells that do the calcs, but cant this be written up in the series editor without using extra cells? Thanks! jlc On Fri, 27 Apr 2007, in microsoft.public.excel.charting, Joseph L. Casale <JCasale@newsgroup.nospam> said: >How can I enter this in so it leverages X in its calculation. I know I can >make cells that do the calcs, but cant this be written up in the series >edi...

Version 2002 - Org Chart - Creating using wizard with xls Data Source
Hi Guys, I'm creating an org chart using xls spreadsheet as the data source. Is there a way to sort the branches alphabetically automatically? Cheers Unfortunately, the Org Chart solution generates the diagram using a random order. -- Mark Nelson Office Graphics - Visio Microsoft Corporation This posting is provided "AS IS" with no warranties, and confers no rights. "u828576" <bitofpacedropshoulder@yahoo.com> wrote in message news:OxaL%23dwvHHA.1524@TK2MSFTNGP06.phx.gbl... > Hi Guys, > > I'm creating an org chart using xls spreadsheet as...

Area Charts
I am using an area chart to display data over the past 16 quaters and would like the ends of the chart touch the "Y" axis line. Currently the area chart begins in the middle of "X" axis point Q1. Can anyone help? Hi DavidZ, Double click the x axis to display the format dialog. On the Scale tab you need to uncheck the Value axis crosses between categories. Cheers Andy DavidZ wrote: > I am using an area chart to display data over the past 16 quaters and would like the ends of the chart touch the "Y" axis line. Currently the area chart begins in the middl...

Help with Scroll and Zoom Bar for Candle Chart
I am currently doing data manipulation for my series of data. I have Time, Open, High, Low, Close. There are approximately 6000 rows of data that I need to input into the Candle Chart and to do that I require a Scroll and Zoom function. I have been using a template for Scroll/Zoom for a normal basic Line Chart and attempted to reconfigure for a Candle Chart. I have named the following: SCROLLVALUE=Sheet1!$S$31 SERIES1==OFFSET(Sheet1!$B$1,SCROLLVALUE,0,ZOOMVALUE,1) SERIES2==OFFSET(Sheet1!$C$1,SCROLLVALUE,0,ZOOMVALUE,1) SERIES3==OFFSET(Sheet1!$D$1,SCROLLVALUE,0,ZOOMVALUE,1) SERIES4==OFFSET(Shee...

HELP, My chart dosen't work correctly.
I am working in Excell XP. I have a chart I need to do, but can't get i to work. The chart needs to have a list of text on the left, y axis and it needs to be JUSTIFIED text with text wrap. On the right needs t be a BAR chart showing survey question results. When I get the text to wrap the bars no longer line up with th questions. I am having a hard time with this. Then it must be able t print portrait style on an 8 1/2 x 11 sheet. Any help would be appreciated. Marshal ----------------------------------------------- ~~ Message posted from http://www.ExcelTip.com ~~View and post usenet ...

single mail item showing
I am running Outlook 2000 on win2k pro with Exchange 2000 as the mail server. There are no other client connections on the Outlook client. The problem that I am having is that certain users with multiple mailboxes, from one extra to six extra, sometimes have one mailbox that all messages disappear from except for one. There are no .pst files and when you go through and clear the customized views the messages reappear. BUT only for a few days. I look at the customized views of multiple users that are not having this problem and they are the same view as the ones with the problems. Also one of...

Box and Whisker Charts
Has anyone ever made one? I've been cranking them out all afternoon. Here's my technique: http://peltiertech.com/Excel/Charts/BoxWhiskerH.html http://peltiertech.com/Excel/Charts/BoxWhiskerV.html - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services http://PeltierTech.com/Excel/Charts/ _______ Kwalters wrote: > Has anyone ever made one? ...

about radar chart #2
I am new in Excel please let me know about radar chart? for which propouse it will used ? let me some samples ? best You can search in Google for information. For example: http://www.skymark.com/resources/tools/radar_charts.asp http://www.jcrinc.com/subscribers/source.asp?durki=3849&site=48&return=3827 http://onyx.quadbase.com/ECServletDemo/RadarServletDemo.html hassan barjini wrote: > I am new in Excel please let me know about radar chart? for which propouse > it will used ? let me some samples ? > best -- Debra Dalgleish Excel FAQ, Tips & Book List ...

Variable Width 100% Stacked Column Chart
Hi, I need to make a variable width 100% stacked column chart (similar t the Column chart with varying width bars from andypope.info but 100 stacked) The x-axis would contain various market segments and the width would b proportional to their size, the y-axis divides each segment up by a attribute. Any ideas? Thank ----------------------------------------------- ~~ Message posted from http://www.ExcelTip.com ~~View and post usenet messages directly from http://www.ExcelForum.com Earl - Stephen Bullen has a Variable Width Column Chart example on his web site (http://bmsltd.co.uk), lo...

axis font excel chart?
how can I change the font of the scale in an excel chart? Right-click on the relevant axis. Format Axis/ Font/ Size: -- David Biddulph "ColinB" <ColinB@discussions.microsoft.com> wrote in message news:6B1096F8-87B3-4642-BCC8-0EEE11FE0DED@microsoft.com... > how can I change the font of the scale in an excel chart? ...

change font color of first cell in data table attached to chart
When I change the color of my font in a data table that I attached to my column chart, the first cell or what appears to be the name for my first column along the x-axis will not change color. WHY? Hi Momo, >When I change the color of my font in a data table that I attached to my >column chart, the first cell or what appears to be the name for my first >column along the x-axis will not change color. WHY? Because Excel does not work with the data cell formatting for its graphs but only with the data cells volumes... BTW: Afaik you can not use different colors within the x...

How to show a message box ?
Hi, I would like a message box to display if the following conditions are met. Assuming column A contains codes that do not change . For example the code in column A6 is ADO and the value in G6 is less than the value in H6 which is less than the value in K6. A message box comes up with “Code ADO has reached criteria”. Being a complete newbie at VBA I would really appreciate some help. Thankyou. Select the sheet tab which you want to work with. Right click the sheet tab and click on 'View Code'. This will launch VBE. Paste the below code to the right blank portio...