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 RepliesI wanted to ask if you know any charting software which can give weight to elements on a chart when two or more variables overlap? Ideally the color intensity of, say, circles should increase from pale blue to vivid blue depending on the number of the data items which are operlapping at one point. I include a sample chart which should give you a general idea of the chart I want to make and the data set from which it was made.
View 5 Replies View RelatedWe have a lot of charts we want to create in Excel 2007. We have templates created that seem to work. (But also sometime seem to not.)
After the charts are created that are placed in their own chart pages/sheets. The problem I'm having is that the x axis title doesn't seem to be centered horizontally wrt the x axis of the plot area. (It does look centered wrt the chart area.)
Some code or an algorithm to control he placement of the x axis title and maybe center it?
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
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
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 RelatedI 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.
How can I stop the overlapping cells?
View 2 Replies View RelatedI follows Andy Pope Methods to make average line,
Q.1 To make the line span across plot area continue…
I can not find solution, trying different options.
Please let me know how to do it?
Q.2 Question related to Average
If value is zero the, how to make average excluding zero by excel functions.
I'm trying to format the plot area of a X-Y scatter chart in a three colour gradient (Green, Yellow & Red).
The trick is, I would like the green portion to be centred in a user defined place on the plot with the yellow radiating from it, and the red radiating from the yellow. Ideally I would like to define some stops for the green as a user defined shape, with stops for the yellow outside the green stops (like the % stops you get in a vertical or angled gradient, but instead of the stops being simple straight lines or concentric circles from the middle, they would be user defined shapes).
I can do the normal types of gradient, but have no idea how to customise them in this way.
I'm using Office/Excel 2004 on a Mac Mini duo running OS10.6.8. I upgraded to Office/Excel 2011 but I find the program so horrible I had to switch back to 2004.
The problem: On one chart, the Y-axis label values have moved from outside the left-hand axis to _inside_ the right-hand axis and within the plot area. I can't figure out how to move the labels back to the right position.
Attached please find two charts based on data from 2003 until year-end 2006 (estimated). I want to show that the data is "seasonal" and sales depends on the month it falls on... i have several issues with the chart, is the second chart "too" overlapping and is there too much data to read? 2) how can i get the text boxes to go in the same direction as the chart..i know in powrerpoint you can move the direction of the textbox but how in excel? in sum, i can only choose one chart but still want to make each chart look much better.
View 3 Replies View RelatedI have some weekly data that I need to produce a graph from (100% Stacked Column). Each time I create the graph using the dates as the x axis i only get a thin line which looks like it represents one day, the other space looks like it is for the other 6 days of the week. I know that you could add an apostrophe to the beginning of the date but this is to be an automated report.
View 9 Replies View RelatedI have a set of data which indicates readings taken at different dates/times at varying intervals.
I want to create a chart where time is the X-axis but the usual charting feature would spread the data points at regular intervals. I want the data is spread out across the X-axis according to the actual timings.
I have a sample data in the file attached to this posting. There I showed the type of chart I want to see (which I achieved only by modifying the original set, Table 1. The modified set of data is in Table 2)
how to achieve this with the original table of data, Table 1.
I am trying to figure out the best way to plot this data -- For each region, I want to plot December 2006 snowfall against December 2007 snowfall. So starting on row 12 to row 41 (in the attached file). First challenge: the dates are not aligned because it is using the retail calendar where 12/3/2006 is compared against 12/2/2007. How can I plot the snow depth for the Northeast for example? I can't just use a dual axis chart because there are also dual dates...
View 3 Replies View RelatedIn the following macro, how to get the name of the chart as the slide title?
PHP Code:Â
Sub Range_Chart_1()
    Â
     'Requires a reference to the Microsoft PowerPoint Library via the Tools - Reference menu in the VBE
    Dim PPApp As PowerPoint.Application
    Dim PPSlide As PowerPoint.Slide[code]....
