B25: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.
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.
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?
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...........................
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 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.
I 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.
I 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.
i 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
Is there any possible to add a series to a graph table without having the line in the Graph. i just want to show the difference for information and not in the graph?
i have tried some different things (like having the difference in white so it is not visible, formatting the Axis etc)
I have a graph that auto updates but sometimes there is only 1 series and sometimes there is 2,3,4, or 5 series. Is there a way to have the graph update accordingly? and only have have the series that are valid be in the key on the graph. Currently the graph keeps all the series in the key even when there isn't any data for it.
Please see attachment. Use sheet 2 for the graph. and use the drop down menu to select catagory. this ia very quick mock up of the concept.
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'm trying to program a spreadsheet for staff attendance at a school. In the spreadsheet the staff member has personal days, sick time, and sick time in the bank (sick time rolled over from last year). When they use sick time, it needs to come out of the bank first, until it reaches zero, then it should move on to the sick time accumulated for this year.
Staff attendance WIP.xlsx
On the left side is the staff name, sick time, personal days, and bank. Then are the school days for July (if a staff member uses sick time/personal day it is placed under the date it was used). On the far right is the total remaining amount that the staff member has left.
The formula I have for cell AF4 is: =MAX(0,G4-SUM(H4:AC4)) so that the cell will not go below zero.
The formula I have for cell AG4 is: =E4-(COUNTIF(H4:AC4,"P")+(COUNTIF(H4:AC4,"HP")/2)) So that when P for personal day is entered, it will take a whole day, and when HP is entered a half a personal day is subtracted.
I need to figure out a formula for cell AD4. I've tried a couple of formulas I've seen on other posts, but they weren't for a series of numbers like this. And when I put them into this particular spreadsheet, it would subtract the bank until zero, move on to the 'sick time' cell and subtract the SUM of all the days sick rather than subtracting what was left when the 'Bank' reached zero.
I have a list of stores and the costs for various items.
Each store has (usually) more than one row.
Each store's items are totaled at the end of each line. Simple
What I do not know how to do is total all rows for each store, automatically. Like I said, I don't know how to explain it, so searching for it does not work out well for me.
Please see the attached sheet : How to Calculate Store Total.xlsx
I'm trying to calculate and average using a time frame in the day. I want to use a certain number "X" that correlates to a hour/minute in the current time. How do I write a statement to get me the correct "X" value?
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?
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 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..........