Trendlines in graphs

Hi all!  I have an interesting question.  I am ploting a 
graph where the X axis is by months and the Y axis is the 
number of parts produced.  Some months we do not produce 
any parts; therefore, the Y axis is "0" at that time.  
When I put a trend line on the chart, it sees the "0" as a 
computable data point.  A line is then generated but is 
off from the true trend.  The true trend can be generated 
if I remove all the non producing months.  Is there a way 
to specify specific groups of cells in a column that you 
want to use in a trendline analysis--sort of hop scotch 
over cells?  Hope to hear from you.
Your cyber buddy, Brad/
0
bradmg (3)
10/23/2003 9:58:50 PM
excel.charting 18370 articles. 0 followers. Follow

2 Replies
1292 Views

Similar Articles

[PageSpeed] 28

The chart trendline will be computed using all charted data points.  If 
you are giving an explicit zero, it shouldn't be surprising that Excel 
uses it in the calculation.  The surprising thing is that a formula like
   =IF(formula=0,"",formula)
will still plot as zero and be used as a numeric zero in the trendline 
calculation.

To omit the non-producing months from the graph, the cells for the 
non-producing months should either be empty, or else should contain 
#N/A.  Either way, there would be no plot symbol for the point.  There 
would be a difference if in addition to the trendline, you want to 
connect successive data points by straight lines; an empty cell would 
cause a break in the connecting line, while #N/A would not.

The easiest way to plot a zero on the graph and ignore it in the 
trendline, is to plot two data series: one with the zeros, and one 
without.  Do not connect data points on the series without zeros, but 
plot the trendline for the series without zeros.

Jerry

Brad Gover wrote:

> Hi all!  I have an interesting question.  I am ploting a 
> graph where the X axis is by months and the Y axis is the 
> number of parts produced.  Some months we do not produce 
> any parts; therefore, the Y axis is "0" at that time.  
> When I put a trend line on the chart, it sees the "0" as a 
> computable data point.  A line is then generated but is 
> off from the true trend.  The true trend can be generated 
> if I remove all the non producing months.  Is there a way 
> to specify specific groups of cells in a column that you 
> want to use in a trendline analysis--sort of hop scotch 
> over cells?  Hope to hear from you.
> Your cyber buddy, Brad/

0
post_a_reply (1395)
10/24/2003 12:58:47 PM
Goto tool>options.
click the tab chart.
click the button "plot empty cells as:" Not Ploted
I think that should do it.



------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~ View and post usenet messages directly from http://www.ExcelForum.com/

0
10/24/2003 3:08:09 PM
Reply:

Similar Artilces:

frozen graphs
all graphs on one Excel leaf have become like "frozen", I cannot select them (although the pointer identifies a "graph area") and of course I cannot change graph parameters, data source nor anything. Thanks and a happy monkey year ! Perhaps the chart data has been delinked. Jon Peltier has some information on his web site: http://www.peltiertech.com/Excel/ChartsHowTo/DelinkChartData.html ralric wrote: > all graphs on one Excel leaf have become like "frozen", I cannot > select them (although the pointer identifies a "graph area") and of > ...

Trendline error???
Has anybody else had this problem? If one uses a 6 order polynomial as trendline and then s/he enters manually in an other column the same equation taken from the displayed trendline equation on the graph , the original trendline is different from the one manually entered. Excel plots the two different curves having the same equation!!! Please see an example at : http://www.geocities.com/audax/error.xls Thank you for the help. Gene Disregard the previous message. The problem is due to the rounding off. The trendline calculation is OK. Gene See my website for the use of LINEST to get coef...

Trendlines in graphs
Hi all! I have an interesting question. I am ploting a graph where the X axis is by months and the Y axis is the number of parts produced. Some months we do not produce any parts; therefore, the Y axis is "0" at that time. When I put a trend line on the chart, it sees the "0" as a computable data point. A line is then generated but is off from the true trend. The true trend can be generated if I remove all the non producing months. Is there a way to specify specific groups of cells in a column that you want to use in a trendline analysis--sort of hop scotch ...