I'm using concatenate to name a chart title. My problem is that when I concatenate two dates I lose the date format. I'm after a title which looks like 06 December 2007 - 13 December 2007
I'm actually getting 39422 - 39429
The cells have been formatted in date format but lose this when concatenated.
Is there a sollution?
I have a pivot table which I update on a regular basis. There's also routine during the pivot table refresh to assign variables to the minimum and maximum dates I include in the range.
Here's what I'm looking for - The chart title needs to be two separate lines. It should look something like this:
Labor Operations performed from min_date to max_date
(labor op code, description, total ops)
By themselves, I have no problem doing this. What I can't figure out is how to format the font of the first line of the title to have different characteristics from the second line. The second line is sort of a descriptor of the part in bold (in an attempt to keep my boss from asking me questions about what he's looking at)
Is there a simple way (without writing code) to have a chart title equal the file (workbook) name? For example a formula to write in the 'Chart Options' 'Chart Title' box?
View 7 Replies View RelatedI would like to create a chart in vba which contains 2 sets of data, both using the same x axis. The first must be a line graph, the second a staggered bar chart. This must be represented on one chart with data labels. Also, how do I change the colour of the plots?
View 5 Replies View RelatedI have a 46000 rows of data. I want to make a chart, but only want to plot the highest and lowest data points in the data set. How do I get Excel to automatically find and plot only the highest and lowest data points?
View 9 Replies View RelatedI am trying to set up a bar chart that will not plot values where I have left the cell blank so that I don't have to keep resizing the source data / sorting the data whenever I add more data.
I have tried putting NA() as the value as you would for scatter plots
I have also tried leaving the category cell blank
Neither of these seem to work. I have attached an example.
one of my excel reports, i am using a dsum formula and plotting a chart against it. i do not want to show the zeros on the graph, is there anyway i can do that, i could not find anything in the tools -> options.
View 2 Replies View RelatedI have a chart updating on a weekly basis
a) can i select a whole range without the chart picking up any blank cells or zeros, if so then a lot of my other question will be redundant
b) I only want to show 4 weeks at a time, so when a new column of data is added on, how do i get VBA to shift the range accross 1 to the right e.g.
week 36 37 38 39
Shift to
week 37 38 39 40
I have a large amount of data recorded from a logging device which i then need to convert to a graph. Is there some nice coding to enable the user to select a range of dates from column A and times from column B then allow the user to select the column in which relevant data is contained and plot a line graph of the data contained with named axes.
View 9 Replies View RelatedI have 2 columns og data, for example
120
120
120
120
220
220
220
220
320
320
320
320
420
420
420
420
I want to plot them via a chart. But here is the problem..
My code.....................
I maintain data which gets updated every week. I have a chart associated with this data which I want to get updated automatically. I tried to use dynamic range on the chart and it kinda works. But the issue with my data is that I have data set in a row, followed by a blank cell which is then followed by average of last two weeks. Hence, when I use a dynamic range, it also displays the last value in the row (average), which I don't want to display in my chart.
So what I am looking forward to do is set up dynamic range or any other solution, which automatically updates my chart with weekly sales data without showing the average value in the chart. I have attached sample worksheet here.
I am creating many graphs. I have already split my data so I can use CurrentRegion.I want each graph to have a different title using the text value of a cell which would be (6 * j - 5, 2) inside the For/To, but I can't get it to work no matter how much I tinker.This is the code as is without any of my attempts inside to clutter it up.
VB:
Dim p As Long p = Sheets("ForWeb").UsedRange.Rows.Count For j = 1 To (p - 1) / 4 ActiveSheet.Shapes.AddChart.Select ActiveChart.ChartType = xlLine ActiveChart.SetSourceData Source:=Cells(6 * j - 5, 5).CurrentRegion ActiveChart.Parent.Cut Cells(6 * j - 5, 1).Select ActiveSheet.Paste Next
c5 shows today() and I'd like c5.value to be the headline of a chart. Hence, in sample file the headline be changed to 05/03/2014 (and tomorrow to 06/03/2014..).
View 2 Replies View RelatedHow do i change a title in legend on a chart. i.e. I want the label to be MGRS 2%
Job Category
Category %
EE Total
Mgrs
2%
15
Supvr
6%
51
[code]....