How to Rank an array directly or a group of arrays without creating more cell references?-CodePudding

How to RANK an array directly? I would like to avoid creating more intermediate data in cells just to reference them.

Excel RANK.AVG formula states it accepts both array and reference:

Syntax
RANK.AVG(number,ref,[order])
The RANK.AVG function syntax has the following arguments:
Number  Required. The number whose rank you want to find.
Ref     Required. **An array of, or a reference to**, a list of numbers. Nonnumeric values in Ref are ignored.
Order   Optional. A number specifying how to rank number.

But Excel keeps rejecting the below formula.

=RANK.AVG(5, {3,1,7,10,5})

If the numbers are put in cells, say B1:B5, Excel accepts

=RANK.AVG(5, B1:B5}

Ultimately, I would like to rank a dynamic array

=RANK.AVG(value, TOCOL(VSTACK(array1, array2))

e.g. =RANK.AVG(5, TOCOL(VSTACK(B1:B5,C1:C10))

CodePudding user response：

It seems that the official documentation on the various RANK functions is simply wrong with respect to the fact that they permit arrays for the ref argument (see here, for example).

You will have to come up with creative alternatives which mimic the RANK.AVG function, for example:

=LET(ζ,SORT(MyArray,,-1),AVERAGE(FILTER(SEQUENCE(COUNT(ζ)),ζ=MyValue)))