SOLVED

Calculate median in quartile

Copper Contributor

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

5 Replies
best response confirmed by j_mig (Copper Contributor)
Solution

@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 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 QuartileCalculate 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...

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.
Hi Snowman.
Thank you for answering my question and solving my problem.
Thank you for your help and your time.
Regards

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

1 best response

Accepted Solutions
best response confirmed by j_mig (Copper Contributor)
Solution

@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.

View solution in original post