Dynamic Bubble Chart - Data Point Naming
Feb 16, 2007
I am looking to create a dynamic bubble chart. To do this I am using offset to create the series formula which without listing all the data names turns out to be something like this (where 297 will adjust to the number of rows with data): =SERIES(Project!$A$5:$A$297, Project!$J$5:$J$297,Project!$P$5:$P$297,2,Project!$Q$5:$Q$297)
My problem is that by creating the chart in this fassion I am unable to get a unique name for each data point. For example, if row A looks like this,
A5: 1
A6: 2
A7: 3
each data point will be named 1 2 3. How do I either create a new series for each row dynamically or get the specific name from column A to associate with the correct data point?
View 2 Replies
ADVERTISEMENT
Jun 30, 2009
I would like combine (overlay) a bubble chart with a connected point scatter chart. I understand that, without VBA, this is not possible. However, I understand that, by using VBA, the markers of a scatter chart can be configured as circles with their size proportional to values in a specified column. This pseudo-bubble chart can then easily be combined with a connected point scatter chart.
My question is: does anyone have any VBA code to share that shows how to configure a scatter chart as the type of pseudo-bubble chart described above.
View 2 Replies
View Related
Nov 27, 2011
I am looking for a creative way to display a pie chart within a data point marker of a line chart.
My database has 3 value columns, Type1, Type2 and the Total (Type1 + Type2)
these are recorded per day (Date, in Column A)
I have a line chart that displays the total by date, but I want to find a way to display the percentage split of a particular day by type.
I was thinking to load the chart image into the Data point marker, but i don't think that is the best way as the data is updated daily and I would have to do it each day for a few line charts.
The other way I was thinking about was to have a generic Pie chat in the Line chart (Maybe in a corner) and the pie would update depending on way date series was selected or Mouseover'd)
the way that I am approaching it at the movement (Not the best way and by far not the coolest way. Is to have a list of all the dates in a column next to the Line chart and using some VBA, what ever date is selected in the column the pie chart displays the corresponding data. But eh challenge is that when there is alot of dates, I am going to be scrolling up and down.
I am using Excel 2010, but I cant not use the slicer's as the other users do not have 2010, they have 2007.
View 1 Replies
View Related
Jun 28, 2009
I would like to combine / overlay a bubble chart and scatter chart with straight connectors.
I understand that, without VBA, it is not possible to combine a bubble chart with a scatter chart.
Unfortunately, I am a VBA newbie, and so I cannot write my own code (though I can usually adapt code to my specific environment).
My question is: does anyone have VBA code to share that will convert a scatter chart series (x,y data in 2 columns) to a bubble-style chart (bubble radius in 3rd column)?
View 2 Replies
View Related
Sep 27, 2007
create a Quadrant Bubble chart. I have found examples of using scatter charts but unfortantely those charts do not satisfy what I am trying to accomplish.
View 5 Replies
View Related
Jun 20, 2013
Showed a line chart using 2 vertical axis where the first was a single line along the graph (ie. 42%) then the second data series was a single data point that was above or below the line which easily showed if the result lied above or below the threshold.
How to get a straight line across the body of the just to be used as the theshold line?
View 1 Replies
View Related
Apr 22, 2014
Pivot Chart. I would like to set up something to where a user can click on an individual value on a pivot chart (currently a line chart set up with 4 data series) and somehow display some underlying data. I have a lot of information stored in a data worksheet that I can't display all at once, but if a user sees a questionable data point, he/she can click and learn more about it from source data, or even a new query of the data worksheet.
I am using Excel 2010
View 2 Replies
View Related
Aug 2, 2013
I created a bubble chart whose x-axis and y-axis show every 10th value between 0 and 100 (i.e. 0, 10, 20, 30...). The axes intersect at (50, 50) to create 4 quadrants.
I want to erase/hide the values on the axes (i.e. the axes will no longer show 0, 10, 20, 30... and at the same time, the bubbles will still be in the same place) to create 4 blank quadrants and make my bubble chart easier to look at.
View 4 Replies
View Related
Nov 26, 2013
I have the table: chart1.png
If the bubble size is 0-5, I need the bubble color to be green
If the bubble size is 6-15, I need the bubble color to be yellow
If the bubble size is 16-25, I need the bubble color to be red.
So, right now my chart looks like this: chart.png
As you can see the top bubble is the right color, but based on the bottom bubble value, it should be green.
How do I make this change colors dynamically based on the bubble size value?
View 1 Replies
View Related
Apr 10, 2012
I have the Lat and Long for each of several US cities. I want to use these to create a bubble chart that plots the cities and which I can place over a US map picture.
However, I have the problem that, because of the curvature of the earth, the city locations form the lat and longs are not tying with the map.
how to adjust the lat and longs so that they will correspond to the typical US map?
For example, the base lat and longs for Denver are 39.5742 and -104.8588. What formulas can I use to modify these to match the standard map projection?
View 9 Replies
View Related
Apr 15, 2007
I’m trying to take an existing bubble chart, paste new data into the sheet,
then march sequentially from row to row, adding specific cells within each as a new series in the chart until I run out of rows. Below is my latest attempt (not working of course).
Sub setseries1()
Range("A1").Select 'select upper left cell to start
Do Until ActiveCell.Value = "" 'stop when you run out of rows
ActiveChart.ChartArea.Select
ActiveChart.ChartType = xlBubble
With ActiveChart.SeriesCollection.NewSeries
'use these values for the next series
.Name = ActiveCell.Offset(1, 0).Select
.BubbleSizes = ActiveCell.Offset(0, 1).Select
.Values = ActiveCell.Offset(0, 1).Select
.XValues = ActiveCell.Offset(0, 9).Select
End With
'go to next row, and repeat
ActiveCell = ActiveCell.Offset(0, -1)
Loop
End Sub
View 7 Replies
View Related
Jul 12, 2007
I have a worksheet where I am collecting data and a seperat Chart. The first part of this code which adds a new row in my table works perfectly fine, but the latter part (based on a macro recording) does not. I need to specify the relevant row for the new series in the bubble chart. The colums are of course the same.
Private Sub CommandButton1_Click()
Dim LastRow, LastRef As Long
'Works!
ThisWorkbook.Worksheets("Projektradar - input").Select
Range("Q3").Activate
LastRef = ActiveCell.Value
Range("Q3").Value = LastRef + 1
View 4 Replies
View Related
Jul 15, 2013
At the moment I've got a chart with 3 series lines on it and each line has 12 points. I need to select the 12th point on each series in the chart and create a label for it. The 3 lines are Budget, Forecast and Actual and often the Forecast line hits the same points as the Budget line in the chart and every time I try to find a way to select the last point on the Forecast line, I end up selecting the budget one because it's the one in the foreground.
I can select the forecast line with the keyboard arrows, but not the last point.
I also try to zoom in to get it but one of my charts has exactly the same forecast line as budget line and I still need the forecast line because the forecast will change in future.
I can delete the budget line then add it back, but this is difficult and one I'd have to repeat many times because I'm always making graphs and labelling the final point in a series is something I'm going to have to do more often.
How do I select the last point on the forecast line if it's directly behind the budget line?
View 4 Replies
View Related
Dec 19, 2006
When you hold your mouse over a bubble on a bubble chart, it typically displays the x value, y value and size value. Is there any way to add other captions to display? For example, if my chart is based on sales data and I'm plotting a point based on ease of probability (x axis) and dollar value (y value)....I'd also like to see the initials of the salesperson assigned to that prospect when I hover over the bubble?
View 2 Replies
View Related
Feb 21, 2014
how to get a line in a graph to become a curved line between 2 data points in 10 point chart. I the pictuer attached I am trying to get the line to curve at the second data point 5(ms) and the third data point at 80(ms). The aftr 80 the line become staright again.[IMG][/IMG]
View 3 Replies
View Related
May 5, 2008
I have to create a chart XY Scatter plot in excel. I have to differentiate the data points color based on the another column "category". Now I can change the data points color manually. Since the number of points is huge, is it possible to color the data points based on a column?
View 5 Replies
View Related
Aug 30, 2008
Let Sheet 1 have data in 3 columns to support a standard bubble chart
Col A: X Values
Col B: Y Values
Col C: Bubble Size
Id like to be able to color the bubbles according to the Y values, whether the Y values fall within the following ranges
Green for Y <=2
Orange for 2<Y<=5
Red for Y > 5
Im assuming some straightforward VB code is in the works but Im not quite there yet
View 4 Replies
View Related
Jun 21, 2009
I would like to replace the blue bit of this Dynamic Named Range (DNR) with an INDIRECT formula in order to modify the starting point for the DNR: =OFFSET(DATA!$C$60,0,0,20,1). Unfortunately, I can’t seem to get my INDIRECT formula to work in order to use it to replace the blue bit above: INDIRECT("'"DATA"'!&ADDRESS(MATCH('SHEET1'!AC8,DATA_Date,0)+22,3)"). The orange bit of the formula above returns a value of 60, therefore the ADDRESS formula should return $C$60, that can then feed into the INDIRECT and act as the equivalent of DATA!$C$60. But it doesn’t.
View 2 Replies
View Related
Jul 26, 2012
I am trying to format the colors of the bubbles on my bubble chart to Green (>5), Yellow (4-5), or Red (<4) based on the value used to create the bubblesize.
The problem that I am having is that .BubbleSizes is property of type String, not range like .xValues, or .Values. So instead of pulling in the value, I am pulling in the reference and getting a "Type Mismatch" Error. The reference of my data for .BubbleSizes is $D$5:$D51. Each row is it's own series and the list is dynamic, so I need to be able to support future rows without a lot of maintenance.
Here is the code that I have so far.
VB:
Sub DataSeriesFormat()
'
' DataSeriesFormat Macro
' Format Data Series based on defined parameters for Green, Yellow, and Red. Created by Derek Steinmetz 7/24/12
'
Dim x As Integer
Dim val As Variant
Dim Green As Range
[Code] ......
View 1 Replies
View Related
May 23, 2014
I am new to excel and to the bubble chart function and need creating one for a presentation.
I was asked to do a bubble chart to show the open and click to open percentage by subject line
E.g.
Subject line: Win 20% open, 23 % Click to Open, Getaway 20$ Open, 21% CTO etc., to include an industry average for open and CTO in the chart.
X axis: click to open and Y axis: open
I tried the following in the picture and it doesn't look right.
Capture.JPG
View 1 Replies
View Related
Nov 9, 2006
Im trying to create a simple 3D Bubble Chart macro, and as most people who've never dealt with this before (i think), i've hit a wall with BubbleSizes. I've attached the file im trying to get to work. The VBA is quite simple but an error occurs at BubbleSizes. I've read that BubbleSizes takes a different reference style than xValues and such, but haven't had any luck trying to change that. For those who just want to see the code without getting the file:
Sub EmbeddedChartFromScratch()
Dim myChtObj As ChartObject
Dim rngChtData As Range
Dim rngChtXVal As Range
Dim iColumn As Long
' make sure a range is selected
If TypeName(Selection) <> "Range" Then Exit Sub
' define chart data
Set rngChtData = Selection
' add the chart
Set myChtObj = ActiveSheet.ChartObjects.Add _
(Left:=250, Width:=375, Top:=75, Height:=225)
With myChtObj.Chart................................
View 5 Replies
View Related
May 16, 2006
I am making a simple database worksheet for the junior. The first thing we need to re-define the new "size" of the database to do some future calculations. I write some code in workbook open to initiate the name area:
Worksheets("Inventory").Activate
maxrow = 0
maxrow = Range("a7"). CurrentRegion.Rows.Count
'Range("A7").Select
Range(Cells(8, "N"), Cells(maxrow, "N")).Name = "tbl_left"
when the junior re-open the workbook after closed, the size of the database
does not growth to a new area even junior add rows of data before close. It seems the currentregion does not work, I've tested it in VBE by press " F8".......
View 5 Replies
View Related
Oct 12, 2013
I am having a hard time creating my ultimate gradebook. Right now, I am able to hide a SPECIFIC table on a SPECIFIC worksheet using the following code on a macro button:
VB : Sub HideRow()Range("Table2[#All]").EntireRow.Hidden = True End Sub
The problem is that this sheet will be a template and as a new user inserts a new sheet, I would like to copy and paste this template to each new sheet. Since the Table values change with each new sheet, code wont work on the new tables. How I can name each new sheets table "Class Data", and the macro button to hide will work on each respective sheet?
View 2 Replies
View Related
Oct 12, 2006
how to name a chart? I've been accessing charts in my VBA scripts using chart names, e.g. "Chart 4". I've been determining the chart name by recording a macro and selecting the charts. But, is there a way to either:
1- change the name of a chart (as you would name a pivot table)
2- determine the name without having to select the chart in a recorded macro
3- reference multiple charts from the same worksheet without using their names
View 4 Replies
View Related
Jan 30, 2014
make it working, tried from online thread but unable to get it done.Dynamic.xlsx
View 5 Replies
View Related
Apr 15, 2014
I need to name a dynamic range of cells. The only constant is the column - H, and the heading "MRC".
MRC column in a table represents an array formula. Unknown is the row where it is going to show up and the number of rows that this array formula will take. I need to name this range (active cells based on the array formula) but do not know what row does it start with and how many rows will it take.
It is not the last table in column H either but there are 2 empty rows before the next table.
Trying something like that...
=========================
Set aCell = Range("H:H").Find(What:="MRC", LookIn:=xlValues, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False)
If Not aCell Is Nothing Then
aCell.Offset(1, 0).Select
========================================
That's how I select the first cell in the range. Not sure how to select the whole range and name it ..
ActiveCell.End(1xDown) ?
View 3 Replies
View Related
Oct 13, 2009
I have a follwing data:
Sheet1 ABCDEFGH2Days12345673Values2040550000 Excel tables to the web >> Excel Jeanie HTML 4
I have plotted a line graph based on the above table; but I am getting zero values also plotted. since these Values are linked to some other workbook I can't delete the cell values also.
How can we avoid Zero values on a graph; Also is there any approach, that my graph should get automatically updated whenever the linked values are updated.
View 9 Replies
View Related
Aug 31, 2006
I have created a chart that automatically expands as more data is added. This works great by using named ranges in my Chart's Series' Values and Axis Label fields. The named ranges use the Offset function.
The problem I am having is applying this same technique to to same chart to vary the number of series that I want to chart. I created a name range, using the offset function. But when I add the name to my Data Range formula, it will not stick. It changes the number of series displayed correctly when I add the name. But after I click OK on the Source Data dialog box, the data range reverts back to the address supplied by the name range variable; it does not retain the name range variable itself. Can Chart Data Ranges not contain a name range variable, especially when name range variables are used in the series formulas?
View 5 Replies
View Related
Jul 27, 2007
In a workbook that I am working on, a macro runs to import data onto 9 separate sheets. The data is essentially x- and y- coordinates of a plot profile from another program. The problem that I am running into is that the number of data points can vary each time the data in imported based upon how the data was acquired.
If I were to put a drop-down list (Method1, Method2, Method3, etc.) on the Instructions page that I have for this workbook and have the user select which method was used prior to running the macro, could I then incorporate some sort of IF statement in the macro to vary the Source Data Series for the charts that I have created? Right now, I have the Source Data covering the scenario with the longest list of data points so when fewer points are used, the graph looks essentially useless.
View 5 Replies
View Related
Oct 25, 2007
I have a chart with Data Series being dynamic ranges. There is only one series and x & y each have a dynamic range. I need to be able to include Series Name as a dynamic range.
For example. I have 3 rows of values with 3 columns.
Col 1 Col 2 Col 3
Name X-Val Y-Val
A 1 3
B 4 6
C 8 0
I now need a chart that has 1 series with a x range of col2 and a y range of column 3 but a corresponding name equal to Col 1. So when I move my mouse over each point I get the series name. E.G. I hover over poing 1,3 I show the name as A. Or I hover over point 8,0 I get C.
If it isnt possible using the ranges the way I am, then I would be looking for a possible solution. I am sure I could do this by some sort of loop but not quite sure given the number of rows constantly changes.
View 7 Replies
View Related
