This Excel lambda function implements the native RANK.AVG in a way that can be used inside MAKEARRAY and SCAN

excel-lambda-RANK.AVERAGE.txt

/*
RANK.AVERAGE

Implementation of RANK.AVG that can be used in MAKEARRAY, SCAN etc. 

Inputs and output is the same as RANK.AVG with the exception that if Number is not found in Ref, the cell will output "No rank" instead of an error value.

*/

RANK.AVERAGE =LAMBDA(Number,Ref,[Order],
  LET(
    _order,IF(ISOMITTED(Order),-1,IF(Order=0,-1,1)),
    _n,Number,
    _r,INDEX(IF(ROWS(Ref)=1,TRANSPOSE(Ref),Ref),,1),
    _d,SORT(_r,1,_order),
    _i,SEQUENCE(ROWS(_d)),
    _ranks,SCAN(0,_d,
            LAMBDA(a,b,
              AVERAGE(FILTER(_i,_d=b)),
            )
           ),
    _out,MAP(_n,LAMBDA(x,XLOOKUP(x,_d,_ranks,"No rank"))),
    _out
  )
);