Add Trendline to Pivot Table Graph
I have a list of survey responses and I have created a Pivot Table with Month as the Row Heading and Count of Respondent as Data. I have then created a Pivot Table chart. I want to show a trendline but when I select the columns and right click for Trendline, as I would normally, the option is greyed out. How do I create a trendline for the pivot table category columns? If Add Trendline is disabled with a pivot chart (I don't know, I rarely make pivot charts), you could plot the data in a regular chart, then add your trendline. Pivot Tables, Pivot Charts, and Real Charts...

weighted trendline
Can anyone tell me if it possible to weight a linear trendline in excel? Like a calibration line where you can weight by 1/x where x is the variance at each point on the line. Hi, I don't believe that can be done with anything built into the trendline chart feature, but you can use the trendline formula in the spreadsheet and then apply your weighting to it. Then plot that rather than the built-in trendline. You can get the formula of the trendline from the chart by choosing to display it or in the spreadsheet by using the LINEST function or the related SLOPE and INTERCEPT func...

scatter chart graphs
I am trying to graph the delivery of pipes for a pipeline. Y being date of delivery, x axis showing mile post. data is to show a line the length of pipe delivered, beginning at the mile post it is to be laid and ending at the length it is. I have various pipe length and widths. Data Source option only allows me to enter each individually. Is there an easier way of entering the Data into the graph? Liz, You can add these variables with VBA. You are changing the xValues and Values properties of the graph after adding a newSeries. -- http://www.ExcelHelp.us ed@ExcelHelp.us 888-MY-ETHER...

Increaseing Precision in polynomial trendline equations
How can I increase the precision in Excel's "Display Equation" option for trendlines? I am trying to fit a polynomial regression (trendline) to a X, Y scatterplot I have created. Excel's built-in trendline function shows a good fit (R2=0.9999) for a 6th order polynomial, however, If I re-plot the data using the coefficients from the "Display equation" option, the data diverge significantly. Apparently, for high order polynomials, you need to have a high level of precision in your coefficients (many decimal places accurate) in order to actually re-plot the same ...

Trendline
How can I create multiple trendlines on one graph i.e I have a graph with 5 years data and I want to show a linear trendline for each year on the same line? You need each year plotted as a separate data series, then follow Mike's advise best wishes -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "Carlo" <Carlo@discussions.microsoft.com> wrote in message news:3F847B4B-9C32-464A-82F9-001A340A67FB@microsoft.com... > How can I create multiple trendlines on one graph i.e I have a graph with > 5 > years data and I wa...

how can i put graphs on one sheet
I want to draw a three lines with their coordinated on one graph sheet Please explain this question in more detail. -- Thanks, Shane Devenshire "Hass" wrote: > I want to draw a three lines with their coordinated on one graph sheet > ...

Excel: Can I force a linear trendline through the origin?
Yes, open at the Option tab when you make (or format) a trendline There is a text box to set intercept to any value (including 0) best wishes -- Bernard V Liengme www.stfx.ca/people/bliengme remove caps from email "Bill" <Bill@discussions.microsoft.com> wrote in message news:B0487508-C3C9-46C9-A7E3-EA42B60016E2@microsoft.com... > I have done that, but it is not working. Perhaps it is because I am plotting a log-log graph and therefore there is no actual zero value for the y-axis. But I need the line to pass through the origin, and because the data covers severa...

Trendlines
Does anyone know how to choose the correct trendline for a data set? I have plotted a data set in Excel 2007 (located at the following link) http://www.mediafire.com/?sharekey=a6c026861bdd9f9ad2db6fb9a8902bda, but I don't know how to choose the correct trendline from the options Excel provides. The 'correct' trendline depends on the data. If I weight 2 bags of sugar, then 4 bags, then 5 I would expect the trend to be linear. But if I measured the area of circles of different radii, then I would need to use a second order polynomial with zero intercept. And so on. What is you da...

Graph Problem
I have a worksheet that extracts live data to a column at regular time intervals set by the user and is continuously updated. How can I transfer this data to another workbook/worksheet wherby time could be reproduced along the horizontal axis and the other data reproduced on the vertical axis? Would one use the timer function within VB code, or is there a simpler way, whereby I could paste each fresh set of new data into a new column in a new workbook/worksheet in order to create a graph simply? ...

Remove Trendline
Version: 2008 Operating System: Mac OS X 10.6 (Snow Leopard) Processor: Intel Email Client: imap How do I remove a trendline? This is very frustrating. <br><br>Even at 200% zoom there is no way to click on the trendline. The cursor remains a crossed arrow. If I right click on the data line, there is no choice for removing a trendline, only for adding new trendlines. <br><br>Robert Rose So I see I posted this to the wrong Forum. Shows how frustrated I am. Hi Robert, I can simply click on the trendline, and it gets highlighted. You have to be very careful...

Bars in bar graph are not centered on label
I have created a bar graph in PowerPoint 2000. The problem is that the bars appear randomly between the tick marks for each month, rather than appearing in the center of the appropriate space, and there seems to be no rhyme or reason as to why they appear where they do. Some are to the left of center, some are to the right. Can anyone tell me how to get the bars centered between the tick marks? The axis labels are centered, why aren't the bars? Hi, Could be the axis is being plotted as Time series rather than Category. Right click chart and pick Chart Options. On Axes...

Recommended Colors for Bar Graph
I currently have a black/white bar graph that I would like to add colors to. Can anyone recommend colors that look good? maybe a website has samples of how different colors go together. Thanks, Marky Marky, Colors are a matter of taste. For some samples of what's available in Excel, take a look here: http://www.mvps.org/dmcritchie/excel/colors.htm John "Mark Jacobs" <spanishtunes@yahoo.com> wrote in message news:b20110da.0401031751.3d5c29d2@posting.google.com... > I currently have a black/white bar graph that I would like to add > colors to. Can anyone recom...

Creating a line graph from 2 pivot tables?
Hello, I have two pivot tables, one contains information RECEIVED in a week for a country and the other contains information ACTIVATED in a week for a country. The countries and time range are the same for both. Within the line graph I would like to show, how many things were RECEIVED and ACTIVATED for that country in that week? I am able to show either the country or the time range but not both. How can I show both? Is this possible? Try using the multiple consolidation ranges in the pivot table options. You should be able to bring both tables into one and then chart it from there ...

Trendlines
Can anyone help? I what to add a logarithmic trendline but the option is greyed out and I can't seem to find where the addin might be. I am working with Excel business 2000. There is no add-in. Sounds as if the data is not amenable to a log fit.Are there negative values? Is the x-axis numeric? Bernard "Gee" <anonymous@discussions.microsoft.com> wrote in message news:E66D16D7-BAC5-454A-9827-59BE6BFB8447@microsoft.com... > Can anyone help? I what to add a logarithmic trendline but the option is greyed out and I can't seem to find where the addin might be. I am worki...

Placing Trendlines Behind Data Points
Is there a way to get the trendline to show behind the points? I have an XY (scatter chart using just the points (no lines). I added a trendline to get a nice parabolic curve. Normally I would use the connected option when I pick the chart type, but the resulting line does not fit as well as the trendline. Not that I know of. The trendline shows within open marker with a white background best wishes -- Bernard V Liengme www.stfx.ca/people/bliengme remove caps from email "Sloth" <Sloth@discussions.microsoft.com> wrote in message news:3D4A7967-EF8C-4B9A-9F76-E280F235E...

cannot see trendlines
I have a graph with 6 series. There is a primary and a secondary X axis. I want trendlines (moving average) on all data series. The chart will not show any. No error messages, go through procedure of adding trendline, but it just doesn't show up. Search for hidden line with cursor, doesn't show. The trendlines show up in the legend whether or not they show up on the chart. Scaling is automatic, and there is space above and below the data curve. ...

Help with graph
Hello, I am having trouble representing this data in a graph Column A = 17 Items Column B = September Reported Column C = September Actual Column D = October Reported Column E = October Actual .... to the end of the year I want to show each item with the numbers reported in contrast to the acutal. I can't get it into a format that is easily represented in a graph. Am I arranging my data incorrectly? What kind of graph should I use? Hi, You could create a (say Bar) chart from your data by selecting B1 and D1 (select B1, CTRL-click D1) and Insert Chart. On the Series tab, add a Series...

graph totals
How do I display totals within the graph data table without those figures showing up in the graph The data table won't display totals. However, you could show the totals with the X axis label: In the data source table, insert a row between the X Axis labels, and the first row of data. In the cell below the first label, enter a formula that refers to the cell with the label, and the cell with the total, e.g.: =B1 & CHAR(10) & B8 Copy this formula across to the last column of data. Select the chart Choose Chart>Source Data On the Series tab, for Category (X) axis label...

linked graphs not printing correctly in word
my linked graphs are not printing correctly. They repeat the same graph several times even though they print preview correctly. Thank you, Keith ...

creating a directed graph from XY scatter
Would like to use XL to create a connected graph ( finance calls it a binomial lattice). X axis iis the time period t=1,2,3,...T, Y is value. XY scatter plots points OK. Need to connect points (nodes)--each node except for t=T has two lines coming out of it. A 3-peiod example is shown below. Eventually need to generalilze. Data is reference T Value NOde 1... 0 100 NODE 2... 1 110 NODE 3... 1 90 NODE 4... 2 121 NODE 5... 2 100 NODE 6... 2 81 FOR NOW: want a line to connect NODE 1 to NODE 2 want a line to connect NODE 1 to NODE 3 want a line to connect NODE 2 to NODE 4 want a li...

Trendline
The Excel trendline equation generator is seemingly incorrect in many instances - espectially at the higher end polynomial fits... is there a fix for this problem? While it is possible that you have run into a problem with the trendline generator, in more cases the problem lies in how one uses / interprets the result. Maybe, if you share more information about the problem that you have discovered... -- Regards, Tushar Mehta www.tushar-mehta.com Excel, PowerPoint, and VBA add-ins, tutorials Custom MS Office productivity solutions In article <0be201c490fe$7e9d56b0$a401280a@phx.gbl&...

Adding a text/line from Drawbox onto graph
Hi, I have a graph that is being updated weekly, so every time the X-axis extends. I add comment texts and / or pointers/lines to the graph from time to time. The problem I have is that each time I expand the graph (X range increases), I have to manually move the comments - because the graph is being rescaled. Is there any way to link the additional elements on the plot to be moved with the X-scale? Any suggestions? Many thanks, Marcin Hi, Replace the textbox comments with a comment in a Data Label. If you only want one comment, select the series, then single click the data poin...