
1. Re: How to have a trendline ignore 0 values
Wayne Contello Feb 4, 2013 3:04 PM in response to ironfromcastmake a column that copies values from another column when the checkbox is set otherwise it contains nothing:
D2=IF(C2=TRUE, A2, "")
E2=IF(C2=TRUE, B2, "")
The chart takes its input like:

2. Re: How to have a trendline ignore 0 values
ironfromcast Feb 4, 2013 4:10 PM in response to Wayne ContelloUnfortunately it is a bit more complicated than this, though your answer makes sense. My graph is based on a set of numbers that is controled by five layers of "if" formulas that rely on checkboxes. Currently, if any of the five boxes becomes unchecked then numbers on a different table change to zero, though those numbers may be added to others in the table that I use for the graph.
To be more specific, I have 50 terms in my graph which are fed by the sum of 100 values each. Each one of those 100 values has a checkbox, but also has a more complex formula that can turn the whole columns to all zeros if certain other checkboxes are unchecked.
I'm sorry if this is unclear, but I wonder if I can change the way the graph functions, instead of the table becuase I would like to see the zero values in the table but not have them appear on the graph. It seems as though Inspector has a place for forumlas for X and Y Data, but I'm not sure how I could use one in this context. Here is the image of inspector with the address for the Series Data.
If this only can use a single address and no formula, then maybe I could set up a second table that mirrors the first to use for the graph and throw in an if formula base on yours that says:
Table 2 :: C2=IF(Table 1 :: C2=0, "", Table 1 :: C2). This seems to work. It just means I now have a dublicate table.
Thanks for your help. If this is all I can do, your equation allowed me to figure out this much.

3. Re: How to have a trendline ignore 0 values
Wayne Contello Feb 4, 2013 5:29 PM in response to ironfromcastiron,
in this case make a new table that contains only and X and Y column. Make the cells in the X column refer to the actual X column and only take that value when the value is greater than 0... same for the Y. Then make your chart refer to this new table.
=if(<SRC CELL>>0, <SRC CELL>, "")