+ Reply to Thread
Results 1 to 4 of 4

Trimmean function with multiple if criteria

  1. #1
    Registered User
    Join Date
    03-09-2016
    Location
    Spokane WA
    MS-Off Ver
    Office 2013
    Posts
    5

    Trimmean function with multiple if criteria

    I am trying to run a trimmean function on a set of data, and it needs to be based on multiple input items,
    The raw data has an average range, and 3 columns for criteria matching (Month, and two more group fields Match1 and Match2).
    I am unable to get this to work, even using an array formula and CTRL SHIFT ENTER.
    I would like to use trimmean to remove 10% of the outlying record set.
    ANy advice?
    I attached a sample sheet.

    The formula i have now is =TRIMMEAN(IF(team=$A2,range,IF(month=B$1,range)),10%)

    Summary sheet:
    Team Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec
    A 1.643406593
    B 1.643406593
    C 1.643406593
    D
    E
    F
    G

    Detail:
    Range Month Match Match2
    0.20 Jan A 1
    0.83 May B 2
    1.17 Mar C 3
    0.45 Mar D 1
    0.92 Mar E 2
    0.02 Sep F 3
    0.47 Jan G 1
    0.33 Mar A 2
    0.40 Mar B 3
    0.62 Mar C 1
    3.57 Apr D 2
    0.42 Mar E 3
    1.15 Mar F 1
    Attached Files Attached Files

  2. #2
    Registered User
    Join Date
    03-09-2016
    Location
    Spokane WA
    MS-Off Ver
    Office 2013
    Posts
    5

    Re: Trimmean function with multiple if criteria

    Even trying one criteria this doesnt seem to work right.
    What am i missing?

    =TRIMMEAN(IF(team=$A2,IF(ISNUMBER(range),range)),0.1)

  3. #3
    Valued Forum Contributor tony h's Avatar
    Join Date
    03-14-2005
    Location
    England: London and Lincolnshire
    Posts
    1,187

    Re: Trimmean function with multiple if criteria

    The if statement does not return a subset of range

    You can do it with a user function in VBA to collect the required cells and return them as a rang for input to the trimmean but I don't know a way to do that just with worksheet functions.

    If you just want to see a hardcoded version for Jan then put the following into a VBA module
    Please Login or Register  to view this content.
    and use
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    It would then be a bit more work to pass the criteria to filterby and compile the actual range to return.


    click on the * Add Reputation if this was useful or entertaining.

  4. #4
    Registered User
    Join Date
    03-09-2016
    Location
    Spokane WA
    MS-Off Ver
    Office 2013
    Posts
    5

    Re: Trimmean function with multiple if criteria

    This was solved with a single formula. VB is an awfully complex solution to something so simple.

    {=TRIMMEAN(IF((location=$A2)*(Month=B$1)*(tier="Tier A"),value),0.1)}

+ 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. Feeding a VBA array into a TRIMMEAN function for a UDF
    By Loganeb in forum Excel Programming / VBA / Macros
    Replies: 15
    Last Post: 01-19-2016, 05:36 PM
  2. [SOLVED] IF FUNCTION: Multiple Criteria, Multiple End Results
    By chantelle0106 in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 09-07-2013, 04:19 AM
  3. Multiple Criteria Function along with Count function
    By Joseph Wee in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 07-23-2013, 12:51 AM
  4. Need help to change multiple criteria from if function to Case function.
    By indkitty in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 05-29-2012, 10:42 PM
  5. Replies: 2
    Last Post: 04-05-2012, 06:21 PM
  6. Replies: 5
    Last Post: 03-23-2012, 11:59 AM
  7. TRIMMEAN with different percentiles?
    By [email protected] in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 04-14-2006, 01:15 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