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.
View 2 RepliesI 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.
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
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 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 RelatedHappy 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 ....
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.
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
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 want to split a column series as per its left three 3 digits into different columns as example given below....
View 9 Replies View RelatedVog 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?
I 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"
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)
See attached picture. Excel 2.JPG
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.
Graphseries.xlsx
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?
example:
8:00 .005
8:01 .006
8:02 .0075
8:03 .0077
8:04 .008
time now is 8:03
How do I get the .0077 in excel?
Bet this is simple to someone...
How can a continue this fill series down a column?
=$BC$3
=$BC$4
=$BC$5
=$BC$6
I would copy, paste and edit only there are 2500 rows!
I want to change the values arranged in the row wise to the format I have attached in the excel sheet. Manually it would be very tiresome.
View 4 Replies View RelatedI want to run a series of cells but how to do it. E.g.
Cell A1 to contain the data 2A
the next cell (A2) to read 2B
the next 2C etc etc
I've tried just dragging the cells down but it just duplicates 2A, 2B, 2C whereas I want it to fill the next cells as 2D, 2E etc
And I've tried using the fill series option but neither seems to be working (presumably due to the letter).
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..
View 2 Replies View RelatedI 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..........