2

I have a list of columns based on date

property | 2023-04-10 | 2023-04-11 | 2023-04-12
A        |    0:10    |    0:20    |   0:30
B        |    0:05    |    0:12    |   0:35
C        |    0:02    |    0:10    |   0:40

I want to compute a column which has average rank, that is

property | average_rank
A        |     (1+1+3)/3
B        |     (2+2+2)/3
C        |     (3+3+1)/3

how do I achieve this in excel, also some of the cells have blank entries, in that case I would not like consider that cell while calculating the average rank

there are 100s of columns so I cannot enter them one by one in the average function

apostofes
  • 2,959
  • 5
  • 16
  • 31
  • isn't something like `=AVG(selectYourCols)` not working? You can mix it in with an `IF` statement to check for empty to skip it. – Xorifelse May 18 '23 at 04:22
  • how do I combine with rank, I use, `=AVERAGE(RANK(ND17,ND$8:ND$500), RANK(NE17,NE$8:NE$500))` but I cannot do this for 100s of columns – apostofes May 18 '23 at 04:24
  • I'm not at all an expert in excel, but I understand sheet formula's and just trying to help and found [`RANK.AVG`](https://support.microsoft.com/en-us/office/rank-avg-function-bd406a6f-eb38-4d73-aa8e-6d1c3c72e83a) function, would that be sufficient? As for 100 cells, you know you can copy with the bottom right corner drag right? – Xorifelse May 18 '23 at 04:54
  • @Xorifelse `RANK.AVG` is intended for something different. Rank calculation has several variants, this is one of them, another one is `RANK.EQ`. In case more than one value has the same rank, returns the average. Per my understanding, the OP wants to find the rank for each element of the input matrix, then calculate per property (row) the average. – David Leal May 18 '23 at 06:23
  • 1
    @DavidLeal Yeah I noticed i got the question or rather, the correlation wrong after the first answer so... my bad was just trying to be helpful polling extra info. Anyways impressive answers! – Xorifelse May 18 '23 at 07:24

2 Answers2

2

Combination of latest functions of Microsoft-365 may work.

=BYROW(
MAP(B2:D4,LAMBDA(x,RANK(x,INDEX(B2:D4,,COLUMN(x)-COLUMN(A2))))),
LAMBDA(rw,AVERAGE(rw))
)

We can avoid using of RANK() function using the following formula.

=BYROW(
MAP(B2:D4,LAMBDA(x,XMATCH(x,SORT(CHOOSECOLS(B2:D4,COLUMN(x)-MIN(COLUMN(B2:D4))+1),,-1)))),
LAMBDA(rw,AVERAGE(rw))
)

enter image description here

Harun24hr
  • 30,391
  • 4
  • 21
  • 36
  • 1
    I think you should replace the reference to cell `A2` with something which references the range `B2:D4` for consistency. – Jos Woolley May 18 '23 at 05:15
  • How does the `XMATCH/SORT` combination compare to `RANK` when there are duplicates within a given column? Do they give the same results? – Jos Woolley May 18 '23 at 06:28
  • Then we may need to use `UNIQUE()` function based on output expected. – Harun24hr May 18 '23 at 06:30
2

You can try the following, assuming no Excel version constraints as per the tag listed in the question:

=LET(in,B2:D4,BYROW(MAKEARRAY(ROWS(in),COLUMNS(in), LAMBDA(i,j,
 RANK(INDEX(in,i,j),INDEX(B2:D4,,j)))),LAMBDA(z, AVERAGE(z))))

Note

Under Excel for Web, I tested you can use INDEX(in,,j) instead of INDEX(B2:D4,,j). As @JosWoolley pointed out in the comment section, testing the formula using the name ìn produces a #CALC! (Nested array error) for desktop under O365. I tested also for this desktop version: Microsoft 365 Apps for Enterprise Version 2304 (Build 16327.20214 Current Channel). A possible bug or inconsistency in Microsoft update/release channel policy. I posted this issue in the Microsoft Feedback community, here is the link, in case you would like to vote for it.

Another workaround to avoid this error and keep using the name in, is to create a LAMBDA(B2:D4) function, i.e. "thunking the range" and name it in, then to refer it. It ensures the formula is easier to maintain (if you need to update the range, update it in one place only). For example:

=LET(in, LAMBDA(B2:D4), BYROW(MAKEARRAY(ROWS(in()),COLUMNS(in()), LAMBDA(i,j,
 RANK(INDEX(in(), i,j), INDEX(in(),,j)))),LAMBDA(z, AVERAGE(z))))

Notice how we call it: in().

Here is the output: output

MAKEARRAY iterates over all elements of the input (in) and calculate the RANK for a given element (i,j) with respect to column j of in. Then we invoke BYROW on the MAKEARRAY result to calculate on each row the average.

David Leal
  • 6,373
  • 4
  • 29
  • 56
  • 1
    Wow. Guess you've recently had a new and very important update to your version of Excel. That returns a `#CALC!` error for my version, since `RANK` requires a *range* for its second parameter, though in this particular construction `INDEX(in,,j)` returns an *array* for me. Has this update been documented anywhere, do you know? Can't wait to get it myself - game-changer! – Jos Woolley May 18 '23 at 05:13
  • Or perhaps you've mistakenly also stored *in* within Name Manager? – Jos Woolley May 18 '23 at 05:14
  • @JosWoolley, no, I am just using the formula I put in the answer. i am using Excel for Web the free version. – David Leal May 18 '23 at 05:15
  • Can you confirm that it **doesn't** work as desired in the desktop version? This is a huge difference between the two if so. – Jos Woolley May 18 '23 at 05:16
  • 1
    Since you start by saying "*assuming no Excel version constraint as per the tag listed in your question*", I think you should amend to say that this is for Excel for Web only. – Jos Woolley May 18 '23 at 05:31
  • 1
    This drawback means that, if we want to declare the original range via `LET`, we have to come up with more involved constructions, e.g. `=LET(ζ,B2:D4,ξ,ROWS(ζ),BYROW(MAKEARRAY(ξ,COLUMNS(ζ),LAMBDA(α,β,MMULT(SEQUENCE(,ξ,,0),N(INDEX(ζ,,β)>=INDEX(ζ,α,β))))),LAMBDA(κ,AVERAGE(κ))))` – Jos Woolley May 18 '23 at 05:32
  • 1
    Under my desktop version, yes I got a `#CALC!` error (nested array), that is weird, since as per [RANK](https://support.microsoft.com/en-us/office/rank-function-6a2fc49d-1831-4a03-9d8c-c279cf99f723) documentation it accepts for **Ref** input argument an array. Interesting, maybe a possible bug, or as you mentioned the Excel Web free version has been recently updated. I tried with `CHOOSECOLS` instead and it gives an Excel error in the formula – David Leal May 18 '23 at 05:34
  • yes, I will update it @JosWoolley I didn't know that is not working under O365 for desktop version being the same version. That is the first time I see this. – David Leal May 18 '23 at 05:35
  • @JosWoolley it works using the range in the `INDEX` call, so I updated the formula. We have seen that before, but this time seems to be something different, I remember we created the name using an inner `LET` as a workaround in another question we debated this, but this doesn't work this time. – David Leal May 18 '23 at 05:52
  • I use 2016 excel – apostofes May 18 '23 at 05:53
  • @JosWoolley this was the question I was referring too: [Using name variable from LET produces #VALUE! inside MAP using SUM with the range defined as INDEX : INDEX](https://stackoverflow.com/questions/74595902/using-name-variable-from-let-produces-value-inside-map-using-sum-with-the-rang) that you responded. Thanks! – David Leal May 18 '23 at 05:55
  • @apostofes next time, use the appropriate tag in your question, I don't consider myself too familiar with the older Excel versions. Maybe other people from the community can help you with a solution to your specific version. Probably a very verbose approach, since it requires to iterate over the entire matrix. – David Leal May 18 '23 at 05:58
  • 1
    @DavidLeal Thanks for the reminder! The only slight issue with the set-up I propose in that link is that you still have to explicitly reference the range for `MAKEARRAY`'s *rows* and *columns* parameter, or else declare the range twice using `LET` (once internal to the `LAMBDA`, once external), which is not ideal. Hence my preference for a set-up which does not involve `RANK`. – Jos Woolley May 18 '23 at 06:33
  • 1
    @DavidLeal Re `RANK` accepting arrays as well as ranges, we've also discussed this before - appears to be an error in the official documentation. – Jos Woolley May 18 '23 at 06:35
  • 1
    Wow! `LAMBDA(B2:D4)`. What's this trickery?! – Jos Woolley May 18 '23 at 06:39
  • 1
    @JosWoolley sometimes Excel users have a lot of imagination to overcome some of the Excel limitations, odd behaviors or lack of documentation, :-). I took the idea from: Sergei Baklan, from this post: [MAXIFS doesn't work inside MAP using names from LET](https://techcommunity.microsoft.com/t5/excel/maxifs-doesn-t-work-inside-map-using-names-from-let/m-p/3681425#M171470) from Microsoft Technical Community. I don't really know the logic behind, it seems to return the range, not an array, so it works – David Leal May 18 '23 at 13:00