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..........
in writing a macro that does the following.
Right there data in cell A1 lets say LTP BY001 and then the next data in column A is LTP BY002 in cell A9. What I need to do is copy LTP BY001 from cell a2-A8 and then LTP BY002 From A10 until LTP BY003 appears lets say A15. Therefore need LTP BY002 copying from A11-A14. I need that to go right the way down the workbook at everytime their is a number change.
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?
My task is to combine two large databases into one spreadsheet by extending the number of columns. The data from each database is 90% matching based on an identification number, however occasionally there are additional rows or unmatched identification numbers that need to be kept for analysis.
When this happens, there needs to be a blank row inserted to represent the missing data in the rest of the corresponding row.
I am having trouble finding a quick way to do this because I have approximately 12,000 rows (and columns up to DV when combined).
for example:
p1
data
data
data
data
p2
data
data
data
data
[Code] .....
needs to become:
p1
data
data
data
data
[Code] .....
I am guessing I will need a macro of sorts, So far I have made one column that tell me if the ID's are matching or not (1 or 0) and if they are not matching (0) I manually insert the rest of the row that is missing or make space for the duplicate data (which needs to remain).
I have two tables, Table1 one has only customer codes in it, and I have Table2 with plenty of customer codes and those customer name, surname, age, location...
And I want to match and copy each of those customer codes in Table 1 all the information which is on Table2
Table 1:
50025
50026
50086
Table 2: Considering that name, age and location is each in separate cell
50025JohnSecond25Location 1
60085EmilyThird 20Location 2
45454Wilhelm Fourth35Location 3
10000RoseFifth 60Location 4
50086JohnySixth 45Location 5
65501JacobSeventh18Location 6
50026Jackie Eighth22Location 7
And the outcame should be that it finds the value from Table1 in Table2 and copy's information in Table 1 like this:
50025 JohnSecond25Location 1
50026 Jackie Eighth22Location 7
50086 Johny Sixth 45Location 5
I have two worksheet "ABX" and "ACX" from which I want to find the matching data for specific columns B, D and E only.
Matching data will be in sheet "Match" and vice versa.
Data are present dynamically.
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.
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 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 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?
View 2 Replies View RelatedI 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?
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
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 RelatedI 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.
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?
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
dependent on both axes?
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...
How I can interpret this data?
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 have a spread sheet with some data strings in Column E.
In Column A I want to extract a series of number from within the data in column E.
Example data in Column E
C:cleanedsystems1021_update_log_2-12-2012.csv
C:cleanedsystems42_update_mail_log_2-12-2012.csv
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.
How to format an Excel table in a forum post!
I have the following table
Code:
seqIdpointsMAEMFE
1033022
1122-511
12-10-1018
130-517
14-11-117
1630-620
1712-120
186-444
190-126
I want to create a stacked chart with seqId as the x axis, and the other 3 columns are values on the y axis.
Excel is making seqId a data series, I don't want it to do that. How do I change it? This is what I have now:
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.:
project 1 = 1000 MW; year=2007
project 2 = 1200 MW; year=2008
project 3 = 900 MW; year=2008
project 4 = 1100 MW;year=2009
project 5 = 800 MW;year=2009
I need a formula that gives me fetches all projects that are =1000 or + and - 100 MW
the result should be the following :
project 1 = 1000 MW; year=2007
project 3 = 900 MW; year=2008
project 4 = 1100 MW;year=2009
I hope I made myself clear...in any case...I'll pass the torch to the Master that can work this dilemna out.
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)
I am creating a timeline using a stacked bar graph. The 3 colums in the source spreadsheet are title,start time and duration. The Two data series are start time and duration. I hide the start time series to give me timelines for each row in the spreadsheet. This works fine. What I'd like to do is to group the rows by title and distinguish by colour in the duration series but this doesn;t seem possible? I hope I've made myself clear.
View 4 Replies View Relatedadapting a formula which I was provided on the forum earlier in the week (I have attached a worksheet as an example). The formula is in column A in the following format ‘=IF(B4=B3,A3,IF(OR(AND(B3<=B2,B3<=B4),AND(B3>=B2,B3>=B4)),A3+1,A3))’.
It is used to number groups of data and it should increase by an increment of 1 for each group. The groups are defined by a changing angle (column B) which runs from high to low (group 1) then low to high (group 2) etc.
The formula in column A works fine until the angle column does not change for 1 or more data points (which sometimes occurs on the change over between groups). See row 430 and 331, the group 6 needs to either be included in group 5 or 7 and then the current group 7 needs to become group 6 and carry on as normal. Is there any way of adapting this formula or changing it to account for this problem?
I have a lot of data series like these and the repeated angles could occur between any groups not just at group 6 as it has done in this example.
I have one series on my chart set as column type with data value labels. I have another series as line type to create a benchmark line across the chart. The chart is interactive where user can select 1 of 20 units.
On some of the units, the labels are overlapping the line type chart and causing a poor display
I have a chart with Data Series being dynamic ranges. There is only one series and x & y each have a dynamic range. I need to be able to include Series Name as a dynamic range.
For example. I have 3 rows of values with 3 columns.
Col 1 Col 2 Col 3
Name X-Val Y-Val
A 1 3
B 4 6
C 8 0
I now need a chart that has 1 series with a x range of col2 and a y range of column 3 but a corresponding name equal to Col 1. So when I move my mouse over each point I get the series name. E.G. I hover over poing 1,3 I show the name as A. Or I hover over point 8,0 I get C.
If it isnt possible using the ranges the way I am, then I would be looking for a possible solution. I am sure I could do this by some sort of loop but not quite sure given the number of rows constantly changes.
Now that the calculations are working, with the press of a button, I need to be able to select a range of dates and copy all lines within the range to a seperate sheet with the desired name under the same headings they currently reside under. I have included some modified code that is being used in another spreadsheet that was created for me, but I do not pretent to understand all of it and I no longer work with the creator of the spreadsheet. How do I use a button to open the form for date selections and entering the name of the new sheet, and then use the start button on the form to begin the matching and copying to a new sheet? If there is an easier way I am all for that too.
View 14 Replies View Related