SOLVED

# Calculate median in quartile

Copper Contributor

# Calculate median in quartile

Hi group.

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.

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

# Re: Calculate median in quartile

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.

# Re: Calculate median in quartile

@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)))
)
)
)``````

# Re: Calculate median in quartile

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.

# Re: Calculate median in quartile

Hi Snowman.
Thank you for answering my question and solving my problem.
Regards

# Re: Calculate median in quartile

@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

# Re: Calculate median in quartile

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.