+ Reply to Thread
Results 1 to 7 of 7

Formula for averaging just the pos or neg and ommitting the outlier

  1. 09-13-2013, 05:39 PM #1
    Brennen81
    Brennen81 is offline
    Registered User
    Join Date
    01-08-2013
    Location
    Toronto
    MS-Off Ver
    Excel 2013
    Posts
    79

    Formula for averaging just the pos or neg and ommitting the outlier

    Hello,

    Is there some sort of function, or combination of, to do the following. I would have a data set with both positive and negative numbers. I would like to average only the positive, and also have it automatically omit the largest number(s). And vice-versa.

    I know there is TRIMMEAN for the entire data set, which omits both lowest and highest. But for this I would only want to take the positive or negative and omit the same.


    Hope I explained properly.
    Thanks!
    Reply With QuoteRegister To Reply
  2. 09-13-2013, 05:56 PM #2
    martindwilson
    martindwilson is offline
    Forum Expert martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: Formula for averaging just the pos or neg and ommitting the outlier

    =(SUMIF(A1:A8,"<0")-MIN(A1:A8))/(COUNTIF(A1:A8,"<0")-1) would omit the smallest
    and
    =(SUMIF(A1:A8,">0")-MAX(A1:A8))/(COUNTIF(A1:A8,">0")-1)
    the largest
    =(SUMIF(A1:A8,"<0")-COUNTIF(A1:A8,MIN(A1:A8))*MIN(A1:A8))/(COUNTIF(A1:A8,"<0")-COUNTIF(A1:A8,MIN(A1:A8))) would get rid of joint smallest

    =(SUMIF(A1:A8,">0")-COUNTIF(A1:A8,max(A1:A8))*max(A1:A8))/(COUNTIF(A1:A8,">0")-COUNTIF(A1:A8,max(A1:A8)))
    would get rid of joint largest
    Last edited by martindwilson; 09-13-2013 at 06:04 PM.
    "Unless otherwise stated all my comments are directed at OP"

    Mojito connoisseur and now happily retired
    where does code go ?
    look here
    how to insert code

    how to enter array formula
    why use -- in sumproduct
    recommended reading
    wiki Mojito

    how to say no convincingly

    most important thing you need
    Martin Wilson: SPV
    and RSMBC
    Reply With QuoteRegister To Reply
  3. 09-13-2013, 06:17 PM #3
    Brennen81
    Brennen81 is offline
    Registered User
    Join Date
    01-08-2013
    Location
    Toronto
    MS-Off Ver
    Excel 2013
    Posts
    79

    Re: Formula for averaging just the pos or neg and ommitting the outlier

    Simply incredible. I VERY much appreciate your response.

    Could I ask what "Joint Largest/smallest" means?
    Reply With QuoteRegister To Reply
  4. 09-13-2013, 06:22 PM #4
    martindwilson
    martindwilson is offline
    Forum Expert martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: Formula for averaging just the pos or neg and ommitting the outlier

    well if you had 10,10,9,8,7 it would average 9,8,7 and ignore both 10's
    Reply With QuoteRegister To Reply
  5. 09-13-2013, 06:33 PM #5
    Tony Valko
    Tony Valko is offline
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Formula for averaging just the pos or neg and ommitting the outlier

    Here's another way.

    Data Range
    A
    B
    C
    1
    Value
    ------
    Average
    2
    -6
    4.333333
    3
    4
    -4
    4
    -8
    5
    -5
    6
    -3
    7
    3
    8
    6
    9
    -2
    10
    10

    Enter this array formula** in C2 for the avg of positives excluding the max positive(s).

    =AVERAGE(IF(A2:A10>=0,IF(A2:A10<MAX(A2:A10),A2:A10)))

    Enter this array formula** in C3 for the avg of negatives excluding the min negative(s).

    =AVERAGE(IF(A2:A10<0,IF(A2:A10>MIN(A2:A10),A2:A10)))

    ** array formulas need to be entered using the key
    combination of CTRL,SHIFT,ENTER (not just ENTER).
    Hold down both the CTRL key and the SHIFT key
    then hit ENTER.
    Biff
    Microsoft MVP Excel
    Keep It Simple Stupid

    Let's Go Pens. We Want The Cup.
    Reply With QuoteRegister To Reply
  6. 09-13-2013, 06:45 PM #6
    Brennen81
    Brennen81 is offline
    Registered User
    Join Date
    01-08-2013
    Location
    Toronto
    MS-Off Ver
    Excel 2013
    Posts
    79

    Re: Formula for averaging just the pos or neg and ommitting the outlier

    Right!!!

    Ok, one last question I think. How could I omit the top 2 or 3 or 4 etc of the set, rather than just the largest?
    Reply With QuoteRegister To Reply
  7. 09-13-2013, 11:21 PM #7
    Teethless mama
    Teethless mama is offline
    Banned User!
    Join Date
    10-14-2006
    Posts
    1,211

    Re: Formula for averaging just the pos or neg and ommitting the outlier

    =AVERAGEIF(A$2:A$10,"<"&LARGE(A$2:A$10,n))

    where n: number of desired
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Need a formula for detecting a single outlier with Grubbs test
    By Flyers in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 05-15-2013, 07:02 PM
  2. Conditional format outlier 2 standard dev
    By Cicada in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 07-27-2011, 06:11 PM
  3. Ommitting some values from randbetween function
    By Geomarsh in forum Excel General
    Replies: 6
    Last Post: 07-27-2009, 07:38 AM
  4. ommitting empty cell from chart
    By douglukas in forum Excel Charting & Pivots
    Replies: 4
    Last Post: 02-05-2008, 05:41 PM
  5. How do I test outlier in Excel?
    By Koos jubileert in forum Excel General
    Replies: 1
    Last Post: 08-30-2005, 10:05 AM

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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1