Chart Making- Chart With 3 Data Series

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.

ADVERTISEMENT

Making Chart Series Dynamic?

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

Change Chart Series Pasted From Another Chart

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

200 Series But Get (The Maximum Number Of Series Per Chart Is 255) Error Message

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

Line Chart - Remove Series Code But What If No Series Exists?

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

Show Chart Data Series Labels On All Series. 2007

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

Spurious XY Scatter Chart Series Adding Series

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

Color Pivot Chart Series Dependant On Series Name

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

Chart - Second Series Starts Where First Series Ends?

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

Color Bar Chart Series Based On Series Name

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

Making Chart With X / Y And Z Axis?

Aug 13, 2002

I would like to make a graph with an x, y and z axis. All of the choices that excel gives me are only x,y graphs. How can I plot a 3-axis graph?

View 9 Replies View Related

Making A Simple Chart

Jun 1, 2009

Im trying to create a bar chart that contains required hours per week with a line of hours worked going across the bars, im struggling to make this work and have basically no experience of making any sort of charts or graphs.

I need to create this using vba but to begin with I cant even make the chart manually! Where its all going wrong is the column line for required hours is so thin it looks rubbish. Its a bar chart then I formatted the series of total hours to be a line chart.

View 9 Replies View Related

Making A Special Chart

Jun 28, 2006

I want to make a chart in Excel similar to a Stacked Horizontal Bar Chart, except I want to be able to have spaces in between the bars of each row. I want to be able to have the separate bars represent spans of time, and I don't want them all to have to necessarily connect to one another. I tried the Floating Bar Chart under the Custom Types tab, but that doesn't let me have more than one bar per line (as far as I can tell).

View 2 Replies View Related

Making A Chart From Data On A UserForm

Jul 26, 2006

I'm trying to make a chart (which will also be on the UserForm) from data that a user will enter onto a UserForm. The problem is that I am trying to select a data range as one of the charts 'series' but I can't quite find the right code. At the moment it looks like this:

