+ Reply to Thread
Results 1 to 9 of 9

Help with Averaging

  1. #1
    Registered User
    Join Date
    07-03-2008
    Location
    Reno, NV
    Posts
    8

    Help with Averaging

    I would like to average a range of cells and throw out the lowest and highest result. I'm using something simple like this:

    =AVERAGE(O2,Q2,S2,U2,W2,Y2)

    Also, is there a way to have the average function ignore any invalid cells? Like in the above example, all of those cells may have results except for say, U2, which will show ####, in which case my average function shows up as #N/A.

    Thanks for any help!
    Last edited by cfeedback; 10-14-2008 at 03:29 PM.

  2. #2
    Forum Expert
    Join Date
    06-18-2004
    Location
    Canada
    MS-Off Ver
    Office 2016
    Posts
    1,474
    Try...

    =TRIMMEAN(IF(MOD(COLUMN(O2:Y2)-COLUMN(O2),2)=0,IF(ISNUMBER(O2:Y2),O2:Y2)),2/COUNT(O2,Q2,S2,U2,W2,Y2))

    ...confirmed with CONTROL+SHIFT+ENTER.

    Hope this helps!

  3. #3
    Registered User
    Join Date
    07-03-2008
    Location
    Reno, NV
    Posts
    8
    Seems to be working, but my question is, is there a way to get it to ignore cells in the average that have no results? IE here's a single row from my sheet:

    #NUM! #NUM! #N/A #N/A 73 73 #N/A #N/A #N/A #N/A

    first 2 columns are averaging every other column in the last results. (ie function one averages a/c/e etc and function 2 averages b/d/f etc)

    Here's another result from the same sheet:

    68.0 68.0 67 67 #N/A #N/A 68 68 70 70

    so it seems to be working if there's only one invalid result, but not mostly invalid results...if this is making any sense.

  4. #4
    Forum Expert
    Join Date
    06-18-2004
    Location
    Canada
    MS-Off Ver
    Office 2016
    Posts
    1,474
    Can you clarify the rules that need to be followed in calculating the average?

  5. #5
    Registered User
    Join Date
    07-03-2008
    Location
    Reno, NV
    Posts
    8
    Quote Originally Posted by Domenic View Post
    Can you clarify the rules that need to be followed in calculating the average?

    Ok, what I have is columns of numbers (maybe 20 or so) matched up to a name. The results are pulled from other worksheets via vlookup. The problem is that sometimes the results don't exist...and I want the average function to ignore that somehow, if possible.

    Please Login or Register  to view this content.
    As you can see in this example, he only had 2 results out of 7 sets, causing the TRIMEAN function to give the #NUM! error. It's not a huge problem, normally I just go and delete all the "#N/A"s when I'm ready to finalize my list, just seemed like maybe excel would have an easier way of handling that.

  6. #6
    Registered User
    Join Date
    10-07-2008
    Location
    Wessex
    Posts
    55
    =AVERAGE(IF((MOD(COLUMN(O2:Y2)-COLUMN(O2),2)=0)*(ISNUMBER(O2:Y2)),O2:Y2))

    array entered

  7. #7
    Forum Expert
    Join Date
    06-18-2004
    Location
    Canada
    MS-Off Ver
    Office 2016
    Posts
    1,474
    If I understood correctly, when the data contains 7 results the smallest and largest results are ignored in calculating the average. How should the average be calculating under the following circumstances...

    6 results

    5 results

    4 results

    3 results

    2 results

    1 result

    0 results

  8. #8
    Registered User
    Join Date
    07-03-2008
    Location
    Reno, NV
    Posts
    8
    Quote Originally Posted by Domenic View Post
    If I understood correctly, when the data contains 7 results the smallest and largest results are ignored in calculating the average. How should the average be calculating under the following circumstances...
    Okay, I see your point. When I'm finished, I'll have 10 data sets, and in a perfect world, it would always include at least 2 results in the average. So if there were only 3 or less valid results out of the 10, it would include all of them, and only start to throw away the highest/lowest results if there were 4 or more out of the 10 available. I may be making this more complicated than it needs to be.

    Thanks for all the help guys, this is a fantastic forum!!

  9. #9
    Forum Expert
    Join Date
    06-18-2004
    Location
    Canada
    MS-Off Ver
    Office 2016
    Posts
    1,474
    Try...

    AA2:

    =COUNT(O2,Q2,S2,U2,W2,Y2)

    AB2, confirmed with CONTROL+SHIFT+ENTER:

    =TRIMMEAN(IF(MOD(COLUMN(O2:Y2)-COLUMN(O2),2)=0,IF(ISNUMBER(O2:Y2),O2:Y2)),LOOKUP(AA2,{0,4},{0,2})/AA2)

    or

    =IF(N(AA2),TRIMMEAN(IF(MOD(COLUMN(O2:Y2)-COLUMN(O2),2)=0,IF(ISNUMBER(O2:Y2),O2:Y2)),LOOKUP(AA2,{0,4},{0,2})/AA2),0)

    Adjust the range accordingly.

    Hope this helps!

+ 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. Averaging Times Across Days
    By gmscharl in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 07-18-2008, 03:44 PM
  2. Averaging Averages For Patient Care
    By Michael Roback in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 04-25-2008, 09:24 PM
  3. Averaging numbers while ignoring blank cells.
    By bjordan in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 12-03-2007, 05:09 PM
  4. Conditional averaging
    By Kullaroo in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 11-11-2007, 09:21 PM
  5. skipping #DIV/0 when averaging range
    By redneck joe in forum Excel General
    Replies: 3
    Last Post: 10-06-2006, 02:41 PM

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