+ Reply to Thread
Results 1 to 4 of 4

Quite Complex Average based on multiple conditions

  1. #1
    Registered User
    Join Date
    06-21-2013
    Location
    London, England=
    MS-Off Ver
    Excel 2013
    Posts
    2

    Quite Complex Average based on multiple conditions

    Hi,

    I'm really struggling with this and i hope someone can help.

    I have a set of data, and I want to find the average of the values in the series, but I only want the average of the values that are in the bottom quartile of the data AND the values in the adjacent collum must be in the Top Quartile of that Collum - I also want to limit this to an additional variable which is the department the values are in... So In the attached spreadsheet in F1 I need a formula that will look from A4:A23, see that A matches the value in E1... And the see of the values adjacent to an A (1, 4, 5,6,10,11,13,14,15,18,19,20), See that 1,4,5 are in the bottom quartile, and see that the values adjacent are 100,1,99... See that 1 isn't in the upper quartile of the series C4:C23, so eliminate 4, and I have 1 and 5 left - so the average is 6/2 = 3. And in G1 I want the average of the other two numbers (100 and 99 - so 99.5)

    I hope this makes sense and someone is feeling charitable enough to help - I'm not an excel novice - but I really struggled to get my head round this - I think I'll need to Nest a Percentiles Function in a SUMIFS function - but I'm not sure how for this?

    Thanks for reading, Ryan
    Attached Files Attached Files

  2. #2
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Office 365
    Posts
    14,987

    Re: Quite Complex Average based on multiple conditions

    Since I'm not familiar with this kind of data, show the expected results in your sheet.

    Also add how you found them manualy.

    Probably the solution will take an helpcolumn to define wether the data needs to in a certain range.
    Notice my main language is not English.

    I appreciate it, if you reply on my solution.

    If you are satisfied with the solution, please mark the question solved.

    You can add reputation by clicking on the star * add reputation.

  3. #3
    Forum Expert daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,675

    Re: Quite Complex Average based on multiple conditions

    You can use AVERAGEIFS but it needs to be entered as an "array formula" because the QUARTILE part is an array, i.e. this formula for F1

    =AVERAGEIFS(B4:B23,A4:A23,E4,B4:B23,"<="&QUARTILE(IF(A4:A23=E4,B4:B23),1),C4:C23,">="&QUARTILE(IF(A4:A23=E4,C4:C23),3))

    confirmed with CTRL+SHIFT+ENTER

    Same for G1 except the red part (range to average) becomes C4:C23
    Audere est facere

  4. #4
    Registered User
    Join Date
    06-21-2013
    Location
    London, England=
    MS-Off Ver
    Excel 2013
    Posts
    2

    Re: Quite Complex Average based on multiple conditions

    Thank you very much daddylonglegs - that works absolutely perfectly! Saved me loads of time - and gets me exactly what I need!

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Tags for this Thread

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