Forum Discussion
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 ...
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.