Duplicate Values For Area Step Chart
Jun 6, 2008
I'm trying to produce step charts like Andy Pope does on his web site using the area chart method. http://www.andypope.info/charts/stepchart.htm. This involves duplicating each of the values in each data series, which is tedious if there are a lot of points. Plus the fact that the area series is offset by 1 from the axis series. Can anyone think of a formula to do this? There are plenty of posts here about deleting duplicates, but I couldn't find much about creating duplicates. I can duplicate row numbers using =CEILING(ROW(),2)/2
View 4 Replies
ADVERTISEMENT
Nov 1, 2006
I am trying to graph the following data in an area chart with line charts superimposed on the area chart. I have a lot of data (and a lot going on) so I'm trying to figure out the best way to show this in excel from a functional standpoing (i can't get this to work in excel!!) to also an asthetic standpoint (dont want it to look terrible or illegible). This is what I'm trying to chart:
1) Weather data (temperature) by region:
So one region, would be: Northwest
I would like the "area" (so a shaded region) to be the min/max of the temperature data for each month.
2) I would like to show the temperature for each year as a line graph on the chart - so you can see if a year falls in or out of the shaded region.
3) I would like to show a company's sales increases across the same months per year as separate line charts. I may choose to just show the biggest outlier year in the end... or to show 2006 (the latest data).
What I am trying to convery with the chart is that the company's sales is or is not tied to weather deviations. I have attached an excel file with the data. I haven't been able to use the area chart or get a two axis chart to work or get it to look even remotely professional.
View 3 Replies
View Related
Mar 26, 2009
I got a formula from this forum to eliminate duplicate records in a array from 1 column in database. Now I would like to take it one step further and filter out records in the array that do not meet the criteria of being in a particular "Zone" selected by the user by clicking on a ComboBox from cell "AA18".
The first formula is copied to cell "C7":
View 6 Replies
View Related
Jul 24, 2007
I am trying to create a floating bridge bar chart.
Here is my data and I will try to describe as accurately as I can.
FY 2004 operating margin = 14.6% (while FY 2005 and FY 2006 = 11.2% => So the difference is 340bps)
The "bridge" back to 14.6% = 90 basis points for Jimmy'Z, 30 basis points for stock option expense, 30 bps for marketing -- that's 150 bps of the 340 and I'm trying to quantify the rest of the bridge but is there a way to show this in excel with floating bars - that is to say the 14.6% bar will touch the axis but the other 3 data points are not touching the X axis but are in the air in a step formation... ?
View 4 Replies
View Related
Jan 29, 2008
Usually Line Graphs show behavior of a process over time. That is why Excel smoothly interpolates between two values.
I would like to show the influence of some laws on technical requirements. Laws kick-in on a certain date, hence they are NOT smooth.
How to make Line Graphs that jump from one value to the other?
I already tried colums that without a gap and an overlap, but they are out of sync.
For clarification I made an example worksheet in which the question has also been illustrated.
View 3 Replies
View Related
Jul 28, 2014
I am trying to do a SUMIFS from a chart with duplicate values in rows and columns.
Data looks like this:
So this is basically sales of different products (columns) on different routes (Rows).
I want to add up a total for all the same kind of products eg. Magnum for each route eg. Local Top End.
View 9 Replies
View Related
Jun 5, 2008
I don't know what exactly is happening, but every step of the macro is happening every 1 second. Literally, the BPM is 60. It will execute a step in the macro every second, when in other macro's i've made, it zips right through as if it was on fast forward.
View 9 Replies
View Related
Oct 28, 2013
Code:
ActiveSheet.Shapes.AddChart.Select
ActiveChart.ChartType = xlAreaStacked
I created a simple stacked area type in vba. Is it possible and how to have this stacked area to get values from combobox and textbox?
View 3 Replies
View Related
May 5, 2009
Currently I'm working with an amount of data that corresponds with one data point every day in one year. Not every day is measured thus nothing is entered, some days can also be 0. Therefore I've made an if statement in the column next to the raw data column with an if statement returning #N/A if a cell contains nothing or 0.
This gives me normally no problems when I want to make a Line chart, excel just skips the #N/A cells and draws the line to the next data point (that's the whole reason for the if statement). However this is impossible with area charts. I can understand why, but I still want it as nothing or 0 in a cell not necessarily mean that nothing happened.
View 9 Replies
View Related
Dec 29, 2008
I'm trying to create an area chart to show the change in the number of staff working in my store over time. Unfortunately, Excel likes to draw diagonal lines. That is if I have the following data:
9:00AM - 1 staff
10:00AM - 2 staff
11:00AM - 1 staff
Excel will draw a diagonal line between each data point, rather than creating a box. This gives the illusion that there are somehow half people working at 9:30 and 10:30. Is there a workaround?
View 3 Replies
View Related
Nov 19, 2011
I'm having trouble drawing an area chart that ends sharply at a specifided value rather than drawing a diagonal line down to zero. Let me explain
I watched an Excellsfun youtube tutorial that used the IF statement to chart a subset of normal curve data as the second series in an area chart.
It looks like an easy way to add some interactivity. I enter a single value, and the IF statement checks to see if the data in the first sereis is less than or equal o that number. Once the first series data exceeds the number I entered, the IF statement populates the second series with "", which is to say a blank entery.
Here's my spreadsheet data and the chart it produces:
Normal
ABCDEFGHIJ1Series AHeight AHeight B:5.5210.04070850.0407085d2.87228320.06610770.0661077x5430.09509940.0950994540.12118840.1211884Height A: =NORM.DIST(A2:A11,$F$1,$F$2,FALSE)650.13680530.1368053Height B:=IF(A2
View 3 Replies
View Related
Nov 30, 2011
I have been measuring variation against some key performance indicators and trying to plot them on a chart i.e., line chart.
Is there any way in Excel that i can highlight in different colours the area between the variable and benchmarks? I want to fill the area say as green when the variable performed better than benchmark (area between the variable line and benchmark line) and as red if variable under performed than benchmark (area between benchmark and variable.
View 1 Replies
View Related
Aug 4, 2008
trying to find a way to insert a table into an active chart area.
unlike inserting charts as objects inside of a spreadsheet, i am trying to insert a table of the spreadsheet into the chart window.
the purpose is so that i may have a numerical reference to my data.
usually i post questions to which i have begun work on - but i have had no luck even brainstorming how this would be done!
the ONLY solution i have surmised is to insert a table into a chart area as an Object(picture)
but how would one write a macro for making dynamic range screen shotpicture files?
View 4 Replies
View Related
May 16, 2014
I have a series of X-Y Data (0-10 in both axis) that I want to overlay on an area chart to show outliers outside of an accepted condition. I have been able to create the area chart and the scatter chart separately but when I try to combine them the axis gets messed up. I tried using a secondary axis but I am not that skilled
I have attached the data and my feeble attempts at creating this chart.
View 2 Replies
View Related
Jan 23, 2012
I need to have a footer that displays the file name in the chart area so that it will copy with the chart when I paste into a word document.
View 1 Replies
View Related
Oct 26, 2006
I have plenty of experience with VBA outside of charts so that's where I need assistance. I need to get SeriesCollection(4), the PTAI series, to appear on the area chart second from the bottom instead of on the top.
The order for the non-line series should be, from top to bottom:
SeriesCollection(3) - Non-Mission AC ( Training, T&E, AMarc series)
SeriesCollection(2) - Total Pipeline series
SeriesCollection(4) - PTAI series
SeriesCollection(1) - Available PMAI series
Here's the
Sub MakeSandChart()
Dim ChartSource As Range
Dim ChartName As String
Dim ChartSheet As String
Dim yCount As Integer
Dim PAA_Value As Integer
Application. ScreenUpdating = False
passtype = Mid(ActiveSheet.Name, 5, 1)
Set ws = Worksheets(ActiveSheet.Name)
ChartName = Left(ActiveSheet.Name, 5)
ChartSheet = ChartName & " Chart"
ChartName = "F18" & passtype
Set ChartSource = Union(Range("SummRowDate" & passtype), _
Range("SummRowTAI" & passtype), _
Range("SummRowNM" & passtype), _
Range("SummRowTotPipe" & passtype), _
Range("SummRowPTAI" & passtype), _
Range("SummRowPMAI" & passtype), _
Range("SummRowPlanPAA" & passtype), _
Range("SummRowWorkPAA" & passtype), _
Range("SummRowDrillPAA" & passtype), _
Range("SummRowBaseline" & passtype))
Worksheets(ChartSheet).Activate
View 9 Replies
View Related
May 4, 2007
I have charts that needs to be placed on specific position and have specific sizes on a chartsheet. I use chartobjects(i).plotarea (top, left, width, height) for that.
Unfortunately when I set these values they still change and get antoher value, there is some scaling going on in Excel and I do not know where that comes from. I have been looking on the internet and finally I found a piece of code which works, but still not good enough, because the legend is also not good positioned.
De data for resizing the charts (ChtNew), the legends and the charttitle I get from other charts (ChtOrig). The legend.legendposition of ChtOrig does not have a value but it still have a top,left,height en width which I use.
I show you my code, the part with the for-next is taken from the internet.
With ChtNew.legend
.Top = ChtOrig.Legend.Top
.Height = ChtOrig.Legend.Height
.Left = ChtOrig.Legend.Left
.Width = ChtOrig.Legend.Width '* 1.1
.Top = ChtOrig.Legend.Top + ChtOrig.ChartTitle.Top
End With
View 9 Replies
View Related
Sep 11, 2007
I have an interactive chart displaying stacked area charts for 2006 and 2007
I currently have 2006 in front of 2007, but as the user chooses 1 of 20 units, it will look better to move 2007 in front of 2006
View 4 Replies
View Related
Jan 6, 2010
I have a stacked area chart located on its own individual worksheet. I would like to use VBA to apply colours to individual series based on the name of that series. I have applied the following code which I found on the following help forum, changing it slightly to fit my needs: http://www.ozgrid.com/forum/showthread.php?t=91381
View 3 Replies
View Related
Jan 29, 2010
how to resize the plotarea of a chart so that it wont overlap with the title. I can't seem to find the appropriate property to let me determine the height or the bottom edge of the chart title.
View 2 Replies
View Related
Oct 2, 2006
I am creating a line chart with markers using 5 series of data. I want to colour in/shade the area under the lowest series.
View 6 Replies
View Related
Feb 17, 2014
I am using an area chart to display the price spread between different types of wheat.
Now the problem I am having is that, at the "end" of the chart, ie. the most recent observation date, the line drops to zero while the value is actually well above zero. How can I avoid this ?
I am attaching a screenshot : Wheat Spread.PNG
View 3 Replies
View Related
May 16, 2008
This seems so simple but i looked everywhere and i cannot find the vba script to add a 3 point solid black line border around the chart and plot area of my graphs. The record macro option does not record this.
View 3 Replies
View Related
Aug 19, 2014
I have a worksheet that has 3 duplicate values in a particular column, I need a macros that will highlight two of the duplicates row and then another macro to delete the entire row. The duplicate element are in column R. find attached worksheet.
Copy of OCL 2010 (3).xlsx
View 1 Replies
View Related
Jun 12, 2008
I am using the following macro to insert the word "Duplicate" in the first blank column next to a duplicate row. My data is sorted by the first column. Data Example:
12345 a
12345 a DUPLICATE
11111 b
23123 b
Here is the macro I am using and it does not work. It marks the first duplicate it finds then goes into an infinite loop. Any Idea where I went wrong?
Sub MarkDupes()
x = ActiveCell.Row
y = x + 1
Do While Cells(x, 1).Value <> ""
Do While Cells(y, 1).Value <> ""
If (Cells(x, 1).Value = Cells(y, 1).Value) Then
Cells(y, 3).Formula = "Duplicate"
Else
y = y + 1
End If
Loop
x = x + 1
y = x + 1
Loop
End Sub
View 3 Replies
View Related
Jan 5, 2004
I have 4 columns in my spreadsheet. I am trying to find any duplicates that may exist in Col A, sum values in Col D, then delete the entire row. So far my sheet before I run my vba code is this.
Col A
100
101
102
105
100
101
102
105
Col D
5
4
2
4
1
2
3
1
After my code is run, I need for my spreadsheet to look like this
Col A
100
101
102
105
Col D
6
6
5
5
I have some code but I still need to do a considerable amount of tweaking to it. Currently my code is only deleting the duplicate values in Col A. I am having difficulty summing the values in Col D as well as deleting the entire row.
Here is my code thus far....
-------
Public Sub FindDuplicates()
For RwCnt = 1 To (Worksheets(1).Cells(65536, 1).End(xlUp).Row)
SrchValue = Worksheets(1).Cells(RwCnt, 1).Value
If Len(Trim(SrchValue)) > 0 Then
With Worksheets(1).Range("a1:a" & Cells(65536, 1).End(xlUp).Row)
[Code]....
View 9 Replies
View Related
Jan 5, 2004
I have 4 columns in my spreadsheet. I am trying to find any duplicates that may exist in Col A, sum values in Col D, then delete the entire row. So far my sheet before I run my vba code is this.
Col A
100
101
102
105
100
101
102
105
Col D
5
4
2
4
1
2
3
1
After my code is run, I need for my spreadsheet to look like this
Col A
100.........................
View 9 Replies
View Related
Apr 18, 2013
How can I add missing values to the axis in a pivot chart line chart? I have a numerical X-axis with values such as 0,1,5,8,14, etc. I have another set of Y values that correspond to the X values. If this was an XY-scatter plot I'd be able to plot X vs. Y and connect the dots for "gaps" in the X values. Since it's a pivot chart I cannot use an XY-scatter plot, I can only use a line chart. The line chart doesn't give me the ability to add the "missing" values, so it gives the impression that my data is more closely packed than it is. The data source is external to my spreadsheet, so I cannot add the values before creating the pivot table/chart.
View 1 Replies
View Related
May 10, 2014
I need to have two area charts combined, but they have different x and y values. Is there another way to do this.When I try to combine them in one graph, I can only use one x-axis value.
In the excel sheet the PV needs to be the x-axis.
View 1 Replies
View Related
Mar 2, 2014
I have two charts (Chart A and B) and 1 duplicate chart of chart A ("Chart A", didnt change the change name so remain the same).
"Chart A" is overlay above Chart B to see the actual different.
When the data is updated, interval-Y in "Chart A" is different (by +-10% offset) than Chart A which support to be the same.
At this stage, comparison is not possible.
The interval-Y is link to vb.
Checked when there is offset the maximum and minimum offset is different and else I cant find any other problem.
Do I have to state that there are two Chart A in the vb?
View 1 Replies
View Related
