3 Replies Latest reply: Feb 4, 2013 5:29 PM by Wayne Contello
ironfromcast Level 1 Level 1 (0 points)

I have a scatterpoint graph that is connected to checkbox formulas. I want to be able to get a good trendline when I uncheck some of the boxes and those values go to 0. Currently, when I use the moving average it factors in the zero, but I just want the 0< values. I have also tried using a polynomial trendline, but this does not seem to provide a very dynamic line. Even after removing multiple values it bearly budges.

Is there a way to maybe use the Series Data (in this case the X data) to create such a formula? or maybe some other options?



  • 1. Re: How to have a trendline ignore 0 values
    Wayne Contello Level 6 Level 6 (13,620 points)

    make a column that copies values from another column when the checkbox is set otherwise it contains nothing:


    Screen Shot 2013-02-04 at 5.03.25 PM.png


    D2=IF(C2=TRUE, A2, "")

    E2=IF(C2=TRUE, B2, "")


    The chart takes its input like:

    Screen Shot 2013-02-04 at 5.04.32 PM.png

  • 2. Re: How to have a trendline ignore 0 values
    ironfromcast Level 1 Level 1 (0 points)

    Unfortunately 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.


    Screen shot 2013-02-04 at 3.45.05 PM.png


    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 Level 6 Level 6 (13,620 points)



    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>, "")