Forum Discussion
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.
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...
Thank you for your help and your time.
j_mig No worries... it's totally up to you to decide which method you prefer. Cheers!
