0

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: Optimizing multiple-criteria IFs

user1527227
  • 2,068
  • 5
  • 25
  • 36
  • Do you have some sample data with expected output. Doesn't have to be much but It's easier to work with than none. – JvdV Nov 12 '21 at 08:10
  • I think you're confusing `IFS` with functions such as `AVERAGEIFS`, `SUMIFS`. You're correct that there is no function `MEDIANIFS`, and to rewrite your current formula so as to use `IFS` (`=MEDIAN(IFNA(IFS((unitType="1x1")*(leaseStatus="Occd"),rent),""))`) would be both convoluted and an inappopriate use of that function. – Jos Woolley Nov 12 '21 at 09:24
  • Thank you @JosWoolley. So what is the best way to implement the multiple criteria? The way I showed with nested IF()? – user1527227 Nov 12 '21 at 15:44
  • Yes, your current formula is optimal. – Jos Woolley Nov 12 '21 at 15:47
  • @JosWoolley Thank you. – user1527227 Nov 12 '21 at 17:08
  • @user1527227 - I have a different view RE: JW's comment concerning 'optimality' - whilst this is a vague term in its own right, I argue there is an even more 'efficient' method to calculate; provide 4 variants (including your 'double-if'); and weight up relative pros/cons in this regard. Kindly consider and vote up/down accordingly for the benefit of future users. Ta – JB-007 Nov 12 '21 at 23:49

1 Answers1

1

Here /screenshot(s) refer.

RE your Q:

"Is it possible to rewrite this function using IFS()? "

(1) (v.) SHORT ANS..

YES (if one must... cf. cell G8):

=MEDIAN(IFS((Table1[Letter]="A")*(Table1[Colour]="blue"),Table1[value],1,""))

M4: attaining result using (unnecessarily) 'IFs' statement

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 M1: Double if - given

(see Q for gen. basis of formula/compound-nested If function)

M2: cell G6

M2: Prosaic 'single-if' method

M3: cell G7

M3: Parsimonious 'filter' method

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

JB-007
  • 2,156
  • 1
  • 6
  • 22
  • Thank you so much. Which method would you use? – user1527227 Nov 14 '21 at 16:26
  • If I had office 365 (which I do) - I tend to prefer something like M3 - BUT, as not a lot of people are exceptionally familiar with this method, should my work be subject to review by others/colleagues etc., it might be better to stick with something like M1/M2. When working on my own projects/personal workbooks etc., I'd try to maintain consistency so that if there is an error with one of the functions/formulae, it's a little easier to trace collateral 'damage' so to speak... :) – JB-007 Nov 18 '21 at 01:57