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 RepliesI 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.
Attached is a chart I am trying to create. So basically, I originally used a line chart but it didn't have the flexibility of the scatter chart, but i am having a very hard time formatting the X axis. First of all - I would like the line in the middle at the 0% point to actually go to the bottom so that the x axis labels aren't in the middle of the graph. Second the x axis labels aren't showing up correctly. They should be quarters: 1Q:02, 2Q:02, 3Q:02, 4Q:02... Finally, how can I show the correlation between these two data points plotted - the R-squared. THANKS!!! Auto Merged Post;I actually was able to figure out most of what I asked. What is remaining: how do I calculate the R-Squared on the chart? Isn't there a way to do it automatically in excel on the chart.
View 3 Replies View RelatedIn the attached spread sheet I have created a scatter plot. How can I label each of the markers with its appropriate label from Column A - the Company Ticker. When I right click to show data labels it brings back the incorrect column. Also is there a way once the labels are brought in to put them in the circle and so that the chart doesn't look too clumped together.
View 2 Replies View RelatedI need to plot multiple series in a scatterplot. The problem is that the number of series is user-defined. And also, the number of data poitns in each series is different. I've looked it up already, but a lot of the examples i've seen don't account for the fact that I need to run through a loop and add series into my graph.
In other words, i need a way to select a certain range each time it runs through the loop, and add the data as a series into the chart. Also, the user enters how many series there are, so I use that as a counter
What I want to achieve is a script that would create a new scatter plot of multiple series from a selected block of cell. I found a thread that was similar but what is different from my data is that my x values are different for each series.
The format of the data is in the following format, the first series will the first x,y pair, the 2nd series would be the second x,y pair. I don't know if it'll be an issue but one thing is that the date might be different lengths. For instance, the 3rd series, only has 4 data points, whereas the other series contains 6 data points?
x0.200.400.600.801.001.20
y5.002.501.671.251.000.83
x0.100.200.300.500.701.30
y5.002.501.671.000.710.38
x0.100.300.400.50
y6.672.221.671.33
So essentially, is there a way I can have the script say, select your range, and then graph the selected range as a scatter plot?
I need to label points on an XY scatter plot with a different set of label points than those provided by simply assigning data labels (don't need series name or x and/or y values displayed but an additional field of data). I can't use an add in (work machine) and would like to do this without using macros.
The project has 4 fields
A B C D
plot# type x y
I need to label the XY points for each series (type) by their plot #. The attached image/file shows what I'm looking for (added plot # labels manually for sake of display, in reality there are >2000 plots so I cant do it manually.
I have a spreadsheet in which the amount of data is not predictable. There are always a different number of rows and columns in this spreadsheet. I need to be able to grab the data that is in it starting with B1 and going to the last row and column with data and put it into a scatter plot. The code that is currently in my macro is as follows:
ActiveSheet.Range("B1").CurrentRegion.Select
ActiveSheet.Shapes.AddChart.Select
ActiveChart.ChartType = xlXYScatterSmoothNoMarkers
ActiveChart.SetSourceData Source:=Range("ForChart!$B$1:$J$1000")
Note: ForChart is the name of the sheet where the data is housed; I have other sheets in this macro.
I know that the last line is the problem. While I figured out how to select only the data I need, when it goes to put it into the chart, it's still using an absolute reference and I don't want that. There are sometimes more columns than J and fewer rows than 1000. I want that data range to be whatever CurrentRegion selected. Is that even something that Excel can do?
I am attaching the Excel File along with this mail. In chart there is data points have been mentioned in scatter plot like (38,15 etc) Instead of these points I want the names that I have mentioned in column A2:A9. I used to make lots of charts related to this and it is very diffilcult for me to manually punch all these names and most importantly the thing is that I cant download the software available on http://www.appspro.com due to some issues can you give me the VBA code for this which will automatically paste these names only I have to change is the data range and its very urgent................ASAP
View 7 Replies View RelatedI now have a XY scatter graph with 109 individual points (representing schools) and a macro that tells you the plot details when you hover over. It's ok if you want to know which school is down the bottom but if you don't know where the desired school is in the first place, it's a lot of trial and error before you find it.
What could I look to add that gives the user the option to have a single data point highlighted (on selection?) so they don't have to manually look for it?
I'm using Excel 2008 for Mac & cannot figure out how to add a data label to an XY scatter plot that comes from a 3rd, separate column.
I have 3 columns of data: (A,B,C)
Labels, X values, Y values
When I select the Data Source for the Chart, there is a greyed out box for Category X axis labels, which is where I remember such information going in PC versions of Excel I used to use.
From the formatting palette, the only options to select for labels add the values of column B, but I need the reference from column A.
I'm not familiar with macros or visual basic. How to add these labels? This is Mac, Excel 2008.
I have a text file which is attached as "rawdata". It contains records of something (let's call it temperature) at different times on different days. My goal is to display a graph of temperature versus time so that I can visually analyze trends. I have hundreds of these files, all of different lengths. it is very important that I automate this process as much as possible.
Detail: (Here I describe what I have done so far; if this is inefficient or unnecessary, feel free to tell me) I open Excel 2010, click File, Open, and select the file that I want to parse. It is a TXT file, so the Text Import Wizard comes up. For step one, I select Fixed Width. I select File Origin: MS-DOS (PC-8). On step 2 of the wizard, I create column break lines to place all dates in the far left column. The next column contains the first column of numbers before the first dash (-). The next column contains only the dash - I will later select "ignore this column" to eliminate them. The next column contains the time stamps. I continue adding column breaks in the wizard until all of the data are parsed into columns in the same manner.
In step 3, I format the first column as "date (DMY)". The columns with the dashes I select "do not import". Everything else is "general". I click "finish", and the resultant workbook is attached, called "import".
Now, as to what I want to do: I want to display the "temperatures" as a graph vs a date/time axis. The reason I find this difficult is because the temperatures and times are not in neat columns, but are in 4 columns that go in a left-to-right and top-to-bottom progression and are broken up every few lines. (I am interested only in numbers that are displayed immediately to the left of a time-stamp. Therefore, the "record #"s should be ignored. We can delete the rows that say "record #" if can be done automatically.)
I need to plot various data on top of stock charts.
This data could be irregular in date: it could be weekly, or simply random.
To plot this data by itself requires a Scatter Chart.
From what I know so far, you CANNOT DO THIS.
However, I suspect this could be done if I build a Stock Chart from scratch using a Scatter Chart.
Error bars can be used to make the tails, however, I don't know how they built the body bar which has the characteristics of a bar (border, and interior.)
But since I don't really need those two characteristics, I just need a wider error bar line that is provided in the chart edit window. I'm guess through a macro, there are wider line widths assignable.
I got the following table and it generates a random number either 1 or 0 (win or loss)
trade win/loss Account value
0 10,000
rand() 1 0 9,800
rand() 2 1 11,000
rand() 3 1 12,000
rand() 4 0 11,500
Each time I press F9, the Account value changes because the win/loss is generated randomly. Assuming, I press F9 100 times, how do I create a scatter plot chart with a best line fit for all the account values. I know how to create the scatter plot but how do I save all the values everytime I press F9.
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.
I have the scatter plot below and I'd like to get the team names to show beside each point.
View 3 Replies View RelatedGiven 100 samples, to calculate: numbers of hours spent on study is dependent on numbers of hours spent on social networking site.
x (social networking site)
y (study)
x^2
xy
6
4
36
16
24
[Code] .......
SUMMARY OUTPUT
SUMMARY OUTPUT
Regression Statistics
Regression Statistics
Multiple R
0.571423290877713
[Code] ....
So, I've got the coefficient of correlation equals to 0.57, how can I plot my scatter graph?
I have a list of interest rates and durations and was hoping to be able to add in a third dimension of data which would either increase the size of the plotted points or change their colour depending on the rating. So have the normal scatter plot then save me from editing each point manually.
i.e.
A1
B1
C1
D1
Interest Rate
Duration
Rating
3%
3.5
A+
[Code] .........
I have a scatter plot with 150 points closely grouped. Enabling the label option clutters the graph. A cleaner way would be to hoveri the mouse over a data point and pop-up a box displaying it's value (x,y) as well as a label contained in an adjacent column.
Details:
The default functionality is that when the mouse hovers over a particular point the yellow pop-up box appears withData set label
Data point name "x"
Data point (x,y)
and one can choose to show "label-name-point" or just "point" by accessing the Tools-Options_Charts menu item. What I would like instead is to hover the mouse over a particular point and get the yellow pop-up box with the following functionalityData set label
Data point name "label"
Data point (x,y)
where the label is contained in an adjacent column in my data spreadsheet.
I read a post by Andrew Poulsom that stated it is probably not possible to customize the yellow pop-up baox and then offered some vba code to allow a mouse click to bring up a label. I was wondering how to implement this code in the spreadsheet. Also, does the graph have to be embedded in the worksheet "as object in", or can I use "As new sheet"?
I need to create some vertical scatter plots ie where all the y values are in the same line, and also to put on the mean and standard deviation error bars, like the example I found below. I have managed to plot the scatter by giving all the y values in each group the same x value, but I don't know how to proceed.
View 4 Replies View RelatedCurrently we have data that is plotted to a scatter plot along with an already defined line. Next, with a straight edge we attempt to draw a line parallel to the already defined line through the scatter points of data. We basically find two points that give us the best fit to the original line. What I attempted to do was calcualte the slope of the original line and then the slope of each combination of two points in the data set. I then subtracted the slopes, found the minimum difference and thought that would give me the line most parallel to the original. This doesn't seem to work. I'm at a loss, any advice?
View 8 Replies View RelatedI have two different data sets that I have plotted using XY(Scatter) w/ smooth lines. Is there a macro or sub that will calculate the volume or area between where the two data sets cross?
View 3 Replies View RelatedI need a way to make a customized legend for a scatter plot, is there any way to do that through VBA? I have a scatter plot with some points highlighted green, and some points grey depending on the user selection and need to be able to create a legend based on what the user selects (e.g. Green - Category B, Grey - Category A, etc.).
For some reason I can't get Excel to rename points on the scatter, so I feel like there is only one option: create custom images and align them correctly with labels on the plot.
What I'm aiming to be able to do is have a scatter plot that displays date labels for each point as well as distinguish the points by year by using different colors. I found a VBA code for scatter plot date labels and I also realize that (for me at least), the easiest way to distinguish the years by color would be to separate them with a logical statement and let the chart do the rest of the work by categorizing them as separate data ranges. However, I tried to do this with the VBA code that I found and it expectedly did not work. I'm not sure if it's an easy fix to just add more "xvalues" columns in the code or if what I want to do here is even possible.
Format and Code for the Date Labels:
Date Time
Rainfall (mm)
24 hr RDII Volume (L/ha/day)
[Code].....
how to attach labels with names to each point in a scatter plot
I have two ranges of cells containing names of chemicals, call the first one "Product" and the second one "Byproduct".
Also, there are two other ranges of cells, called "Productvalue" and "Byproductvalue". These cells contain the numerical values of products and byproducts. How can I plot both the products and byproducts in the same graph using xlBarClustered in different colors?
I have a couple of points, and when i do a scatter plot with connected by smooth line plot, it seems like excel does a bezier splines interpolation. I am wondering am i able to extract the data plots from the data chart?
for example if i have
X - Y
0 - 5
180 - 0
360 - 5
I would like to have all datas of Y from X (0 - 360 degree every degree).
I am supposed to make an xy scatter graph like the one drawn of paper. The average point need to have a "cross" around it. So far I can just plot the points. I have added the average point as secondary series. I just need to put the "+ sign or cross around" it.
View 1 Replies View RelatedI have a series of X-Y Data (0-10 in both axis) that I want to overlay on an area chart to show outliers outside of an accepted condition. I have been able to create the area chart and the scatter chart separately but when I try to combine them the axis gets messed up. I tried using a secondary axis but I am not that skilled
I have attached the data and my feeble attempts at creating this chart.
I'm trying to find a way to alternate between two colors (white, grey, for example) in a stacked columns plot. Each stacked column should begin with a grey layer then white, then grey and so on. These plots will be created and changed frequently so I cannot manually do this every time.
View 1 Replies View Related