adapting 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.
Using Excel 2010. I have a Column Chart that shows 3 data series by month for one year
How can I customize the chart to group the data series on the X-Axis and repeat the 12 month series 3 times on one chart with one X-Axis to show each data series as a group?
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?
I am trying to develop a formula that will tell me whether an account has both Medicare and Medicaid or Medicaid HMO insurance. An account can have one or more lines, and one or more insurances. I manually completed the sample file in column 'D' (see attached) to come up with the desired output. The criteria is below:
-If the account has Medicare and Medicaid financial class, then it is a '5'. -If the account has Medicare and Medicaid HMO financial class, then it is a '6'. -Otherwise, it is a '0'.
Conditional formatting. I want to change the color of a group of cells based on data in another group of cells. Example:
If cells G8 and G9 (which are merged) are between 80% and 94%, then I9,I10,I11 (which are merged) will turn Yellow. Also, under the same scenario, IF G8 and G9 is greater than 94%, then cells I9, I10, I11 will turn Red.
I'm trying to find a way to sort or use a formula to group same numbers that are positive and negative together for reconciliation purposes. What part of my spreadsheet looks like is this:
I am trying to write VBA code that groups cells between blank rows and inserts a message if a string of text (a name) is missing from the entire group. I want the code to search cells in column A and group the cells between blank cells. Use the name I input in an input box as the search criteria and insert a message in the Column C next to the last blank cell in the group and then move on to the next group and highlight the cell in red with bold text. I am including a spreadsheet with an example of what the sheet should look like before and after the code is run.
I have date fields as column labels in a pivot table. When I try to group them I'm only given the option to group by integers and not by months, years etc.
I've had a look at the format settings of the column and they are formatted as date fields.
1) Not using a macro and combining relevant w/s functions, is there an intelligent way to return the value of: SUM(n=n1 to n=n2) of (-1)^(n-1)*(n)^(n-2)/(n-1)!*X^n to a cell containing the formula ??
2)Example: n1 in cell A5:: 1 (integer variable) n2 in cell A6:: 32 (integer variable) X value in cell A7::1.00 Returned sum in cell A8:: 0.567143 ...
In columm (A) I have a row for each day of the year, that is, 1/1, 1/2.....12/31 which has a corresponding value in column (B). In column (K) I have a row for each week of the year, that is, 1, 2....52.
In column (L), what formula can I use so that each row will sum the values of a seven day period from column (B)?
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 have several groups of data in the same sheet. Each group has two blank rows above the first row of data. Each group has column "B" in common (e.g., "Phone" in the example attached). I'd like to add a title/label of "Phone" to the first column in the row immediately preceding the first row of data for that group.
I am working on a spreadsheet and I need values from Sheet1!a2, a7, a12, a17 etc...
I want to fill in the formula ='Sheet1'!a2 etc, to fill column A in sheet2!
When I highlight and drop down to fill excel fills the column in increments of five like i need but doesn't start on the right one. Instead it fills the formula as ='Sheet1'!a6, (For cel A1) and ='Sheet1'!a11, (for cell A2) and so on.
I was trying to set yp a formula that will automaticaly extend a series. I mean will add a sequential number to the one above. If I have in cell A1: CD001. I want to place a formula that will show me: CD002 in cell A2. I tried n function but it only converts it to the number and comes uout as 0. I don't know which one to use if there is any. Answer to the first question I will probably be asked: No I cannot drag it down with a mouse as I will place it in the conditional function afterall.
I want to create 3 stacked bars on a chart, where each stacked par points to the same series data. This is for an automated reporting process and I don't want to create a 'staging' table if I don't need to. I have discovered that while the series formula can take an array in curly brackets of actual numeric values, if i fill that array with the same spreadsheet cell reference 3 times it is unable to evaluate it. By way of example:
1. This works =SERIES(Chart1_Data!$A$7,Chart1_Data!$B$1:$D$1,Chart1_Data!$B$7:$D$7,6)
2. This works =SERIES(Chart1_Data!$A$7,Chart1_Data!$B$1:$D$1,{592210.02,592210.02,592210.02},6)
3. This works =SERIES(Chart1_Data!$A$7,Chart1_Data!$B$1,'MI Excel Template v.1.xlsx'!testrange,6)
4. This doesn't =SERIES(Chart1_Data!$A$7,Chart1_Data!$B$1:$D$1,{Chart1_Data!$B$7,Chart1_Data!$B$7,Chart1_Data!$B$7},6)
I went through a lot of threads in this forum, although there were a couple relating to the same problem am facing, they didnt solve my error .. so here's my question again ..
When am trying to delink charts , i get an error stating "Unable to set the formula property of series class".
The code is as follows :
Sub DelinkChartData()
Dim iCtr As Integer, iChars As Integer, SH As Shape, iPlotOrder As Integer Dim nPts As Long, iPts As Long 'Holds the total no of points in the chart Dim xArray, yArray, sChtName As String, sSrsName As String Dim xVals, yVals Dim ChtSeries As Series ' var used to loop thru the series collection Dim ws As Worksheet Dim sChartType As String, iCtr1 As Integer
I want to be able to run a macro that will export some of the data from a worksheet called Generated Report into another worksheet called Matrix. The data will be taken Generated REport and grouped into sections of the worksheet. These sections will be defined by the BRID value.
The Data that i want to export will be from the first 5 columns within the Generated Report(note there are other columns which data is not required from).
The following columns will be exported from Generated Report -
BRID,Requirement, Bug Description, Bug ID and Alt Bug ID.
BRIDRequirement Bug DescriptionBUG ID Alt Bug ID PQ115 PQ115-Login Bug Description text xihllloloj43712 123 PQ116PQ116-Landing Bug Description text ghghghgoot 43713 126
For Matrix
The data will be imported into Matrix worksheet into the following columns as part of the row headings
- Requirement, Description, Bug ID and VF Bug ID.There will also be other columns (outlined below) which i will use to manually enter data
The data will be grouped and imported into the relevant section by its BRID eg PQ115, PQ116 etc.
I want each section (marked by BRID number)to be separated by row headings.
These row headings are to be generated after populating all data within each BRID number.
When the data has been populated in each row there is a column called 'Status' which contains drop down list boxes which can be assigned.
The default status will be set to 'To Do'. The complete row headings are displayed with Output from Generated Report underneath
BRID Priority Requirment Description BugID Alt Bug ID PC UpdateNotes Actual Result Type of DEfect Test Status Tested by PQ115 PQ115-Login Bug Description text xihllloloj43712 123 To Do
BRID Priority Requirment Description BugID Alt Bug ID QC UpdateNotes Actual Result Type of DEfect Test Status Tested by PQ116 PQ116-Landing Bug Description text ghghghgoot43713 126 To Do
So far the macro that i have been using allows me to locate the data for each specific BRID in Generated Report and copy into the corresponding BRID section within the Matrix. I have to repeat this step for each new BRID and i want to be able to whole automate this process.
Rows 1 & 2 are on a single sheet - there are 26 counties in all, in alphabetical order and divided into IPF groups (as shown on row 1).
On a second sheet I have a data validation list showing IPF1, IPF2, IPF3... etc. In the columns beside the validation list I would like to show just those counties in that IPF group (example in rows 4 & 6).
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 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.
