Skip navigation

How to have a trendline ignore 0 values

324 Views 3 Replies Latest reply: Feb 4, 2013 5:29 PM by Wayne Contello RSS
ironfromcast Calculating status...
Currently Being Moderated
Feb 4, 2013 1:35 PM

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?



  • Wayne Contello Level 6 Level 6 (12,650 points)
    Currently Being Moderated
    Feb 4, 2013 3:04 PM (in response to ironfromcast)

    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

  • Wayne Contello Level 6 Level 6 (12,650 points)
    Currently Being Moderated
    Feb 4, 2013 5:29 PM (in response to ironfromcast)



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


More Like This

  • Retrieving data ...

Bookmarked By (0)


  • This solved my question - 10 points
  • This helped me - 5 points
This site contains user submitted content, comments and opinions and is for informational purposes only. Apple disclaims any and all liability for the acts, omissions and conduct of any third parties in connection with or related to your use of the site. All postings and use of the content on this site are subject to the Apple Support Communities Terms of Use.