+ Reply to Thread
Results 1 to 6 of 6

How to Calculate Trimmed Mean without Rounding

  1. #1
    Registered User
    Join Date
    02-03-2015
    Location
    Washington
    MS-Off Ver
    Office 2013
    Posts
    3

    How to Calculate Trimmed Mean without Rounding

    I am trying to calculate the mean of an array, but want to exclude 5% of the outliers. Since the trimmed mean formula rounds down, the results of the trimmed mean is sometimes the same as the mean. I would like to know how to create a formula for the true trimmed mean without rounding.

    Example
    Data points: 7 7 8 9 9 10 10 10 15 17 17 19 20 53

    MEAN = 15.07
    TRIMMEAN = 15.07 (=TRIMMEAN(array,0.05))

    In reality, I think the trimmed mean should exclude the data points 7 and 53. Therefore, the trimmed mean would be 12.58 (=AVERAGE(array)).

  2. #2
    Forum Expert
    Join Date
    05-01-2014
    Location
    California, US
    MS-Off Ver
    Excel 2010
    Posts
    1,795

    Re: How to Calculate Trimmed Mean without Rounding

    Quote Originally Posted by duaxe_thore View Post
    I am trying to calculate the mean of an array, but want to exclude 5% of the outliers. Since the trimmed mean formula rounds down, the results of the trimmed mean is sometimes the same as the mean. I would like to know how to create a formula for the true trimmed mean without rounding.
    Example
    Data points: 7 7 8 9 9 10 10 10 15 17 17 19 20 53
    MEAN = 15.07
    TRIMMEAN = 15.07 (=TRIMMEAN(array,0.05))
    In reality, I think the trimmed mean should exclude the data points 7 and 53. Therefore, the trimmed mean would be 12.58 (=AVERAGE(array)).
    Nitpick: TRIMMEAN does not remove "outliers" per se. In your example, only 53 is an outlier by most methods of determination.

    TRIMMEAN will always round down the number to exclude to the nearest multiple of 2. We cannot change that. So it is unclear exactly what you want.

    If you want to exclude a mininum of 2 (1 from each end), assuming an array of 3 or more:

    =TRIMMEAN(array,MAX(2.1/COUNT(array),5%))

    I use 2.1 (arbitrarily) instead of 2 to avoid any binary arithmetic anomalies.

    On the other hand, if you want to round up the number to exclude to the nearest multiple of 2, again assuming an array of 3 or more:

    =TRIMMEAN(array,EVEN(COUNT(array)*5%)*1.1/COUNT(array))

    Again, I multiply by 1.1 (arbitrarily) to avoid any binary arithmetic anomalies.

    Change 5% accordingly.
    Last edited by joeu2004; 02-03-2015 at 04:20 PM. Reason: cosmetic

  3. #3
    Registered User
    Join Date
    02-03-2015
    Location
    Washington
    MS-Off Ver
    Office 2013
    Posts
    3

    Re: How to Calculate Trimmed Mean without Rounding

    Thanks!

    Problem trying to solve: Creating a formula for reporting that will eliminate outliers based upon the percentage selected (5% is arbitrary) of outliers to exclude. I would like the formula to not require changes other than % to exclude. The problem with the trimmedmean formula on it's own is that it rounds. In certain cases, it will not exclude the outliers (tail ends) we want it to.

    The formula you provided worked great to achieve 12.58, but I think we would need to change 2.1 each time if I used it =TRIMMEAN(array,MAX(2.1/COUNT(array),5%))

    Questions on this formula: =TRIMMEAN(array,MAX(2.1/COUNT(array),5%))

    Does 2.1 need to be changed for each data set to be the numbers of cells to exclude?
    • Example: Using 25% instead of 5%, (0.25*14), it should exclude 3.5 from each end. If I round up to 4 using the original data set, the trimmed mean would be 11.83 =AVERAGE(9,10,10,10,15,17)
    7 7 8 9 9 10 10 10 15 17 17 19 20 53
    • If I change the formula to =TRIMMEAN(array,MAX(8.1/COUNT(array),25%)) I will get 11.83; however, I would like to not have to change 2.1 to 8.1 each time.
    • I also noticed that changing using 5% instead of 25% =TRIMMEAN(array,MAX(8.1/COUNT(array),5%)) still results in 11.83

    Essentially I want the results of your formula =TRIMMEAN(array,MAX(2.1/COUNT(array),5%)) to calculate automatically by not having to calculate and change 2.1 or 8.1 or etc.. I would like to simply change 5% to 25% and it update the results accordingly.

    I hope this makes sense. Thank you!

  4. #4
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    16,164

    Re: How to Calculate Trimmed Mean without Rounding

    If you want to exclude 5% of the outliers you need to make the number ".025" in the formula.


    On http://www.excelfunctions.net/Excel-...-Function.html it says:
    "Note that, the specified percentage is the total percentage of values to be excluded. This percentage is divided by two, to get the number of values that are removed from each end of the range. "
    One test is worth a thousand opinions.
    Click the * Add Reputation below to say thanks.

  5. #5
    Forum Expert
    Join Date
    05-01-2014
    Location
    California, US
    MS-Off Ver
    Excel 2010
    Posts
    1,795

    Re: How to Calculate Trimmed Mean without Rounding

    Quote Originally Posted by duaxe_thore View Post
    Problem trying to solve: Creating a formula for reporting that will eliminate outliers based upon the percentage selected (5% is arbitrary) of outliers to exclude.
    As I said previously, TRIMMEAN has nothing to do with excluding "outliers" per se, as understood by statisticians. In your example, you said you intended to exclude 7 and 53. But one 7 is no different from the other 7; if one 7 is an "outlier", both 7s are. (The fact is: neither 7 is an outlier, statistically speaking.)

    Bottom line: If you are doing this for any professional purpose and you intend to use statistical "good practices", TRIMMEAN is not the correct method for achieving your purpose.

    Quote Originally Posted by duaxe_thore View Post
    I would like the formula to not require changes other than % to exclude.
    The formulas that I provided are designed that way.

    In both formulas, 5% is intended to be whatever %exclusion factor you want: 5%, 25%, etc.

    2.1 and 1.1 are constants. They do not change when the size of the array and/or the %exclusion factor changes.

    Quote Originally Posted by duaxe_thore View Post
    The formula you provided worked great to achieve 12.58, but I think we would need to change 2.1 each time if I used it =TRIMMEAN(array,MAX(2.1/COUNT(array),5%))
    No. The use of 2.1 ensures that TRIMMEAN excludes at least 2. If 5% of the array size is greater than 2, TRIMMEAN will continue to round down to the nearest even number -- but not less than 2 because of the MAX function.

    As I explained previously, 2.1 should really be just 2. I increase it slightly to avoid any adverse effect of computer binary arithmetic. Mathematically, COUNT(array)*(2/COUNT(array)) is obviously 2. But in computer binary arithmetic, the result can be infinitesimally less than 2. In that case, TRIMMEAN might round down to zero.

    Quote Originally Posted by duaxe_thore View Post
    Example: Using 25% instead of 5%, (0.25*14), it should exclude 3.5 from each end. If I round up to 4 [...]
    It appears that you want to round up to the nearest even number, not simply specify a minimum of 2. In that case, you should use the other formula, to wit:

    =TRIMMEAN(array,EVEN(COUNT(array)*5%)*1.1/COUNT(array))

    Change 5% to whatever %exclusion factor you want: 5%, 25%, etc.

    As I explained previously, multiplying by 1.1 is a trick to avoid any adverse effect of computer binary arithmetic. Ostensibly, we just want EVEN(COUNT(array)*5%)/COUNT(array). Mathematically, COUNT(array)*(EVEN(COUNT(array)*5%)/COUNT(array)) is obviously EVEN(COUNT(array)*5%), and the EVEN function rounds up to the nearest multiple of 2. But again, in computer binary arithmetic, the result of COUNT(array)*(EVEN(COUNT(array)*5%)/COUNT(array) might be infinitesimally less than EVEN(COUNT(array)*5%), and TRIMMEAN would round down instead of effectively round up.

    Quote Originally Posted by duaxe_thore View Post
    Example: Using 25% instead of 5%, (0.25*14), it should exclude 3.5 from each end. If I round up to 4 using the original data set, the trimmed mean would be 11.83 =AVERAGE(9,10,10,10,15,17)
    7 7 8 9 9 10 10 10 15 17 17 19 20 53
    That is incorrect. The 25% exclusion factor, rounded up, would exclude a total of 4 data points, 2 from each end. That is explained in the TRIMMEAN help page, to wit:

    "For example, if percent = 0.2, 4 points are trimmed from a data set of 20 points (20 x 0.2): 2 from the top and 2 from the bottom of the set."

    It sounds like instead of specifying a percentage to exclude, you would like to specify a number to exclude from each end. Try the following:

    =TRIMMEAN(array,(4*2)*1.1/COUNT(array))

    Change 4 to whatever number of data points you want to exclude from each end.

    2 and 1.1 are constants; they do not change.

    Again, it should be sufficient to write simply 4*2/COUNT(array). (And of course, you are free to multiply 4 by 2 in your head, entering 8 instead.)

    But IMHO, multiplying by 1.1 (or some number close to, but larger than 1) is prudent to do in order to avoid adverse effects of computer binary arithmetic.
    Last edited by joeu2004; 02-04-2015 at 11:29 PM. Reason: cosmetic

  6. #6
    Registered User
    Join Date
    02-03-2015
    Location
    Washington
    MS-Off Ver
    Office 2013
    Posts
    3

    Re: How to Calculate Trimmed Mean without Rounding

    Thank you for your help! This worked great.

+ 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. Replies: 2
    Last Post: 07-31-2013, 07:17 AM
  2. Trim and Keep the Trimmed Data
    By sally t in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-06-2005, 03:05 PM
  3. Trim and Keep the Trimmed Data
    By sally t in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-06-2005, 12:05 AM
  4. [SOLVED] Trim and Keep the Trimmed Data
    By sally t in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-05-2005, 11:05 PM
  5. Trim and Keep the Trimmed Data
    By sally t in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-05-2005, 10:05 PM

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