ActiveChart.SeriesCollection(1).Values = Range(BMIM20Ttl, BMIM2025Ttl, BMIM2530Ttl, BMIM3040Ttl, BMIM40Ttl)
ActiveChart.SeriesCollection(1).Name = " Total"
ActiveChart.SeriesCollection(2).Values = Range(BMIM2075, BMIM202575, ...

The problem is with the Range function.

View 9 Replies View Related

Making Chart/graph From Text By Occurrence

May 29, 2009

I track inventory/sales for a very small sole proprietorship. I'm looking to track the number of items that I sell based on the text that I enter in a certain column.

Specifically, I want to have a visual chart/graph to show the percentage of each size/color of an item that I sell, as I sell it. When I enter the words "3T pink" I want the chart/graph/whatever to show another quantity of this category. As I sell a new item, I want the chart to reflect it.

The problem I'm having, is that the graphs/charts require two data sets, but I want the graph to calculate it for me! All I want to do is enter the text for specific items, and when the text occurs subsequent times, I want this reflected in the graph/chart.

It seems so much more simple in my head....basically I want a dynamic chart, not based on a static data set.

View 9 Replies View Related

Use VBA Variable In Chart Series

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

Adding A 2nd And 3rd Series Onto A Chart

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

Dynamically Chart Series

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

Each Chart Source Row As A New Series

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

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 View Related

Reference Last Chart Series

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

Name Each Name Series Collection Of Chart

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

Excel 2010 :: Making Chart Title From Cell Contents?

Jan 10, 2014

I am trying to make the contents of a cell into a chart title.I know I can do it like this...

ActiveChart.ChartTitle.Select
Selection.Caption = "=tab_name!R2C1"

However, I am trying to use the same macro on several different tabs in the same EXCEL worksheet. Obviously, I can't have the tab name in the macro. But, I don't get a title at all when I try to remove it like this...

ActiveChart.ChartTitle.Select
Selection.Caption = "=R2C1"
or
ActiveChart.ChartTitle.Select
Selection.Caption = "=A2"

Is this possible? I am (kinda new at) using EXCEL2010.

View 3 Replies View Related

Making Chart Plot Data Cells & Not Null Values

Sep 6, 2006

I have a chart that shows up to a list of 28 people and the number of sales for that day. I'm try to make the chart only show the names and number of salesperson that are not = to null. This is what the chart has for values right now "=' Nest Average'!$C$6:$C$33". How can I make it so that it only shows those cells if not = to null.

View 2 Replies View Related

Adding And Removing Series To Chart?

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

Determine Selected Series On Chart?

Mar 26, 2014

Is there a way of determining the series that has been selected on a chart?

View 3 Replies View Related

Updating Multiple Series' On Chart

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

Change Colour Of Chart Series

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

Set XValues For Series Class (Chart)

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

Pivot Chart - Displaying Two Series

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

Set All Series In Chart To Secondary Axis?

Nov 21, 2012

How do I select all of the series on my chart and flip them over to the secondary axis? I can't seem to find an answer via google... Either VBA or straight Excel would be fine.

For the record, this is part of a larger question. I've been creating a chart using a couple for loops to work through a 2D array (VBA, clearly). At first I would just set each series to the second axis as it was created, but I've found that if I use collapseable groups to turn off different values...well, if I collapse the first series it all dissapears (still shows up in the legend, but the lines are gone.)

If I leave them on the primary axis I have no prolblem. So now I'm working on the theory that it's the way they are being added. So I'm looking for another way to put them on the other axis.

how do you put all series in a graph on the secondary axis all at once. Either an Excel based or VBA answer is fine (I can turn the Excel into a Macro if push comes to shove.)

View 1 Replies View Related

Using Array In Chart Series Formula?

Aug 16, 2013

I want to create 3 stacked bars on a chart, where each stacked par points to the same series data. This is for an automated reporting process and I don't want to create a 'staging' table if I don't need to. I have discovered that while the series formula can take an array in curly brackets of actual numeric values, if i fill that array with the same spreadsheet cell reference 3 times it is unable to evaluate it. By way of example:

1. This works
=SERIES(Chart1_Data!$A$7,Chart1_Data!$B$1:$D$1,Chart1_Data!$B$7:$D$7,6)

2. This works
=SERIES(Chart1_Data!$A$7,Chart1_Data!$B$1:$D$1,{592210.02,592210.02,592210.02},6)

3. This works
=SERIES(Chart1_Data!$A$7,Chart1_Data!$B$1,'MI Excel Template v.1.xlsx'!testrange,6)

4. This doesn't
=SERIES(Chart1_Data!$A$7,Chart1_Data!$B$1:$D$1,{Chart1_Data!$B$7,Chart1_Data!$B$7,Chart1_Data!$B$7},6)

Is there a solution to 4?

View 3 Replies View Related

Chart Series - Set Start / End Dynamically

Apr 16, 2014

The formula used to create the marked series line.

Code:
=SERIES(Rolling_window_data!$B$2;Rolling_window_data!$A$3:$A$122;Rolling_window_data!$B$3:$B$122;1)

The statistics in Rolling_window_data.

I need the marked line to be different from/to a point in time (x scale), depending on the backfill date in Rolling_window_data. All the contents are generated programmatically so the backfill date cell with a value will almost always be different. From what I've researched, I should make 2 series that overlap each other (in the current case, the 1st series should end on line 27 and the second should start from line 27 and continue to the end). The start is always line 3 and the end is always line 122.

How do I dynamically set the series start/end so I can make them overlap each other? I'm assuming I should add some kind of 'IF Statement' to the series code but I'm very new to excel formulas.

Code:
=SERIES(Rolling_window_data!$B$2;Rolling_window_data!$A$3:
IF(FOR Each Cell in Range("D3:D122") cell.value #N/A, Cell, "D122");Rolling_window_data!$B$3:$B$122;1)

View 2 Replies View Related

2 Separate Selections In 1 Series For A Chart

Jan 3, 2007

Hi does anyone knows how can I do a chart with 1 serie with the next values using the 2000 version of excel (not using row number 3)? .......

View 9 Replies View Related

Make Line Chart From 3 Series

Jan 8, 2010

I have data:

Jan Feb Mar Apr May
2009years 56 45 42 50 62 - series1
2010years 48 47 45 48 55 - series2

I need make line chart from 3 series. First two are series1 and series2. Third is percent change between 2010 and 2009 years every month (series3). First two are easy, but third is very difficult for me, becouse I haven't row with percent change and I can't haven't it in my exercise.

How can I make it without new row with percent change?

I tried it with Data Source in chart, also formula in Name Manager, but it doesn't succeed

View 9 Replies View Related

Changing Chart Series Colours In VBA

Jan 21, 2010

I have a spreadsheet which reports across different categories, but there's a lot of info. So simplify this, I've written a simple macro that changes the series range in an existing chart, so top level and by category. This works well apart from I'm using excel 2007, and some people aren't, so the default colours look fine on my version, but change to some awful colours on anyone with an earlier version of excel. I need a way of changing the colours to standard ones depending on which macro is being run.

I have tried running the following, but I get an error, basically saying the series hasn't been selected.

ActiveSheet.ChartObjects("Chart 6").Activate
ActiveChart.SeriesCollection(3).Select
ActiveChart.SeriesCollection(3).Interior.Color = RGB(255, 0, 0)

View 9 Replies View Related

Continuous Chart Instead Of Splitting It Into Two Series

Apr 22, 2006

I have data on two rows and would like to have a continuous chart instead of splitting it into two series. see attached file.

View 4 Replies View Related

Delete All Series In XY Scatter Chart

Aug 1, 2006

I am using the following code to delete all of the series in the chart. It is give type mismatch error.

Sub allchannelchart()
Dim i As Integer, s As SeriesCollection
ActiveSheet.ChartObjects("allchan").Activate
'ActiveChart.PlotArea.Select
For Each s In ActiveChart.SeriesCollection
s.Delete
Next s

View 2 Replies View Related

Formatting Chart With Too Many Series To Display

Aug 29, 2006

My question is a display/ chart format question. I need to convey what I am showing on this chart in a less "busy" fashion. Is there a way to display the data in an easier to read and see format? In particular, is having both averages on the chart in the same format an issue?

View 2 Replies View Related

Chart: Data Series Address

Nov 9, 2006

Trouble: I need to replace address of datacells for serias of chart
Result: data for serias will be some lines above or below as i need

simv = Workbooks(1). Names("adr").RefersToRange.Formula
num = Len(simv)
For i = 1 To ActiveChart.SeriesCollection.Count
oldadr = ActiveChart.SeriesCollection.Item(i).Formula
res = Left(oldadr, Len(oldadr) - num - 3) + simv + Right(oldadr, 3)
ActiveChart.SeriesCollection.Item(i).Formula = res
Next i

simv - my new address data for all serias this code is working on Excel 2003, but not working on some other versions. Beause "Formula" may has different structure :
version 2003: =SERIES(,,'C:...[macros.xls]Sheet2'!$B$8:$M$8,1) other version:
=SERIES('C:...[test.xls]#Source'!$C$6:$D$6,'C:...[test.xls]#Source'!$E$2:$AC$2,,1)

View 3 Replies View Related

Automate New Series For Bubble Chart

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

ADVERTISEMENT