+ Reply to Thread
Results 1 to 7 of 7

Trimmed mean doesn't give expected result

  1. #1
    Registered User
    Join Date
    09-03-2012
    Location
    Vincentia, Australia
    MS-Off Ver
    Excel 2010
    Posts
    3

    Trimmed mean doesn't give expected result

    I used the trimmean function on the following array of numbers:

    50, 20,20, 20, 20, 5

    I specified that I wanted to remove one-third of observations. So, i expected the formula would remove 50 and 5 and calculate the average of the remaining numbers.

    But the answer obtained wasn't 20.

    Why not?

  2. #2
    Forum Moderator zbor's Avatar
    Join Date
    02-10-2009
    Location
    Croatia
    MS-Off Ver
    365 ProPlus
    Posts
    15,576

    Re: Trimmed mean doesn't give expected result

    Are they in one cell or in multiple cells?

  3. #3
    Forum Contributor
    Join Date
    08-29-2012
    Location
    Slough
    MS-Off Ver
    Excel 2007
    Posts
    469

    Re: Trimmed mean doesn't give expected result

    Hi Winton,

    Is the answer you are currently getting 22.5?

    TRIMMEAN rounds down to the nearest number of two(to exclude) so using 0.34 is easier to use for a third than 0.333 etc.

    That should give you the correct answer.

    EDIT: Of course another way around it would be to set up a seperate cell with the formula:
    Please Login or Register  to view this content.
    say you put that formula in cell A1 and had the number in B1 through to B6 the next code would be:
    Please Login or Register  to view this content.
    Regards

    Danny
    Last edited by DannyJ; 09-03-2012 at 05:23 AM.

  4. #4
    Registered User
    Join Date
    09-03-2012
    Location
    Vincentia, Australia
    MS-Off Ver
    Excel 2010
    Posts
    3

    Re: Trimmed mean doesn't give expected result

    Seperate cells.

  5. #5
    Forum Guru Andy Pope's Avatar
    Join Date
    05-10-2004
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    20,419

    Re: Trimmed mean doesn't give expected result

    From Help.
    TRIMMEAN rounds the number of excluded data points down to the nearest multiple of 2. If percent = 0.1, 10 percent of 30 data points equals 3 points. For symmetry, TRIMMEAN excludes a single value from the top and bottom of the data set.

    For you data set you need to use 34%
    Cheers
    Andy
    www.andypope.info

  6. #6
    Forum Contributor
    Join Date
    08-29-2012
    Location
    Slough
    MS-Off Ver
    Excel 2007
    Posts
    469

    Re: Trimmed mean doesn't give expected result

    Quote Originally Posted by Andy Pope View Post
    From Help.
    TRIMMEAN rounds the number of excluded data points down to the nearest multiple of 2. If percent = 0.1, 10 percent of 30 data points equals 3 points. For symmetry, TRIMMEAN excludes a single value from the top and bottom of the data set.

    For you data set you need to use 34%
    If you need it to be precise though you can still use a third. I have tested it with one cell using 0.3 recurring and it does work. You just have to force excel to work the recurring bit out for itself.

    Danny

  7. #7
    Registered User
    Join Date
    09-03-2012
    Location
    Vincentia, Australia
    MS-Off Ver
    Excel 2010
    Posts
    3

    Re: Trimmed mean doesn't give expected result

    Thanks Andy and Danny. It didn't occur to me to specify recurring.

+ 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