Scale Chart Series To MIN & MAX
Sep 26, 2007
I have a Scatter chart that plots a simple XYScatter plot. The data for this plot can be very wide ranging, and for that reason, I use AutoScaling to scale the axes. Nine times out of ten, it works just fine, but it seems that when the X data ranges from about 5200 to 7200 +/-, the autoscaling does not calculate well (example attached).
Autoscale range will go from 0 to 8000, leaving a large area of unplotted graph space.
I thought the original issue was that I may be plotting zeros or blanks, but I've eliminated that problem. I have yet to find another range of values that causes it, but I'm sure there's some. What is it about the way that Excel calculates its AutoScaling that I'm missing?
What do you all recommend to eliminate this problem? Manually scaling it is not really an option in this case, as I want this to be completely transparent to the user for ANY range of data.
View 9 Replies
ADVERTISEMENT
Aug 4, 2014
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] .....
View 1 Replies
View Related
Dec 22, 2011
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.
View 4 Replies
View Related
May 31, 2008
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?
View 4 Replies
View Related
Aug 21, 2006
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 ............
View 9 Replies
View Related
Jun 24, 2008
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...........................
View 2 Replies
View Related
Feb 16, 2012
I am using a VBA macro which performs a Yahoo webquery to obtain stock data. After the data are downloaded, a chart is updated with the new data.
The current macro updates the scaling on the price axis just fine, however, if I change the time frame the x axis, i.e, time/date axis is either spread out to far or jammed up.
The code for re-scaling the the y axis is shown below:
Code:
Sub UpdateScale()
Dim ChartVar As Chart
Dim lMax As Long, lMin As Long
On Error GoTo ScalingProblem
'Assigns the values in the Min and Max ranges to variables.
[Code]....
Any similar sub that would also rescale the x axis as needed when the data intervals are changed?
View 6 Replies
View Related
Sep 21, 2011
I 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
View 5 Replies
View Related
May 15, 2008
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 Related
Dec 14, 2007
how to get the min value scale value off a graph when .MinimumScaleIsAuto = True?
The reason I ask is that I want to make my baseline for the graph that min number so that names from the x axis are not sitting in the middle of the graph. Incognito Auto Merged Post;I feel real bright...just playing around I found my answer...
With .Axes(xlValue)
.MinimumScaleIsAuto = True
.MaximumScaleIsAuto = True
.MinorUnitIsAuto = True
.MajorUnitIsAuto = True
.Crosses = xlCustom
.CrossesAt = .MinimumScale
.ReversePlotOrder = False
.ScaleType = xlLinear
.DisplayUnit = xlNone
End With
View 2 Replies
View Related
Dec 10, 2008
I am using Jon Peltier's worksheet_change event code to try and automate chart axes.
Private Sub Worksheet_Change(ByVal Target As Range)
Select Case Target.Address
Case "$AG$5"
ActiveSheet.ChartObjects("Chart 2").Chart.Axes(xlCategory) _
.MaximumScale = Target.Value
Case "$B$3"
ActiveSheet.ChartObjects("Chart 2").Chart.Axes(xlCategory) _
.MinimumScale = Target.Value
Case "$AG$7"
ActiveSheet.ChartObjects("Chart 2").Chart.Axes(xlCategory) _
.MajorUnit = Target.Value
Case "$L$3"
ActiveSheet.ChartObjects("Chart 2").Chart.Axes(xlValue) _
.MaximumScale = Target.Value
Case "$N$3"
ActiveSheet.ChartObjects("Chart 2").Chart.Axes(xlValue) _
.MinimumScale = Target.Value
Case "$AH$7"
ActiveSheet.ChartObjects("Chart 2").Chart.Axes(xlValue) _
.MajorUnit = Target.Value
Case Else
End Select
End Sub
However, I have some of the cells setup as formulas....but worksheet_change apparently only updates values when manually changed.
View 9 Replies
View Related
May 15, 2008
I am trying to manually format a few graphs in my sheet. I, however, leave the major units of the axes to be automatically calculated. The code that I use is given below:
ActiveChart.Axes(xlValue).Select
With ActiveChart.Axes(xlValue)
.MinimumScale = Range("Min").Value
.MaximumScale = Range("Max").Value
End With
Now what this does is that it sets the maximum of the scale exactly equal to maximum of the range being plotted and hence a few parts of my graph overlap with the border of the plot area. Is there any way in which I can round the max and min scale of y-axis to the next highest and lowest major unit respectively or may be add and subtract a value proportional to the major unit of the y-axis. I cannot add a constant in the code given above since I that does not suite my requirements.
View 2 Replies
View Related
Jun 5, 2014
I have some code for changing the scale of my chart axes so that when new data is inserted into the column which the chart sources from (say column B), the axis scales on my chart will automatically adjust (as the Excel algorithm sometimes leads to charts which are a bit squashed). This code works fine, and I have included it below.
I have then tried to introduce an ActiveX listbox so that when you select an item in the box, the reference number generated (1,2,3...etc) then determines which data series is inserted into column B using a vlookup function. However, when I do this, the original VBA code for automatically adjusting the chart axes no longer works.
Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
With ActiveSheet.ChartObjects(1).Chart
[Code].....
View 4 Replies
View Related
Jun 30, 2008
I have a chart with 3 data series. The series are located in columns A, B and C respectively. Series I is a general number anywhere from 0 to 100,000. Series 2 is also a number, but is is devided by series 1. So, if series 1 was 100,000 then series two would be 25,000/100,000 which is 0.25. Series three will always be numerator of series 2 or 25,000 in this example. The numerator, or the 25,000 will never change, so, in my example, series three will be a straight line across the chart because it is always 25,000.
In my example, the .25 is plotted on the left value axes, and series one is plotted on the right value acccess (secondary axis). Series three is just a line in the middle.
My problem is that sometimes the line, series 3, doesn't match the values in both the right and left values axes. So, if series one is 48,000, then series two would be 48,000/48,000 = 1, and series three would be 48000 - the straight line. The third series should be a line touching the 48,000 on the right and the number 1 on the left. It does touch the 48000 on the left, but is below the number 1 on the left.
View 9 Replies
View Related
Jun 19, 2008
I want to use a macro to change attributes of a series in a chart. Unbelievably, if the series has been pasted into the chart from another chart, and although the macro can address the series and even return values (eg name) correctly, the selection simply cycles to one of the original series on the chart.
View 3 Replies
View Related
May 16, 2013
I want to automate the Display labels in my Charts using VBA Coding…
I would need to use this small code in Charts with different Scales as some of the Scales in my Data are in a very large Range close to Trillions so its not possible to have these as the Primary-Y-Axis Scale and therefore I would need to use the in-built Display label option which Excel provides..
However, I always prefer to have the scale in the Range of Thousands so the Max I can have in the Scale can be 10,000 so if there is any Number more than that or the Scale is going above than I would prefer that this gets auto-calculated to Multiply with the remainder and show appropriate Scaling.
For Ex:In this example My largest Number is 4500000000 which is 100 M..So I would want the scale to show 4500 and the Display label as X Millions..
So The Display Label would be different for different Ranges but the Max Scale preffered would be 10,000.
View 8 Replies
View Related
Feb 1, 2013
How can we convert time on a scale of 100 to a scale of 60?
View 3 Replies
View Related
Oct 2, 2012
I'm attempting to create a dynamically built donut chart using VBA, but I'm having trouble inserting a variable to be used as one of the values.
The chart graphs 3 data points:
ASC GP - Located in cell T4
ACC GP - Located in cell AE4
ACT GP - variable called LocSumActGP
The code snippet I assumed would work looked like this:
Code:
.SeriesCollection(1).Values = "='Location Summary'!$T$4,'Location Summary'!$AE$4," & LocSumActGP
But it doesn't seem to like that.
View 2 Replies
View Related
Jan 24, 2007
I have the following code which puts a series onto a chart
With Charts("Test Chart")
.SetSourceData Source:=Sheets("Data").Range("A1:B" & Nreadings), PlotBy:=xlColumns
.Axes(xlCategory).MinimumScale = 1
.Axes(xlCategory).MaximumScale = Nteeth
.Axes(xlValue).MinimumScale = Miny
.Axes(xlValue).MaximumScale = Maxy
End With
I would like to add a 2nd or third series onto the same chart using the same scale etc. How do I do this in VBA code? Whenever I add another line to add a series it just overwrites the first series I have shown here.
View 9 Replies
View Related
Sep 18, 2008
I have calculated data (the column and row headings are calculated as well) similar to this for which I need to create an automatically updating chart.
******** language="JavaScript" ************************************************************************>Microsoft Excel - test.xlsm___Running: 12.0 : OS = (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)boutA1=
ABCDEF1 BalloonsFlagsCushionsPillowsStickers2Blue136013Red352104Green703245Yellow075056Orange558327White26073Sheet2
[HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name box
PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.
The chart should update if the data is filtered; so if a user doesn't want to see the 'orange' row they can filter the data accordingly. There is a finite number of rows and columns in the range but sometimes there will be less data. I have tried to convert the data to a table and that works quite well except the legend keys persist: so if there is no data for pillows and stickers the legends still appear and have to be removed manually. Also, there seems to be a bug in Excel 2007 whereby the removal of a chart linked to table data causes a persistent error message to appear.
I know that I need to created a named range for the data series using OFFSET, and I have also read that I need to refresh this formula using VBA but I am now very confused and none of my attempts quite work.
View 9 Replies
View Related
Dec 13, 2006
my problem is:
I have a whole list of data which changes according to the users preferences
It comprises of a NAME, X Value, and Y Value and the addition of X and Y. (these all have auto filters on them)
The user inputs under these fields, as many rows as they wish (which will be around 20ish)
I want it so when i click a button "CREATE GRAPH"
it selects the highest 10 values (if there is 10 values, if not then all the values) from the 4th column (X+Y) and then creates a graph. i want each row to be a new series...
View 9 Replies
View Related
Jan 3, 2008
I am trying to create a chart that will allow the user to add/remove series to compare with each other. When adding a series, Excel gives it a name (e.g Series1) the macro is then set to rename it to a particular cell, however the name Excel gives the series varies depending on the order in which the series are added.
ActiveChart.SeriesCollection(SeriesCollection.Count)
instead of
ActiveChart.SeriesCollection(1)
would work collect the last series added, which is what I want, but when I apply this I get an run-time '424' error.
View 2 Replies
View Related
Feb 7, 2008
I hope this passes muster. Anyways, I wrote some VBA code to handle an embedded chart in my workbook. I named the chart "Data" but i'm getting some unexpected trouble from VBA. Last time I worked on this set of code, it ran fine. When I try to run it now, it is throwing up an error: "1004 Unable to set name property of series class" The .name function is not working correctly but everything up till that point is working correctly. I have 5 sets of data in this chart also ....
View 9 Replies
View Related
Jun 6, 2014
I want to use 4 toggle buttons to be able to add and remove 4 different series to a chart. When the toggle button is clicked, a specific series is added to a chart, and when it is unclicked then the series would be removed.
The problem I am running into is that unless I add and remove each series in a specific order, I will get an error because I may be trying to remove a series whose series number is not on the series collection in the chart.
for example: if I remove series number 2 with the toggle button for that specific series, then the toggle button for series number 3 will remove the old series 4 (which is now 3), and the toggle button for series 4 won't work because series 4 will now have become series 3.
Therefore, would there be a way to remove a series based on the name of the series, rather than the series collection number?
View 5 Replies
View Related
Mar 26, 2014
Is there a way of determining the series that has been selected on a chart?
View 3 Replies
View Related
Nov 18, 2008
I have a chart with 3 series':
Series1
Series2
Series3
The chart is called chtEngagement. I want to use vb to update these series'.
Series1 should be shown as:
View 5 Replies
View Related
Jan 4, 2010
I'm attempting to write a code that will change the colour of a series in a chart based on the name of each series. i.e. I have Roads, Water and Rail as three of my series names. can i somehow assign the colours yellow, blue and red to these series using macro code.
View 5 Replies
View Related
Feb 5, 2010
I'm getting an error when I try to set the XValues for a chart in my excel worksheet. The chart shows up fine and the new series adds fine though when I try to set the XValues the below error occurs
'Unable to set the Xvalues property of the Series Class' 1004. My Code is below. Can anyone see anything wrong with it? This could be a floor in my understanding of the chart object. I don't want to be selecting charts and working on an activechart because I'll have several in the one worksheet.
View 2 Replies
View Related
Jun 6, 2012
I am looking for a quick way to create a number charts using data from multiple sheets.
I have 12 sheets Apr - Mar that contain data in identical format. I also have sheets 12 sheets Apr Charts - Mar Charts, I need to create a number of charts on each sheet for the relevant month. To avoid having to create a ridiculous number of charts, I thought I would try to dynamically change the chart data series based on a value in say cell A1.
For example if cell A1 of the Apr Charts sheet = Apr, the chart will show the data for Apr.
The idea is that I can copy the Apr Charts sheet the required number of times and replace the value in A1, and my charts will update dynamically. I need to have seperate sheets for each of the monthly charts.
View 1 Replies
View Related
Nov 2, 2012
I have 2 columns of dates on which 2 separate events occur, e.g columns 1 is dates of receiving incoming calls (and there can be multiple incoming calls , so there are multiple rows with same date) and the other column is dates of outgoing calls (same deal of having multiple rows with same date, each row = 1 outgoing call). I want to plot both as line graphs and find out where they intersect , each one peaks and tapers.
View 2 Replies
View Related
