1

Recently I answered a question about how to retrieve the MEDIAN() of each MEDIAN() in a 2-column matrix without helpers, e.g:

enter image description here

The row-wise calculation without helpers wasn't too hard because the median with only two values is always the average. Therefor a simple formula was all it took:

=MEDIAN((A1:A3+B1:B3)/2)

But for curiosity sakes however, wat if I would have at least a 3-column matrix?

enter image description here

The median will actually need to be calculated. Here the medians are {8,2,2}.

I can't seem to find a way to to get a row-wise calculation for 3+columns. In this case it's about MEDIAN() but I can imagine there could be other functionalities. Since this could be simplified data I don't want to resort to something like =MEDIAN(MEDIAN(A1:C1),MEDIAN(......

I tried to fiddle around with OFFSET(), though not a fan of volatile functions I was hoping it would either work directly with an array, or would be triggered correctly through using MEDIAN(LET(X,SEQUENCE(ROWS(A1:A3)),MEDIAN(OFFSET(A1:C1,X-1,0)))). I then moved on to combinations of either MMULT() or LARGE(), however none of my attempt were succesfull.

Question

So the question ultimately is; how do we return the result (array) of an row-wise calculation without helpers. And if not possible, that's also a perfectly fine answer so I can rest my head =)

JvdV
  • 70,606
  • 8
  • 39
  • 70
  • The problem is that Median accepts arrays as input, so the first Median, even if you were to get the offset correct would capture all the array and find the median of that and pass only one number to the outer median. – Scott Craner Dec 16 '20 at 14:53
  • SUM, AVERAGE, MIN, MAX do the same thing. – Scott Craner Dec 16 '20 at 14:53
  • @ScottCraner, exactly my problem. Though `SUM()` and `AVERAGE()` we can deal with through `MMULT()`, I was hoping there is a more generalized way of doing a per-row calculation. A "no that's not possibble" is perfectly acceptable too. – JvdV Dec 16 '20 at 14:53
  • Which means I do not think it possible. With the others we can use the *IFS() formulas and OFFSET to do what you want, but there is no MEDIANIFS() – Scott Craner Dec 16 '20 at 14:55
  • @ScottCraner, maybe a recursive `LAMBDA()` can get us anywhere? However, I do not yet have got that function. – JvdV Dec 16 '20 at 14:58
  • maybe try offset in a LARGE that returns the middle number then take the median of that? – Scott Craner Dec 16 '20 at 15:02
  • 1
    I have tried for the past 90 minutes, I do not think it possible. – Scott Craner Dec 16 '20 at 16:15
  • @ScottCraner, if also you have given up I'll also throw in the towel. That is atleast untill `LAMBDA()` comes around. Who know.... it may still be possible =) – JvdV Dec 16 '20 at 16:18
  • You could use LET in this fashion: `=LET(x,MEDIAN(A1:C1),y,MEDIAN(A2:C2),z,MEDIAN(A3:C3),MEDIAN(x,y,z))` But that is not very scalable, and my reading on Lamda is that it would need to be used in a similar fashion. But we will see. – Scott Craner Dec 16 '20 at 16:30
  • @ScottCraner.....my head hurts but.....I think I cracked it =) – JvdV Dec 18 '20 at 22:12

1 Answers1

1

New Answer

With the new BYROW() function one could use:

=MEDIAN(BYROW(A1:C3,LAMBDA(a,MEDIAN(a))))

The nested LAMBDA() in the 2nd parameter makes it a piece o' cake to loop all rows in a dynamic array (not a range per se).

Previous Answer (Pre-BYROW())

So. After a long thought, as far as my understanding goes this is not possible through current formulae. However, currently in BETA, Excel365 will feature the new LAMBDA() function which makes it possible to create your own function without VBA and even recursively call itself. It isn't the prettiest of solutions but I thought I would share what I did here:

enter image description here

Formula in E3:

=MED(A1:C3,"",ROWS(A1:A3))

Where MED() is our own LAMBDA() function created at the "name manager" menu. It reads:

 =LAMBDA(rng,txt,rws,IF(rws=0,MEDIAN(FILTERXML("<t><s>"&txt&"</s></t>","//s")),MED(rng,TEXTJOIN("</s><s>",,txt,MEDIAN(INDEX(rng,rws;0))),rws-1)))

As can be seen there are 4 main parameters of which 3 variables:

  • rng - The range to be examined.
  • txt - A reserved variable to be used in FILTERXML().
  • rws - A counter.

The 4th parameter is a nested IF() which if the counter is as 0 will return the median of all medians. This is done through FILTERXML() which I will not get into detail right now.

If the counter is not yet at 0 it will recursively call the LAMBDA() function untill it is, and what it does is using the same three parameters but we can alter them right there and then. Therefor we leave rng intact, we concatenate the MEDIAN() of the row (current counter) through TEXTJOIN() to create a valid xml construct. And last but not least we need to lower the counter.

It's a struggle, but with LAMBDA() it will now be possible to do a rowwise calculation.

Note, if you are interested in the FILTERXML() construct, you might like this post where I now also included a LAMBDA() version of a SPLIT() function.

JvdV
  • 70,606
  • 8
  • 39
  • 70
  • 1
    I do not have lambda yet. but fascinating that you can do recursive. That is awesome. I will need to play with it when I finally get it. – Scott Craner Dec 18 '20 at 22:16
  • @ScottCraner. Have a look at your account, I didn't have it untill today but then I realised I wasn't an insider yet. It's a simple button with a switch to say you want the BETA environment. It opens many opportunities. I dived right in to create a recursive substitution of characters instead of nested `SUBSTITUTES()`. Very fun! – JvdV Dec 18 '20 at 22:17
  • I was not on beta chanel. – Scott Craner Dec 18 '20 at 22:21
  • 1
    There you go. Time to switch the channel and get an update =) @ScottCraner – JvdV Dec 18 '20 at 22:22