Rank equally across multiple columns with different data types using a single formula. Is it possibl-CodePudding

See this data set below. It has 3 columns (PTQ, % Growth, $ Growth) that all need ranked individually then summed up and then ranked again for a total power rank of each region. Is there any way I can do this with a single formula? I do this a lot and it would be nice not to have to rank everything individually each time. Basically I need to end up at the "Power Rank" column.

To clarify, I do not want to rank first on one column then another, they all need to be ranked equally together.

CodePudding user response：

Thought I'd add a solution for non-365 users:

Edit: slight amendment so that the returned ranking mimics that of the RANK function, with thanks to @Scott Craner

=MMULT(N(MMULT(CHOOSE({1,2,3},RANK(A2:A10,A2:A10),RANK(B2:B10,B2:B10),RANK(C2:C10,C2:C10)),{1;1;1})>TRANSPOSE(MMULT(CHOOSE({1,2,3},RANK(A2:A10,A2:A10),RANK(B2:B10,B2:B10),RANK(C2:C10,C2:C10)),{1;1;1}))),ROW(A2:A10)^0) 1

which may require committing with CTRL SHIFT ENTER, depending on the version of Excel being used.

The static

{1;1;1}

relates to the fact that 3 columns are being queried, and could of course be replaced with a dynamic alternative.

CodePudding user response：

We can use Office 365 LET and SORT. RANK does not allow the use of arrays, so we need to improvise with MATCH and SORT.

=LET(
    ptq,A2:A10,
    grwt,B2:B10,
    grwthd,C2:C10,
    ptqrnk,RANK(ptq,ptq),
    gwtprnk,RANK(grwt,grwt),
    gwtdrnk,RANK(grwthd,grwthd),
    sm,ptqrnk gwtprnk gwtdrnk,
    MATCH(sm,SORT(sm),0))