The full s/s stretches to nearly 350,000 rows and consists of over 20,000 series of data, between 6 and 30 rows deep, in columns A:J.
In column I of each series, on rows 3, 4 and 5 there are three numbers.
The second number is to be subtracted from the first with the answer being divided by the third.
If possible, the answers should appear in the rows directly beneath these three numbers.
Because there are so many series of data it isn't feasible to manually input the formula and I'm searching for a macro(s) which can do it automatically.
Can anyone please suggest code to help me to this end?
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?
regards the two attached files (both are identical) except '1' has TWO Rows of data above the Macro Button and works correctly whereas '2' has only ONE Row (the desired option but does not work correctly!( Inserts Row in wrong place and incorrectly fill series)).
basically the program inserts a additional row below the last data entered, copies a formula cell from above and finally (the problem area) Series Fills a cell from data above.
The macro (used to) go down the list of spreadsheets and copy certain data from them into this master workbook. Now I'm getting a "'C:Users310108841DesktopTestFolder' could not be found" error and don't know why.
[Code].....
The error appears to happen here:
[Code] ....
What's more infuriating is that this appears to happen at random. I have been trying all afternoon to get it to work, and had no luck. I have literally just run it now, and it works fine. I can't believe it just works at random.
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:
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.
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
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...........................
Basically in B3 I have number 1. I want a macro to go to the next empty cell down and continue the series, i.e. 2. Then if it is ran again it will go to the next cell down and put in 3. This is what I want it to do only I don't want it to reference ranges. I added the offset line in but still need to know how to autofill to the nex line down..
I am trying to create a line graph that will incorporate multiple columns of data in one series of data. The reason I do not place all of the data in one column is because it could exceed the maximum amount of rows allowed in excel. Also I need the data split up for viewing purposes.
I can easily just graph one column but how do I combine all the columns into one line graph with the data being in separate columns. Basically all the columns will be my Y values and X values are just 1:n.
Example Below:
Column AColumn B Column C159261037114812
Now in the example all of the values are x values.
I want to delete all series which are in graphs. is there any way so that i can pull total number of series in VB from graph, and then just do all the series one by one.
I have a combination chart with data from Sun thru Sat (7 days). My manager wants to display only the weekday data (M,T,W,Th,F) and remove the weekend data (Sat,Sun). However, I also need this data to be included in other charts I need. I was thinking in years past, that I was able to remove specific data from the chart directly without having to alter or change the spreadsheet data? I have copied a second set of data and hid the weekend data, however, I have gaps in the data, which I need to remove in the chart display. Not sure what to do, or the best avenue?
I 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)
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.
I have a spreadsheet (see attached) in which there are many series and i want to be able to delete most of the series except the first 4.
(The 0%, 5% 10% air voids and the 95% comp line)
iv got this code, which is attacted to a command button, which works fine to delete all the series but makes the graph a blank white box. id prefer it so it delete just the series not the graph "picture?" ...
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 have about 50 lincharts that I wont to update with VBA but they have different number of series. Some have two series, some three and others four. Is there a way to count how many series a chart has? Otherwise could I get tips on how to approach this problem.
I have a big set of data and I want to create different graphs in different tabs with it.
So Imagine that in Tab 1 I want to do the sales forecast for the next five years for product A for all countries in Europe Tab 2 would be the same, but for product b Tab 3 the same, but for product c
So with dynamic tables in each tab I can easily do my graph
The problems is that since values vary per product country A in tab 1 is red, in tab 2 is blue and in tab 3 may not exist.
How can I set up a template so taht I do not have to color code each country in each tab? I.e. I want Spain to be blue in all my graphs in the workbook, Italy red and so on...
There is data which comprises of Part (#), Discount (%), Start Date & End Date. In this data Part (#) can repeat but Start Date & End Date periods will not overlap.
I want to write a formula which looks up into this whole data set and give me result as TRUE or FALSE and get Respective Discount (%) in another cell. True if for a specific Part (#) Discount (%) is applicable on Current Date.
Attaching sample excel file for example of data set.
I want to match rainfall with historical rainfall. It is quite simple to match a single value but I want to mtach a series or closest series (or series')
Eg for the last 6 measurements I get an couple of columns like this: Time value 1 5 2 4 3 5 4 2 5 5 6 5..........
I have got the following code to fill series and someone please help me that how to use this macro I can enter thses code in sheet1 on enter event and the result iwill be displayed in sheet2 and if any duplication in series then it is copied in sheet3....
I have a bar graph with 2 series of data. Anyone knows how I can specify the order in which they appear? code looks similar to the following. The bar graph shows the second series before the first series. How do I reverse the order?
With cht .ChartType = xlColumnClustered
' first data series Set ser = .SeriesCollection.NewSeries With ser .Values = someValues .XValues = someValues End With
' second data series Set ser = .SeriesCollection.NewSeries With ser .Values = someValues .XValues = someValues End With
So I have this given: Screen Shot 2014-06-23 at 3.53.01 PM.jpg and I am trying to fit my data to the graph I have in excel below. There is only one x-axis; suction pressure. However, there y values are dependent on two variables, discharge pressure and outdoor air temp.
How can I make the data on this sheet: Screen Shot 2014-06-23 at 3.53.05 PM.png
I have a list with hundreds of customers. Each customer has two measures (i won't bore you with how they are calculated)
1) % Coverage by Salesman 2) % Of Opportunity Won
What tips and tricks can you give me to take my analysis to the next level? The example is a very limited sample and the data was made up using a formula so correlated very well, i've chucked in a couple of anamlous results to make it a bit more realistic.
One of the main things i would like to see is a correlation "score". I suppose if the two series correlated perfectly then the score would be 100%. The less they correlate, then the lower this score gets....
In business terms, if i can run this rule over thousands of customers and get a good correlation - then it becomes a conversation with the salesman to show them that doing those pesky, boring coverage calls has a measurable impact on sales...and here is the number to prove it...
I've tried several things to accomplish this with no luck, but I'm sure it's not too tough to do:
On my worksheet, I am entering columns of cells with alphanumeric data. The data are actually thousands of reference numbers that need to go in chronological order, with only the numerals changing (e.g., ABC0001DEF, ABC0002DEF, ABC0003DEF..., with "ABC" and "DEF" always the same).
I can create a series just fine if I leave off the "DEF" part of the reference numbers (by selecting the first couple cells in the series and using the fill handle), but I can't get the same results with the numerals "nested" within the reference numbers (i.e., ABC####DEF).
It sure would save me a lot of typing to be able to create a series to fill in all my reference numbers!
I created a formula in column A "=MID(E2,20,4)". I drag this down the column and I get the following results
1021 42_u
The first result is fine but the problem is the second result. Because I am looking at fixed length I am often getting something other than the file number which I seek.
Is there a way to do a formula that will go to my starting positions (this is a constant starting point) and then look for the numbers before the first "_" as a stopping point? The maximum will always be 4 digit numbers but the problem is there are 1-3 digit numbers as well.
let's get str8 to the point. My data sheet comprises of projects in MW. So I have the name of a project and there power in MW. I would like to fetch all projects that have a similar MW power within a range of + and - 100, and align them one under another. i.e.:
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)