i have created a xlColumnStacked chart using VBA in excel 2000. i would like to place a line in the center of one of the Points(). How can i locate a specific point, for example Points(1), so what i can then "draw" a line in the center
View 7 Repliesfinding a function/formula for determining the breaking point (Yvalue) in a graph, that doesn't necessarely equals the maximum value of that curve!
So the curve in the sample is a stress strain curve (tensile test) of a fibre and at the end it breaks and the Y value returns to 0. I would like to determine that Y value before it is 0, so the last Y value before it returns to zero.
I thought of a formula like:
display last y value before it drops with 90%...
but I don't have a clue how to write it in maths...
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?
I have an XY chart that I would like to have the user select a single data point in the chart and be able to add a comment in a table corresponding to that point. For example: if the x-values are in A1:A10, y-values are in B1:B10, the user can select a point, say the 4th point, have some code run that will enter a text string into cell C4. I can manage the code to add the text to the appropriate location, but I'm having trouble with 2 things.
First, how can I ensure that the user has selected a single point (as opposed to the entire series or the chart background)?
And second, how can I determine which point is selected? I think I need to determine the point's item number in the SeriesCollection, then offset that number of rows in my data table. But I can't find a way to retrieve that number.
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 RelatedIs there a way of determining the series that has been selected on a chart?
View 3 Replies View RelatedI have a scatter graph that has over 100 curves that im need on one graph that at the moment i putting each x and y and name in by hand in series. I have been doing it for hours and have looked round the site to see if anything similar answer on ozgrid would help but im baffled
i was just wondering if there is an easier way of doing this
i havent really used macros but iv been reading up on it can i used it to do this?
I have added the table and graph and this is how i would like all the columns to look like
its the table called graph that makes the curves that i would like to be able to add in quickly
the rest im not too fussed about.
how can i plot multiple series of values in one graph after checking some check boxes, which i have previously done, randomly?
View 4 Replies View RelatedLooking for code that returns the cell address of a series in a pre-existing graph. I have a graph that already has Source Data manually assigned to it. Is there a way to find just the cell values or location of a single Series in a SeriesCollection object? I know that in order to assign a series of data to a Series object, you do something like this:
ActiveChart.SeriesCollection(1).Values = Worksheets("My Worksheet").Range("A1:F1")
The issue I am tackling with is a graph that already has preexisting data in the Series object, and I just need a quick and easy way to find the address of this data set.
I've been trying to work out a code for a while now to plot some series in rows on a graph. The wonderful Mr Pope helped me with one before but for series in columns but i just don't seem to understand it enough to use it for this problem. the graph already has 2 series plotted (called the envelope) so i have to use seperate series to plot the data.
Basically the name of series is down column A
but the range is from E1 to Y1
and the data to be plotted on the graph which already has two series plotted in it is E2 to Y2, E3 to Y3 etc. iv been trying to use this code but failing miserably
Sub adddatatopsd(Name As Range, XData As Range, YData As Range)
With ActiveSheet.ChartObjects(1).Chart
With .SeriesCollection.NewSeries
.ChartType = xlColumnClustered
.XValues = XData
.Values = YData
.Name = Name
.ChartType = xlXYScatterSmooth
End With
End With
End Sub...........................
I want to add a user-form on a chart, which will have check-boxes that will allow me to select series(lines) that I want see and compare in a chart. Currently my line chart has 24 series (Lines) which makes the chart very difficult to view and looks very busy. How do I add list of check-boxes that will allow me to select one or multiple lines that I want to see at a time?
View 2 Replies View RelatedI have created a combination chart comprising a column and a line. The columns to the extreme right and left are half visible (I can only see half of the column).
Look at my two attachments to see what i mean.
Attachments:
Excel File: http://www.MegaShare.com/235337
Screenshot:
OR
http://img530.imageshack.us/my.php?i...icture1qi7.png
Note: I have cross-posted this thread in excelforum.com: http://www.excelforum.com/showthread.php?t=608124
I run a large simulation experiment. I have a loop plotting data in excel of a user defined area. Because of the limit of 255 series I have allowed a maximum of 250 simulations (they all need to be plotted). But the length of each simulation is free. I know there is a limit of 32.000 data points in a graph and I have this as a condition too.
If I set the data range to 100 columns and 3000 rows the graph is produced when I plot by columns. (code below)
But if I set the data range to 250 columns and 1000 rows I get the above mentioned error message. Even though I only have 250 series.
After the data is plotted it is the code below that gets the error:
[Code] .....
I have the following code:
Sub Macro5()
ActiveSheet.ChartObjects("Chart 243").Activate
ActiveSheet.ChartObjects("Chart 243").Activate
ActiveChart.SeriesCollection(1).Delete
ActiveChart.SeriesCollection(2).Delete
End Sub
However, if there is no SereisCollection(1) actually present in the chart I get an error. How can I work around this? I will need an IF statement I assume, just not sure what it will look like.
I just recently installed Excel 2007 and I would like to know if it's possible to change all data points of a chart at the same time. In Excel 2003, I would normally hold down shift while clicking on each of the data points to make a global change. However, it appears I cannot do that in 2007.
I would like to display each data point's series name. When I go to Layout on the Excel Ribbon, and click on "Data Labels", and click on "More Data Label Options", the actual Y-axis values are shown for each data point. However, I do not want this - I actually only want the Series Name, but when I uncheck "Value" and check "Series Name" instead (under "Label Contains"), it only changes it for one of the series. Is there a better way, instead of going through each and every single series to make this change?
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 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.
The following code is supposed to produce six series on an xlXYScatter chart. It produces seven with the seventh series being a repeat of the sixth but named series 7.
Sub Chart2()
Dim DataRange As Range
Dim CellString As String 'Stores a cell range in the form "AA27:AB39"
Dim CurrentSeries As Integer
Dim SeasonCount As Integer
Worksheets("Hemisphere").ChartObjects(2).Activate
CurrentSeries = 1 ............
I have a list of data on one sheet and a 'reports' page on another sheet. The reports page has several pivot tables and a pivot chart. I want the pivot chart to format the bars on the chart relevant to the series name. The series names are "R" "A" amd "G" for Red Amber and Green respectively, I want the chart to change the colour of the series so that it is the correct colour ie. "R" would be coloured Red, "A" would be Amber and "G" would be green.
Sub PivotLoader()
Dim Red As Integer
Dim Amber As Integer
Dim Green As Integer
Red = Range("H9").Value
Amber = Range("H10").Value
Green = Range("H11").Value
Range("B8").Select
ActiveSheet.PivotTables("PivotTable4").PivotCache.Refresh
Range("D25").Select
ActiveWindow.SmallScroll Down:=18
Range("B49").Select...........................
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 want to insert a text box into a line graph. I want the box locked onto a data point in this graph. The problem I am having is the graph is updated daily with more data so the point moves so I have to drag the box daily. Is there a way to fix this so it works automatically?
View 1 Replies View RelatedMy goal, albeit a simple one, is to return and store a single value within a Series. It is the first in the series collection.
Dim MyPoint As Integer
MyPoint = Worksheets("My Worksheet").ChartObjects("My Chart").Chart.SeriesCollection(1).Points.Item(1)
However, after i type the period after "Points", no autocomplete listing appears, and when the expression is evaluated, I get the error 'Runtime Error 438 - Object doesn't support this property or method'. How to accomplish the task of retrieve a single data point in a series and 2) Why the Points and Item methods don't seem to work as described in the help documentation
I have been looking for a printable overview of Excel VBA that covers the fundamentals like program structure and syntax, but have been unable to find anything other than pure examples (like many 3rd party sites) or unprintable references (such as Microsoft's help file). If there is a site that covers the fundamentals of using Excel VBA in some depth
I am building stacked XY Scatter charts and having difficulty formatting the y-axis values (which are data series point labels)
Rob Bovey's XY Chart Labeler manages this fine, but I work in a strict corporate environment which does not allow downloads. Also the file will be posted on our web as a tool for all company users to extract and analyze data from corporate databases.
I am stuck on the number formatting of the labels. My file is too big to attach; I have included the code below.
I'm sure there is something very basic that I am missing. Needless to say VBA is a challenge for me.
Option Explicit
Sub Atest()
Dim intR As Integer, intP As Long
Dim sngVal As Single, sngY As Single
Dim dtX As Date
Dim chtTst As ChartObject
Set chtTst = ThisWorkbook.Worksheets("GRAPHS").ChartObjects("Chart 18")
chtTst.Select
ActiveChart.SeriesCollection("Y Axis Values").DataLabels.Delete
If I have a graph with 2 series of data plotted, how can I display the x-axis' value when these 2 curves intersect each other?
View 9 Replies View RelatedI would like to have two series of data using the same X axis (date, formatted in months). The Y axis is in intervals of 100,000.
The first data series is historical (actual) data (i.e. Jan 2009 to August 2011). The second data series is forecast (Sept 2011 - August 2012). So i want the forecast series to start immediately after the historical series. It is a 'line with markers' chart. The key objective is that the forecast data looks visually distinct from the historial series.
Excel version: Excel 2010
OS: Windows 7
I'm using the following code to set the colours of the slices of all the pie charts in my spreadsheet based on each slice's legend's label: ....
View 9 Replies View RelatedOk, so here's the trick:
I know how to create combo or mixed graphs with line/bars on single axis, and secondary axis. I even know how to do mixed stacked and unstacked columnar charts...
But how do you depict in single view graph
a single bar (1 datapoint) with a single line (upper control limit e.g. target).?
And I don't want to to use shapes to draw the target line.
I want the target line to be automatically plotted by Excel...
I have put my values into a XY scatter graph but need to find the maximum point i.e when the gradient is zero. I am using visual basic to obtain my data and draw my graph.
View 7 Replies View RelatedI have a spreadsheet created in Excel 2003 (which is what we use at work, unfortunately).
My employees periodically take a test to ensure they have certain items memorized (or are making progress to that end). The spreadsheet rows show all 46 of my employees, and their test scores. The columns are the dates that the tests are administered. I can create a line graph based on the chart data, and interpolate these data with no problems.
The problem is that there are 46 employees! 46 lines on the same graph make for a very cluttered, hard to understand visual. I want to simplify the view by "filtering out" some of the data.
I have an additional column in my spreadsheet for each employee's work area (Area 1, Area 2, etc), and another column with data based on first letter of last name (the values here could be "A-G", "H-M", "N-S" and "T-Z", for example). I figure i could filter my line graph based on these two columns. For example, somehow select just Area 1, and reduce the number of lines on the graph to 16. Or better yet, Choose "Area 2" AND "A-M" and end up with 7 employees (and therefore 7 lines on the graph).
Here's what i have tried:
1) Select the work area column, and use the Filter, which created a drop-down list at the column heading. When i use this drop-down list, i can easily filter the data in the worksheet by Work Area, but this is not reflected in the line graph, which still shows all 46 lines. The problem was that i forgot that i had set the Calculation Options to "Manual". Setting this to "Automatic" (or leaving it on Manual and pressing F9) solved the problem, as the chart now updates when i use the filters. Calculation options are under the "Formula" tab in 2007, or in Tools -> Options -> [either calculation or formula, i forget what it's called] in 2003.
2) Create several separate line graphs in several separate sheets. I wouldn't want to assign someone else the task of maintaining a spreadsheet of such inefficient design.
Is there any possible to add a series to a graph table without having the line in the Graph. i just want to show the difference for information and not in the graph?
i have tried some different things (like having the difference in white so it is not visible, formatting the Axis etc)
See attached picture. Excel 2.JPG