Home

Deleting data points within a range from trend line

%3CLINGO-SUB%20id%3D%22lingo-sub-584258%22%20slang%3D%22en-US%22%3EDeleting%20data%20points%20within%20a%20range%20from%20trend%20line%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-584258%22%20slang%3D%22en-US%22%3E%3CP%3EHello%2C%20I%20have%20the%20attached%20image%20(left)%20of%20more%20than%2045k%20data%20points%20and%20I%20want%20to%20delete%20all%20the%20points%20within%20a%20range%20from%20the%20trend%20line%20to%20have%20it%20like%20the%20one%20on%20the%20right%20side%20(only%20the%20points%20between%20the%20two%20red%20lines%2C%20deleting%20all%20other%20points%20that%20fall%20out).%20I%20can't%20think%20of%20a%20solution%20to%20this%2C%20so%20please%20let%20me%20know%20if%20you%20know%20a%20solution%20or%20a%20way%20around.%20Thanks%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-center%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F114231i396E4EA337AEFC39%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22Picture1.png%22%20title%3D%22Picture1.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-584258%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-612566%22%20slang%3D%22en-US%22%3ERe%3A%20Deleting%20data%20points%20within%20a%20range%20from%20trend%20line%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-612566%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F344272%22%20target%3D%22_blank%22%3E%40jkharraz2%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EInstead%20of%20plotting%20y%20against%20x%20and%20adding%20a%20trendline%2C%20add%20the%20trend%20data%20to%20the%20table%20using%26nbsp%3B%3C%2FP%3E%3CP%3E%3DTREND(y%2C%20x)%3C%2FP%3E%3CP%3EPlot%20this%20against%20x%20as%20a%20line%20with%20no%20markers.%3C%2FP%3E%3CP%3EFilter%20the%20y%20values%20using%20a%20formula%20of%20the%20sort%3C%2FP%3E%3CP%3E%3D%20IF(%20ABS(y-t)%3CA%3E%3C%2FA%3E%3C%2FP%3E%3CP%3Eor%3C%2FP%3E%3CP%3E%3CSPAN%3E%3D%20IF(%20ABS(y-t)%2C%20y%2C%20-100%20)%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%3EAn%20up%20to%20date%20version%20of%20Excel%20will%20ignore%20the%20'%23N%2FA's%20but%20older%20versions%20will%20scatter%20markers%20along%20the%20x-axis.%26nbsp%3B%20If%20that%20happens%20choose%20a%20fictional%20for%20y%20value%20that%20will%20be%20outside%20the%20plot%20region.%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E
jkharraz2
Occasional Visitor

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

 

 

Picture1.png

 

1 Reply

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

Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
46 Replies
Extentions Synchronization
Deleted in Discussions on
3 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
30 Replies
Security Community Webinars
Valon_Kolica in Security, Privacy & Compliance on
13 Replies
flashing a white screen while open new tab
Deleted in Discussions on
14 Replies