+ Reply to Thread
Results 1 to 6 of 6

Outlier removal

  1. #1
    Registered User
    Join Date
    12-30-2016
    Location
    Cleveland, Ohio
    MS-Off Ver
    2016
    Posts
    3

    Question Outlier removal

    I have two columns of numbers, A and B. A is just the point number (1, 2, 3, etc.) and B is the corresponding reading number. The reading numbers have massive outliers that when put as a scatter plot make the rest of the values unreadable (for example most values are small decimals but the outliers could be as large as 70). Is there a way to remove numbers outside a certain range while still keeping their corresponding point numbers (so the plot would just have a blank space where the outliers were rather than shifting the other data to fill in the gap. The data sets change constantly (one might be 900 points, the next could be 1100) so I essentially need to make a sheet I can paste the data into and have it remove the outliers and plot automatically.

    I have tried using the basic filters but they were giving me the problem with filling in the gaps of the outliers like they never existed and were making plotting difficult and results inconsistent from data set to data set

    sorry if its tough to understand, if any clarification is needed let me know

  2. #2
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,891

    Re: Outlier removal

    The main thing that you need is a criterion to define an outlier. had you a simple numerical cut-off in mind? If so, does something like this meet your needs?

    or do you need a more statistically based definition of an outlier?
    Attached Files Attached Files
    Glenn




    None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU.

    Temporary addition of accented to illustrate ongoing problem to the TT: L? fh?ile P?draig sona dhaoibh

  3. #3
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,891

    Re: Outlier removal

    Here is an alternative (same end result!!), but using a more robust approach.

    The formula calculates the first and third quartiles of the data (Q1 and Q3), and calculates the inter-quartile range (IQR) by subtracting one from the other.

    It then subtracts 1.5 IQR from Q1 and adds 1.5 IQR to Q3. If the value in column B is greater than Q1-1.5*IQR and less than Q3 + 1.5*IQR, it is NOT an outlier and is accepted.

    If it's outside that range the formula returns #N/A, an error which is ignored when drawing the chart.

    =IF(B1> QUARTILE.INC($B$1:$B$20,1)-1.5*QUARTILE.INC($B$1:$B$20,3)-QUARTILE.INC($B$1:$B$20,1),IF(B1< QUARTILE.INC($B$1:$B$20,3)+1.5*QUARTILE.INC($B$1:$B$20,3)-QUARTILE.INC($B$1:$B$20,1),B1,NA()))
    Attached Files Attached Files

  4. #4
    Registered User
    Join Date
    12-30-2016
    Location
    Cleveland, Ohio
    MS-Off Ver
    2016
    Posts
    3

    Re: Outlier removal

    I think that might be perfect. I have already calculated my limits using quartiles and basic stats so I can just sub those in for the 1 value you used.

    Thank you so much for your help Glenn

  5. #5
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,891

    Re: Outlier removal

    Glad to have helped...

  6. #6
    Registered User
    Join Date
    12-30-2016
    Location
    Cleveland, Ohio
    MS-Off Ver
    2016
    Posts
    3

    Re: Outlier removal

    ..........
    Last edited by will.im.not; 12-30-2016 at 11:12 AM.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Outlier Formula
    By JamieTabone in forum Excel General
    Replies: 21
    Last Post: 03-21-2015, 08:32 PM
  2. [SOLVED] Plotting outlier in Excel bar chart
    By bstrides in forum Excel Charting & Pivots
    Replies: 2
    Last Post: 02-11-2015, 12:53 PM
  3. [SOLVED] Formula for averaging just the pos or neg and ommitting the outlier
    By Brennen81 in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 09-13-2013, 11:21 PM
  4. Excluding outlier value based on average
    By abhi254 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 07-31-2013, 09:51 AM
  5. Excluding outlier value based on average
    By abhi254 in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 07-30-2013, 03:44 AM
  6. Conditional format outlier 2 standard dev
    By Cicada in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 07-27-2011, 06:11 PM
  7. How do I test outlier in Excel?
    By Koos jubileert in forum Excel General
    Replies: 1
    Last Post: 08-30-2005, 10:05 AM

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