+ Reply to Thread
Results 1 to 4 of 4

Need help with deleting data points within a range from trend line

  1. #1
    Registered User
    Join Date
    05-17-2019
    Location
    Hong Kong
    MS-Off Ver
    365 ProPlus
    Posts
    2

    Question Need help with 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

    Picture1.png

  2. #2
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    10,818

    Re: Need help with deleting data points within a range from trend line

    I would probably:

    1) Get an equation/formula/function for the two red lines/curves.
    2) Add a helper column or two to my table that will either compute the value for the red curves at each point or determine if the value is outside of the red lines.
    3) Use a filter (autofilter or advanced filter) to hide/remove all of the points that are outside of the red lines. https://www.wikihow.com/Use-AutoFilter-in-MS-Excel

    It's difficult to give specifics based only on pictures of charts, but that's how I might do this. Let us know what specific help you would need to implement something like this.
    Quote Originally Posted by shg
    Mathematics is the native language of the natural world. Just trying to become literate.

  3. #3
    Registered User
    Join Date
    05-17-2019
    Location
    Hong Kong
    MS-Off Ver
    365 ProPlus
    Posts
    2

    Re: Need help with deleting data points within a range from trend line

    Hi MrShorty,

    Thank you for your reply.

    Problem is I am myself not an expert Excel user. I usually try to perform tricks that come to my mind when I face such problems but I can't think of one to this issue!
    Basically, I think the answer to my issue would be your second point. "2) Add a helper column or two to my table that will either compute the value for the
    red curves at each point or determine if the value is outside of the red lines." but how would I determine if the value is outside the red line?

  4. #4
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    10,818

    Re: Need help with deleting data points within a range from trend line

    I am a little surprised that -- assuming you are able to perform step 1 -- that you are having trouble with step 2. Step 1 seems like the difficult step. Once you have values for the red curves, then determining which values to accept/reject should be a simple boolean test. Either:
    2a) If this value is greater than the upper red value or less then the lower red value (If this value is not between the red curves), then hide/remove this point.
    2b) If this value is between the upper red value and the lower red value, then keep/show this point.

    Step 2 could either be done in helper columns or step 2 could also be part of the filter criteria.

    Perhaps a very simple example would help illustrate the steps. Starting in a blank workbook or tab:
    A) In A1 enter "value". In B1 enter "lower limit". In C1 enter "upper limit". In D1 enter "test".
    B) In A2 enter =RAND(). In B2 enter 0.25, In C2 enter 0.75. In D2 enter =AND(B2<A2,A2<C2)
    C) Select A2:D2 and copy/paste/fill down several rows.

    This will generate a bunch of random numbers then tell us which numbers are between 0.25 and 0.75. We can add a filter to this range that will show only those rows where D2 is TRUE (though we need to remember that the random numbers will update each time Excel recalculates, but the filter won't). I'm sure that is much simpler than your actual calculations, but it should illustrate the programming steps I would expect to go through.

    Are we getting any closer?

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1