Calculating averag but leaving highest and lowest number out ?

Discussion in 'Recreational Math' started by hihihi, Apr 16, 2005.

  1. hihihi

    hihihi Guest

    Hi..

    Is there a function in a spreadsheet to calculate the average of some
    numbers, but leaving the highest and lowest numbers out ?
    Like : 1, 5, 3, 6, 2, then the 1 and 6 would not be used.
    And the average of 5, 3 and 2 would be calculated.

    Is there a function name in a speadsheet (like gnumeric) which can do this ?

    Thanks..
     
    hihihi, Apr 16, 2005
    #1
    1. Advertisements

  2. hihihi

    Dave Langers Guest

    Is there a function in a spreadsheet to calculate the average of some
    How about something like
    (SUM-MIN-MAX)/(COUNT-2)
    where you replace these commands by those that your spreadsheet supports
    (surely it will support something equivalent to SUM,MIN,MAX and COUNT).
     
    Dave Langers, Apr 16, 2005
    #2
    1. Advertisements

  3. hihihi

    mensanator Guest

    this ?

    If your data is in column A

    =(SUM(A:A)-MIN(A:A)-MAX(A:A))/(COUNT(A:A)-2)
     
    mensanator, Apr 16, 2005
    #3
  4. Sometimes a geometrical mean is used because it is more insensitive to
    outliers!
     
    Casey Hawthorne, Apr 16, 2005
    #4
  5. hihihi

    hihihi Guest

    Yep, works, thanks..
     
    hihihi, Apr 18, 2005
    #5
  6. hihihi

    Dana Guest

    In Excel, another option would be:

    =TRIMMEAN(A1:A5,2/5)

    Where the idea is you are removing 2 (high & low) of the 5 items.
    You could include the Count function in a more general function...

    =TRIMMEAN(rng, 2/COUNT(rng))

    HTH :>)
     
    Dana, Apr 18, 2005
    #6
  7. hihihi

    hihihi Guest

    The trimmean in gnumeric removes not the highest and lowest this way.
    It removes the first and last in the list of numbers.
    You cloud first sort the numbers from low to high.
    But that has downsides in my case :)
     
    hihihi, Apr 18, 2005
    #7
  8. hihihi

    mwelinder Guest

    Gnumeric's TRIMMEAN most certainly sorts. The example from the
    beginning
    of this thread yields 3.333... as expected.

    If there's something specific wrong -- quite possible -- please let us
    know at bugzilla.gnome.org.
     
    mwelinder, Apr 19, 2005
    #8
  9. hihihi

    hihihi Guest

    hihihi, Apr 19, 2005
    #9
  10. hihihi

    mwelinder Guest

    21.5
    http://home.wanadoo.nl/hihihi/temp/gnumeric-trimmean-perhaps-bug.gnumeric

    I get 21.875 in cells E17, E21, and E22. I believe that is the right
    number.
    However, that is on different hardware than you are likely to use and a
    look
    at the code suggests a problem. Try using 2/6+0.00000001 instead of
    2/6.
    (2/6 does not have an exact finite representation in base 2.)

    I'll fix the code.

    Thanks!

    Morten
     
    mwelinder, Apr 19, 2005
    #10
  11. hihihi

    hihihi Guest

    E17 was 22.93333, and the others 21.875 on my computer celeron 700.
    Yep, then it's 21.875 in E17 also..
    It gives 21.875 upto 2/6+1e-16.
    On 2/6+1e-17 it gives 22.93333 again.
    It's a great pleasure to have help improve gnumeric a little bit.

    ..
     
    hihihi, Apr 19, 2005
    #11
  12. hihihi

    hihihi Guest

    E17 was 22.93333, and the others 21.875 on my computer celeron 700.
    Yep, then it's 21.875 in E17 also..
    It gives 21.875 upto 2/6+1e-16.
    On 2/6+1e-17 it gives 22.93333 again.
    Great.
    You might also want to change the help text on trimmean.

    Original:
    "8 numbers are trimmed from the data set (40 x 0.2), 4 from the top and 4
    from the bottom of the set."

    Into this maybe:
    "8 numbers are trimmed from the data set (40 x 0.2), the 4 highest values
    and the 4 lowest values of the set."



    It's a great pleasure to have help improve gnumeric a little bit.

    ..
     
    hihihi, Apr 19, 2005
    #12
    1. Advertisements

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments (here). After that, you can post your question and our members will help you out.