Not Plot Empty/Blank Cell In Chart
Aug 21, 2006
For Excel 2000:
In worksheet A, there is a column of numbers that can be 0 or not zero.
In worksheet B, there is a column of cells that determines if a corresponding cell in worksheet A is zero or not.
'As an example, for cell 'B!C6
=If( 'A!B52=0,"",'A!B52)
The column in worksheet B is used in a chart.
The chart settings are:
Tools | Options | Chart | Active Chart- Plot empty cells as: Not plotted
The chart is not treating "" as EMPTY, and therefore is not presenting correctly.
How would I set the value of cell 'B!C6 to EMPTY (or null, or blank, or whatever Excel calls it) using an IF statement, such that 'B!C6 would evaluate to EMPTY, and be ignored by the chart?
Would this be any different in Excel 2003?
View 9 Replies
ADVERTISEMENT
Mar 17, 2006
I 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.
View 9 Replies
View Related
Dec 6, 2006
I 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 Related
Oct 8, 2007
I 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.
View 9 Replies
View Related
Jan 1, 2008
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 Related
Apr 14, 2014
I 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 Related
Aug 12, 2008
I 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 Related
Oct 20, 2006
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 Related
Oct 24, 2006
I 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
View 5 Replies
View Related
Nov 9, 2006
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 Related
Jul 11, 2007
I 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.....................
View 3 Replies
View Related
Jun 27, 2008
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.
View 4 Replies
View Related
Apr 10, 2014
I have a data series plotted as columns. Then I want a horizontal line at $5,000 to appear all the way across the chart. It appears from the 1st to the last data point, but there is blank space before the first point and after the last point. I would like the red line to extend into this blank space before and after, so that it is going across the entire chart.
I tried playing around with different axis settings and no luck.
View 2 Replies
View Related
Jan 16, 2007
im having problem formatting my graph.
chart type = line.
I have 1 series of data, which is N values vs. Time.
Time however, is specified by specific dates. The graph is not putting a plot point accorrding to the date corresponding to the N value.
Example values:
date:____1/1/05 _______ 5/7/05 _____ 8/13/05
n value: __55 ___________ 22 _________ 11
I want the chart to reflect the time between the dates and put a plot point (n value) under each date listed and connect with a line.
View 9 Replies
View Related
Aug 18, 2006
how can i plot multiple series of values in one graph after checking some check boxes, which i have previously done, randomly?
View 4 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
Dec 8, 2006
I 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 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
Jul 27, 2007
Attached is a chart I am trying to create. So basically, I originally used a line chart but it didn't have the flexibility of the scatter chart, but i am having a very hard time formatting the X axis. First of all - I would like the line in the middle at the 0% point to actually go to the bottom so that the x axis labels aren't in the middle of the graph. Second the x axis labels aren't showing up correctly. They should be quarters: 1Q:02, 2Q:02, 3Q:02, 4Q:02... Finally, how can I show the correlation between these two data points plotted - the R-squared. THANKS!!! Auto Merged Post;I actually was able to figure out most of what I asked. What is remaining: how do I calculate the R-Squared on the chart? Isn't there a way to do it automatically in excel on the chart.
View 3 Replies
View Related
Sep 26, 2007
I have over 5000 cells of data. I want to take 4 data points at a time, average them then plot them on a dynamic chart. I know how to do the dynamic chart but how can I take the average of the the first 4 numbers store it in a different cell, then the next 4 and so on...
View 4 Replies
View Related
Apr 21, 2008
Is there any way in which I can map the tick mark labels on either the x-axis or the y-axis to different values other than the ones that are being plotted for.
For example, lets say the data is:
1 5
2 6
3 10
4 15
5 8
I want to plot these in a chart but instead of displaying 1,2,3,4,5 on the x-axis, I want to display a different column, say,
5
7
9
11
13
The answer is not as simple as plotting the required x-values with the y-values. I have just used a simplistic example but the thing I am trying to do has dependencies involved so I cannot simply plot the chart with the required column.
View 5 Replies
View Related
Jun 4, 2008
In the attached spread sheet I have created a scatter plot. How can I label each of the markers with its appropriate label from Column A - the Company Ticker. When I right click to show data labels it brings back the incorrect column. Also is there a way once the labels are brought in to put them in the circle and so that the chart doesn't look too clumped together.
View 2 Replies
View Related
Aug 1, 2008
I have the following list of values which I want to plot as a zigzag. But when i create a line chart from them, the lines connect the zeros and the non-zero points - while I want the lines to connect only the non-zero points - so that there is a zigzag-like pattern shown.
the Data:
0
0
0
0
0
1.4343
0
0
0
0
0
0
0
0
1.4332................
View 6 Replies
View Related
Jan 16, 2013
I am writing the following code to set the source data for a column chart. the source should be F13 until last cell, which is F862
VB:
ActiveChart.SetSourceData Source:=Sheets("Input TE country").Range("F13:" & ActiveSheet.Range("F13").End(xlDown).Address), PlotBy:=xlColumns
Now it selects F13 until the last cell, which is F65536.
View 5 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
Feb 6, 2007
I would like to click on my chart, which then gets resized automatically depending on its current size.
Situation:
My main macro creates various charts, all of them I connect to the macro "chartMakro". After the main macro has finished successfully, I can click on such a chart and the macro "chartMakro" is launched, supposedly resizing the chart. unfortunately i do NOT know how to tell the macro which is the active sheet, therefore my macro fails to work.
Question:
When a click on a chart launches a macro, how can I tell the macro which chart called it?
'appending a macro to a chart
' MAIN MACRO
'[...]
activeChart.ChartArea.Select
ActiveWindow.Visible = False
Selection.OnAction = "chartMakro"............
View 4 Replies
View Related
May 9, 2008
I have several worksheets with thousands rows (independent variables) and hundreds columns (all dependent variables). Each line basically gives me hundreds values for each independent variable - see below:
...
C9 39.65 653.95 5.28 163.56 99.56 14.49 ... ...
E9 7535.92 21500.56 2835.88 3122.98 7225.34 5371.25 ... ...
G9 111568 298021 12940 31645 181797 36996 ... ....
...
I need to know how the values in each row are distributed, and I ideally plot a 2D column graph of the distribution. Is there a way to do that and create/program a macro (with relative button on the workboook) that does it automatically once clicked?
Very often there are outlying values (bigger or smaller by a factor of 1000 or even more), mistakes, which I would like to identify and fix possibly.
View 9 Replies
View Related
May 16, 2008
I need to plot multiple series in a scatterplot. The problem is that the number of series is user-defined. And also, the number of data poitns in each series is different. I've looked it up already, but a lot of the examples i've seen don't account for the fact that I need to run through a loop and add series into my graph.
In other words, i need a way to select a certain range each time it runs through the loop, and add the data as a series into the chart. Also, the user enters how many series there are, so I use that as a counter
View 9 Replies
View Related
Jun 30, 2008
I've been trying to work out a code for a while now to plot some series in rows on a graph. The wonderful Mr Pope helped me with one before but for series in columns but i just don't seem to understand it enough to use it for this problem. the graph already has 2 series plotted (called the envelope) so i have to use seperate series to plot the data.
Basically the name of series is down column A
but the range is from E1 to Y1
and the data to be plotted on the graph which already has two series plotted in it is E2 to Y2, E3 to Y3 etc. iv been trying to use this code but failing miserably
Sub adddatatopsd(Name As Range, XData As Range, YData As Range)
With ActiveSheet.ChartObjects(1).Chart
With .SeriesCollection.NewSeries
.ChartType = xlColumnClustered
.XValues = XData
.Values = YData
.Name = Name
.ChartType = xlXYScatterSmooth
End With
End With
End Sub...........................
View 4 Replies
View Related
Jun 9, 2014
How can I write code to test if a cell is blank? My code is using "isempty" but I keep type mismatch error because my cells are filled with formulas that make the cells blank. It only works if the cells are truly empty with no formulas.
View 8 Replies
View Related
