View original (French)

Conditional Quartile Function

HEMIYELY Posted messages 71 Status Member -  
eriiic Posted messages 24581 Registration date   Status Contributor Last intervention   -
Hello,
I would like to do a QUARTILE based on the values in another column.
Let me explain.
In my database, I have data in column C for which I want to calculate the quartile.
However, in my BQ column, I have 4 conditions (coded as 1/2/3/4). Therefore, I want to calculate the quartile for the values in C when I have 1 in BQ, and then the quartile when I have 2 in BQ, and so on...
Is there a formula like: IF.QUARTILE?

Configuration: Windows / Chrome 87.0.4280.141

2 answers

  1.
    ALS35 Posted messages 1034 Registration date   Status Member Last intervention   147
     
    Hello,

    Please specify your need.
    Otherwise, perhaps simply this
    =QUARTILE(Your data range in C;Your cell BQ)
    e.g. =QUARTILE(C1:C100;BQ1)

    Best regards
    0
    1. HEMIYELY Posted messages 71 Status Member
       
      Hello,
      Basically on the same principle as AVERAGEIF(Range;Criteria;Average range), I would like to replace the average with the Quartile.
      Best regards
      0
      1. ALS35 Posted messages 1034 Registration date   Status Member Last intervention   147 > HEMIYELY Posted messages 71 Status Member
         
        Hello,

        With a matrix function

        =QUARTILE(IF(criteria range=Criteria;Data range;"");N0 quartile)
        Notice, array formula to be validated by Ctrl+Shift+Enter, it will then automatically be enclosed in curly braces {}

        Best regards
        0
      2. eriiic Posted messages 24581 Registration date   Status Contributor Last intervention   7 281 > ALS35 Posted messages 1034 Registration date   Status Member Last intervention  
         
        Oh yes, with "", I hadn't thought of that...
        0
  2. eriiic Posted messages 24581 Registration date   Status Contributor Last intervention   7 281
     
    Hello,

    not very clear.
    Do you want to create the 4 quartiles for the values that only have 1, then 2, etc.?
    If so, I think you have to sort on BQ in order to work with a dynamic range.
    https://www.cjoint.com/c/KAlibwdNhOX
    eric

    --
    By continuously trying, we eventually succeed.
    So the more it fails, the more chances we have that it works. (the Shadoks)
    In addition to thank you (yes, it happens!!!), remember to mark as resolved. Thanks
    0