Forum Discussion

jkharraz2's avatar
jkharraz2
Copper Contributor
May 18, 2019

Deleting data points within a range from trend line

Hello, I have the attached image (left) of more than 45k data points and I want to delete all the points within a range from the trend line to have it like the one on the right side (only the points between the two red lines, deleting all other points that fall out). I can't think of a solution to this, so please let me know if you know a solution or a way around. Thanks

 

 

 

excel

1 Reply

  • PeterBartholomew1's avatar
    PeterBartholomew1
    Silver Contributor
    May 18, 2019

    jkharraz2 

    Instead of plotting y against x and adding a trendline, add the trend data to the table using 

    =TREND(y, x)

    Plot this against x as a line with no markers.

    Filter the y values using a formula of the sort

    = IF( ABS(y-t)<a, y, NA() )

    or

    = IF( ABS(y-t), y, -100 )

    An up to date version of Excel will ignore the '#N/A's but older versions will scatter markers along the x-axis.  If that happens choose a fictional for y value that will be outside the plot region.

Resources