Split Series
Mar 16, 2009I want to split a column series as per its left three 3 digits into different columns as example given below....
View 9 Replies
ADVERTISEMENT
Series Line Not Correct After Split
Apr 16, 2014I'm using this code to split a series line into 2 overlapping parts, so I can use different styles on them:
Code:
Sub backfillDataSeries()
'make new series in Port Historical Risk
'if there is a backfill date in Rolling window data
Dim cRange As Range
Dim i As Integer
[Code]....
This is how the line looks before I run the code:
And this is how it looks afterwards:
I think it prints mirrored. Can I get a mirror image of the 2nd line?
200 Series But Get (The Maximum Number Of Series Per Chart Is 255) Error Message
Aug 4, 2014I 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] .....
Line Chart - Remove Series Code But What If No Series Exists?
Dec 22, 2011I 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.
Show Chart Data Series Labels On All Series. 2007
May 31, 2008I 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?
Spurious XY Scatter Chart Series Adding Series
Aug 21, 2006The 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 ............
Color Pivot Chart Series Dependant On Series Name
Jun 24, 2008I 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...........................
Summing Series Of Maximums In Larger Series
Aug 22, 2014I consider myself decent at Excel, but I don't have quite the depth of knowledge I would like. Here is my issue:
I currently have a series of 50 numbers for each row on a 300 row spreadsheet. I would like to write a formula that identifies the six largest numbers in that series and adds them together. So, for example, if the series was 8-6-4-4-4-5-1-1-0-15-3-9, the formula would spit out 47 (15+9+8+6+5+4)
How do I write that kind of formula?
Chart - Second Series Starts Where First Series Ends?
Sep 21, 2011I 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
Color Bar Chart Series Based On Series Name
May 15, 2008I'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 RelatedSplit 1st & Last Names & Split Addresses After 1st Comma
Mar 6, 2008I'm using Excel 2000 and I have a spreadsheet with 4 columns (A-D) and many (500+) rows.
Part 1:
#########################################
Colums A & B both contain identical data - a first name and a last name in the format "John Doe".
I want the second word ("Doe") removed from all cells in Column A so that only the first name remains, and I want the first word ("John") to be removed from every cell in Column B so that only the last name remains.
So, where A1 & B1 both started with the data "John Doe" now A1 contains only "John" and B1 contains only "Doe".
#########################################
Part 2:
####################################################
Column C contains addresses in the format:
"#5 - 123 Fake Street, Some City, CA 90210"
There is ALWAYS a comma and a space after the street address, then the name of the city or town followed by more data which may include one or more commas.
I would like everything BEFORE the first comma to remain in column C, and everything AFTER the first comma & space to be moved into Column D of the same row. The first comma and space are not needed again.
So, where C1 started with "#5 - 123 Fake Street, Some City, CA 90210", it now only contains "#5 - 123 Fake Street" and D1 now contains "Some City, CA 90210".
####################################################
Split Numbers And Split Words
May 8, 2009How do I split numbers!
I have two problems/challenges!
Part I...
I got the answer 1987, and now I want Excel to take the numbers out and display...
1 in one box then i set + in the next, then 9 in the 3ed. box, next box +, then 8, then +, then 7 in the last so that i can have Excel make a SUM of it all to 25.
How do I split 1987 and put the numbers in different boxes?
Part II...
I want to make A=1 B=2... all the way up to 9, then start over again with J=1 K=2... up to 9 again and then over again.
So that if I write my name it comes out as a value of 14 (Odd = O=6 D=4 D=4 =14)
(AJSØ=1 BKTÅ=2 CLU=3 DMV=4 ENW=5 FOX=6 GPY=7 HQZ=8 IRÆ=9, It's the Norwegian alphabet, that's why there are some extra letters)
So how do I set up my Excel so that is ANY name is typed in I can get it out into a number from the values assign?
Split Data In A Cell And Get The Value For The Split Data From A Different Table
Jul 31, 2006Here's my problem. I have a cell where there are many data strings seperated by ",". Each data string has a seperate value of its own like for e.g:
A2: aa,ab,ac
String Value
aa 1
ab 1
ac 3
What I want it accomplish is that, split the A2 cell into the different data string entities seperated by ",", then get the corresponding value of each of the data string entity, and to take the average of all the values of the different data string entities.
Max And Min Value Of Series
Feb 8, 2010Happy Morning to all I am new here, i need your help how can I get the Max and Min value of this ref#
GHACCCSBF00044711
GHACCCSBF00044712
GHACCCSBF00044713
GHACCCSBF00044714 ....
First Value In Series To / Below Zero?
Oct 19, 2012B25:AK25 represent months 1-36 B26:AK26 is declining dollar value tied to other formulas Need formula which indicates in which month the associated dollar amount in row 26 first declines to or below zero.
View 2 Replies View RelatedSum Of Arithmetric Series
Nov 19, 2009I need to make a formula in a Excel (I have Excel 2003), that takes value X and adds Z to it, then increases X by 1 and again adds Z to it, then again increases X by 1 and adds Z to it, until it reaches value Y plus Z. Or, repeating the (X+1)+5 process N times.
For example, if X=3, Y=6 and Z=5:
(3+5)+(4+5)+(5+5)+(6+5)
X (and Y) will have to use values from 1 to up to tens of thousand. X, Y and Z (or X, Z and N) will be cell values from an existing table.
Filling Down A Series.
Aug 3, 2009I m looking to fill down a column where I am assigning a number that maps to a certain week of the year. In essence after every 7th value the number should increment by 1.
Date Day ConsumptionWeek number
1/01/2009Thu 827 1
2/01/2009Fri 907 1
3/01/2009Sat 1,013 1
4/01/2009Sun 1,051
5/01/2009Mon 1,097
6/01/2009Tue 1,155
7/01/2009Wed 1,044
8/01/2009Thu 976 2
9/01/2009Fri 1,034 2
10/01/2009Sat 1,107
11/01/2009Sun 1,073
12/01/2009Mon 1,161
13/01/2009Tue 1,395
14/01/2009Wed 1,172
15/01/2009Thu 1,060
16/01/2009Fri 1,008
Multiple The Series
Jun 6, 2007I need to create a chart where there is one horizontal series and one vertical series for each data point on the Y axis. Example: results for 5 people (series along the X axis) in trials vs. results for the same 5 people in the actual competition (second X series). The trials vs. actual is the Y series for a certain competition on date MMDDYY. The Y axis would represent the various competitions throughout the year.
VOG Compare Series
Apr 19, 2009Vog I am trying to use your code from following post
http://www.mrexcel.com/forum/showthread.php?t=384758
Actual when I use to compare 2 series in same sheet I use following formula in column C (All Series "Number Stored as Text")
HTML If(Iserror(Vlookup(B2,$A$2:$A$40000,1,False)),"Not Found", "Found")
How should I use mentioned post code as per my formula?
Series Collection Vba
Jan 6, 2010I trying to code a SeriesCollection in vba, below is the code I have:
' ActiveChart.Name = "Chart1"
ActiveChart.PlotArea.Select
ActiveChart.SeriesCollection(1).Name = "='Chart1'!$C$1"
ActiveChart.SeriesCollection(2).Name = "='Chart1'!$D$1"
ActiveChart.SeriesCollection(3).Name = "='Chart1'!$E$1"
ActiveChart.SeriesCollection(4).Name = "='Chart1'!$F$1"
ActiveChart.SeriesCollection(5).Name = "='Chart1'!$G$1"
ActiveChart.SeriesCollection(6).Name = "='Chart1'!$H$1"
ActiveChart.SeriesCollection(7).Name = "='Chart1'!$I$1"
ActiveChart.SeriesCollection(8).Name = "='Chart1'!$J$1"
ActiveChart.SeriesCollection(9).Name = "='Chart1'!$K$1"
ActiveChart.SeriesCollection(10).Name = "='Chart1'!$L$1"
ActiveChart.SeriesCollection(11).Name = "='Chart1'!$M$1"
ActiveChart.SeriesCollection(12).Name = "='Chart1'!$N$1"
However it it giving the error "Object denfined or Appication denfined"
Add New Series To Charts
Nov 13, 2008i am trying to add 50 new data series and it takes hell of a lot of time to do it manually.
Sub Macro6()
Dim total As Integer
Dim Taper As Integer
Taper = 2
Do Until total < 100
total = total + 1
Taper = Taper + 1
ActiveSheet.ChartObjects(" Chart 3").Activate
ActiveChart.PlotArea.Select
ActiveChart.ChartType = xlBubble
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection("Total").XValues = "=Data!R" & Taper & "C6"
ActiveChart.SeriesCollection("Total").Values = "=Data!R" & Taper & "C8"
ActiveChart.SeriesCollection("Total"). Name = "=Data!R" & Taper & "C9"
ActiveChart.SeriesCollection("Total").BubbleSizes = "=Data!R" & Taper & "C7"
Loop
End Sub
Split Quantity Onto One Row Each?
Mar 30, 2014I have a table like this:
QTY PRODUCT
---------------------
1 AB111
3 CC332
2 DA221
Is there a way to display that information on another sheet so that each individual product is on its own line with a QTY of one? I can do it with VBA but was wondering if there is an alternative?
QTY PRODUCT
---------------------
1 AB111
1 CC332
1 CC332
1 CC332
1 DA221
1 DA221
Split The Range..
Dec 22, 2008I was multiplying 2 ranges in an formula to get a cell's value. The first was a set of user inputs and the second was an array of constants. Recently I needed to add another field in the middle of the user inputs which subsequently broke the range.
View 4 Replies View RelatedSplit The Text..
May 10, 2009I have in one column first name space and then surname. I want to divide it into two columns 1 for the first name and 2 for the surname.
View 2 Replies View RelatedCell Getting Split Up
Feb 3, 2014I have a very long HTML code that i am using for a description of something on our website, when i save the file in CSV format the HTML gets cut up and put into different cells on different lines ... the file needs to be saved in CSV format.
View 1 Replies View RelatedSplit Name Into Its Components?
May 29, 2014I need a routine that splits a cell with a name into its various components.
For example: split the name Johnny Jones and place Johnny into a different cell, and place Jones into yet another cell.
Split File
Dec 15, 2008QB03654338AGLQLD210/06/2007 0:0014/02/2008 0:00
QB03654338AGLQLD215/02/2008 0:0010/04/2008 0:00
QB03654338AGLQLD219/08/2008 0:0031/12/9999 0:00
to this:
QB03654338AGLQLD210/06/2007 0:0014/02/2008 0:0015/02/2008 0:0010/04/2008 0:0019/08/2008 0:0031/12/9999 0:00
So adding it to all to one line if it is the same ID number "QB03654338". See the attached file.
Split Worksheets
Jul 1, 2009In column A of a spread sheet I have lots of entries in this format:
admin_staff_a_002
admin_staff_a_006
admin_staff_1_010
admin_staff_1_003
admin_staff_b_005
admin_staff_z_007
admin_staff_z_001
What I need to achieve is to have a different worksheet containing all the rows for admin_staff_a then another for admin_staff_1 etc.
I want to name each of the worksheets a, 1 etc so i'd remove the admin_staff bit and the _003 bit
(Excel 2k) Using Split
Jul 7, 2009One of my three active brain cells isnt functioning today, so I need some help.
I am trying to use the "split" function but cant seem to get it working.
The variable sWbName is "MOT 4506667 C 142 #JN #SN.xls".
How Do I Split A String Twice?
Nov 17, 2009So I have strings like the one below. What I'm looking for the text behind the ":" being, 17-SEP-2009, 000737-EWO, SRS and finally 002-YAO
Example string:
date: 17-SEP-2009, number:000737-EWO, detail: SRS, DD:002-YAO
My ...