+ Reply to Thread
Results 1 to 10 of 10

Averaging while removing outliers

  1. #1
    Registered User
    Join Date
    09-04-2011
    Location
    Australia
    MS-Off Ver
    Excel 2003
    Posts
    5

    Averaging while removing outliers

    Hi Everyone,

    I am looking for a way to average my data, while removing any outliers present. Currently the raw data has time in columns and 16 data points for each time across in a row. I would like to average those 16 data points, but also remove any outliers (+/- 2 standard deviations or ><1.5xIQR is fine).

    I have attached a copy of some of the data.

    Thank you.

  2. #2
    Forum Contributor
    Join Date
    08-01-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2007
    Posts
    182

    Re: Averaging while removing outliers

    Hi Spets,

    Welcome to the forum. Unfortunately the file is not attached. Please reattach the file.

    Cheers-

  3. #3
    Registered User
    Join Date
    09-04-2011
    Location
    Australia
    MS-Off Ver
    Excel 2003
    Posts
    5

    Re: Averaging while removing outliers

    Oops! Here it is.
    Attached Files Attached Files

  4. #4
    Forum Contributor
    Join Date
    08-01-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2007
    Posts
    182

    Re: Averaging while removing outliers

    Ok... This involves Maths and I am not good in that... Though I have couple of questions, how will you calculate Outliers... as I see from your data in Row 4 the min and max range from -43865 to 38867... If you can tell me how to calculate the outliers, I may be able to help you.

  5. #5
    Registered User
    Join Date
    09-04-2011
    Location
    Australia
    MS-Off Ver
    Excel 2003
    Posts
    5

    Re: Averaging while removing outliers

    Thanks Inayat, I was thinking of finding the inter quartile range
    (IQR) for the data and then using the cut off values of 1.5xIQR above and below. So for t=0; q1=QUARTILE(B44:Q44,1); q3=QUARTILE(B44:Q44,3); IQR=q3-q1

    Hope this helps

  6. #6
    Forum Contributor
    Join Date
    08-01-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2007
    Posts
    182

    Re: Averaging while removing outliers

    Hi Spets,

    Please see if the attached sheet helps. Just to explain a little... I have calculated IQR.. then i have given a .5 higher range and .5 lower range than IQR... Let us say if the IQR is 4 then the range will be from 2 to 6 (both inclusive)... Any value below 2 and any value above 6 will not be included while average is being calculated... I hope that is right...

    Please let me know if this works for you.
    Attached Files Attached Files

  7. #7
    Registered User
    Join Date
    09-04-2011
    Location
    Australia
    MS-Off Ver
    Excel 2003
    Posts
    5

    Re: Averaging while removing outliers

    Thank you very much inayat. That is almost what I need. Instead of using 0.5 below and above IQR, I would like to use 1.5 above and below. When I change the values in the "S" column, I get some div/0 values in the "Averages" column. So close yet so far

  8. #8
    Forum Contributor
    Join Date
    08-01-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2007
    Posts
    182

    Re: Averaging while removing outliers

    Hi Spets,

    Here you go... I have added another column where I have taken 1.5 of Quartile.

    Tell me if this works.

    Cheers-
    Inayat

    If this answers your question, please mark the thread as Solved. Click the Edit button on your first post in the thread, Click Go Advanced, select [SOLVED] from the Prefix dropdown, then click Save Changes.
    Attached Files Attached Files

  9. #9
    Registered User
    Join Date
    09-04-2011
    Location
    Australia
    MS-Off Ver
    Excel 2003
    Posts
    5

    Re: Averaging while removing outliers

    Thank you again Inayat, but for some reason the averages don't seem correct as they are about 10 timea higher than expected. The
    =AVERAGEIFS(B4:Q4,B4:Q4,">="&T4,B4:Q4,"<="&U4
    formula is very helpful, could you explain the logic in please. Sorry to sound like a total noob, but Excel and I don't get along very well.

  10. #10
    Forum Contributor
    Join Date
    08-01-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2007
    Posts
    182

    Re: Averaging while removing outliers

    I checked the data and it works perfectly. See what AverageIFs does is that it i will calculate the average based on some criteria. The criteria that I have given here is that it should take the average of those cells that are above the min quartile and below the maximum quartile. I manually checked couple of rows after removing the data that was not wihtin the range and the average was the same as the formula.

    I see that you have Excel 2003. I am not sure if averageifs works in that. Can you please tell me what is the result you are getting in Row 4. As per the formula it should be 11248.69.

    One more thing that you need to check is the 1.5 above or below. please check if that figure is correct or not.
    Last edited by inayat; 09-26-2011 at 12:04 AM.

+ 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