Excel does not have a function to sort for multiple criteria for MEDIAN() as it does with AVERAGEIFS() or SUMIFS().
I have been sorting multiple criteria for median with nested IF() statements. For example, suppose I have a rent roll and I'm looking to calculate the median rent of a particular unit type that's occupied, I would do:
=MEDIAN(IF(unitType="1x1",IF(leaseStatus="Occd",rent)))
Where unitType, leaseStatus, and rent are arrays of data.
Is it possible to rewrite this function using IFS()? I have not been successful. I tried for example:
MEDIAN(IFS(unitType="1x1",leaseStatus="Occd",rent))
with no success.
Thanks in advance.
This post does not discuss how:
High level conclusions See below for further info RE: M1-4 (corres. Method 1-4 resp.)
M1. Nested if (M1, given) is natural starting pt. yet rendered unnecessary by latest software version (i.e. Office 365 compatible Excel); thus room for improvement/refinement remains
M2. Prosaic 'single-if' method appears to be an improvement over M1 given elimination of nested if method (however, this depends upon one's defn. of optimality to begin with).
M3. Parsimonious: filter - per below: recommended method provided Office 365 Excel version is avail. (failing which, M2)
M4. Ifs (M4) possible yet inappropriate/superfluous given nature of
filter in Q
---------------------------------------------------------------------
(2) VARIANTS & REL. MERITS
IFs statement akin to If/else if/else if etc. compound statement; you're looking for a compound If statement as opposed to a series of sequential filters that suffer from the unnecessary ordered dependency for the filtration in question.
Here is a comparison table highlighting relative merits/otherwise for 4 different methods/calc. bases (incl. the variant given in the body of the Q [M1] and the IFs variant / equivalent [M4]:
functions corres. to respective cells/comparison table (G5-G7):
M1: cell G5
(see Q for gen. basis of formula/compound-nested If function)
M2: cell G6
M3: cell G7
M4: cell G8
- Relies upon double ifs per Q.
- fn: see v. first function 'v. short A' above
---------------------------------------------------------------------
(3) COMPARISON OF M1-4
See table in first screenshot provided above