Forum Discussion

j_mig's avatar
j_mig
Copper Contributor
May 24, 2024

Calculate median in quartile

Hi group.

Need your help

I have a excel table. In column a is the employee code, in column b is the sex (m man, w woman) and in column c is the amount of their payment.

I need to calculate the paymen median of quartile 1, median of quartile 2, median of quartile 3 and median of quartile 4 for man and woman separately.

Thanks in advance

  • j_mig 

    You did not specify the table name, and your column names look imprecise.  So I suspect that you do not have an Excel table, which Microsoft uses as a technical term (and that's not to be confused with a pivot table or data table—other Excel constructs).

     

    But in the attached workbook, I have shown solutions both for data in a range and data in an Excel table. The former uses two dynamic named ranges (rngSexes and rngPayments) to make formulas on that worksheet easier to understand; the ranges are, of course, defined in the Name Manager.

  • SnowMan55's avatar
    SnowMan55
    Bronze Contributor

    j_mig 

    You did not specify the table name, and your column names look imprecise.  So I suspect that you do not have an Excel table, which Microsoft uses as a technical term (and that's not to be confused with a pivot table or data table—other Excel constructs).

     

    But in the attached workbook, I have shown solutions both for data in a range and data in an Excel table. The former uses two dynamic named ranges (rngSexes and rngPayments) to make formulas on that worksheet easier to understand; the ranges are, of course, defined in the Name Manager.

    • j_mig's avatar
      j_mig
      Copper Contributor
      Hi Snowman.
      Thank you for answering my question and solving my problem.
      Thank you for your help and your time.
      Regards
  • djclements's avatar
    djclements
    Bronze Contributor

    j_mig As a dynamic array variant (for MS365):

     

    =DROP(REDUCE("", UNIQUE(tblData[Sex]), LAMBDA(a,v, LET(
        arr, SORT(FILTER(tblData[Payment], tblData[Sex]=v)),
        m, MAKEARRAY(1, 4, LAMBDA(r,c, LET(
            pq, QUARTILE.INC(arr, c-1),
            cq, QUARTILE.INC(arr, c),
            MEDIAN(FILTER(arr, IFS(c=1, arr<=cq, c=4, arr>pq, TRUE, (arr>pq)*(arr<=cq))))))),
        VSTACK(a, m)))), 1)

     

    Adjust the table name and/or column names as needed.

     

    Calculate Median by Quartile

     

    EDIT: A couple of tweaks to the above-mentioned formula...

    • Use TOCOL(UNIQUE(tblData[Sex]), 1) to ignore blanks
    • Remove the SORT function (not needed)

    The formula can also be written as a single-cell report, complete with headers and row labels:

     

    =LET(
        arr, TOCOL(UNIQUE(tblData[Sex]), 1),
        HSTACK(
            VSTACK("Median", arr),
            REDUCE("Q"&UNICHAR(SEQUENCE(1, 4, 8321)), arr, LAMBDA(p,v, LET(
                a, FILTER(tblData[Payment], tblData[Sex]=v),
                m, MAKEARRAY(1, 4, LAMBDA(r,c, LET(
                    pq, QUARTILE.INC(a, c-1),
                    cq, QUARTILE.INC(a, c),
                    MEDIAN(FILTER(a, IFS(c=1, a<=cq, c=4, a>pq, TRUE, (a>pq)*(a<=cq))))))),
                VSTACK(p, m)))
            )
        )
    )

     

    Please see the attached workbook...

    • j_mig's avatar
      j_mig
      Copper Contributor
      Hi djclements- Your answer is perfect and solves my problem. I can't give two answers as the best so I haven't been able to mark your answer as the best.
      Thank you for your help and your time.
      • djclements's avatar
        djclements
        Bronze Contributor

        j_mig No worries... it's totally up to you to decide which method you prefer. Cheers!