+ Reply to Thread
Results 1 to 4 of 4

Finding Outliers and Obtaining the Data

  1. #1
    Registered User
    Join Date
    03-19-2012
    Location
    Boston, MA
    MS-Off Ver
    Excel 2010
    Posts
    2

    Finding Outliers and Obtaining the Data

    Hi Everyone,

    I need your assistance working on a large data set that I am working with. I am looking for a restrictive way of identifying the outliers in my data. I am currently using the median and an array formula to identify outliers that are two deviations from the Median Absolute Deviation. This gives me both positive and negative outliers. I used an array formula to distinguish between normal data and an outlier. However outlier shows up for both positive data and negative data. How do I distinguish between the two types of outliers?
    Secondly, is there a better approach to finding outliers and how do I go about capturing the data.
    Any assistance you have would be greatly appreciated.

    Thanks.

  2. #2
    Forum Contributor darknation144's Avatar
    Join Date
    01-24-2012
    Location
    London
    MS-Off Ver
    Microsoft Excel 365 MSO
    Posts
    555

    Re: Finding Outliers and Obtaining the Data

    Do you want to tell the difference between positive and negative outliers or just make them all postive. If you want to do the latter use ABS()

  3. #3
    Registered User
    Join Date
    03-19-2012
    Location
    Boston, MA
    MS-Off Ver
    Excel 2010
    Posts
    2

    Re: Finding Outliers and Obtaining the Data

    Hi darknation144

    I'll give you an example using an array formual the following come back as outliers: 3.08 and 27.77. The MAD (Median Absolute Deviation) is 7.45, in this instance I am more concerned about the 3.08 outlier than I am about the 27.77 positive outlier. The 3.08 outlier is a detrimental outlier in this instance.


    Thanks

  4. #4
    Forum Contributor darknation144's Avatar
    Join Date
    01-24-2012
    Location
    London
    MS-Off Ver
    Microsoft Excel 365 MSO
    Posts
    555

    Re: Finding Outliers and Obtaining the Data

    You need a logical formula to check against. So say highlight numbers that are 2 standard deviations outside the median or something like that. You come up with a logic based test and we can help you.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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