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.

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

Data:

CodePudding user response：

Assuming the same layout as P.b., in I4:

=1 SUMPRODUCT(N(MMULT(CHOOSE({1,2,3},RANK(C$4:C$12,C$4:C$12),RANK(D$4:D$12,D$4:D$12),RANK(E$4:E$12,E$4:E$12)),{1;1;1})<SUM(RANK(C4,C$4:C$12),RANK(D4,D$4:D$12),RANK(E4,E$4:E$12))))

and copied down.

CodePudding user response：

This is quite challenging in older Excel, but possible nonetheless:

=IFERROR(
INDEX(
MMULT(--(RANK($C$4:$C$12,$C$4:$C$12) RANK($D$4:$D$12,$D$4:$D$12) RANK($E$4:$E$12,$E$4:$E$12)>=TRANSPOSE(RANK($C$4:$C$12,$C$4:$C$12) RANK($D$4:$D$12,$D$4:$D$12) RANK($E$4:$E$12,$E$4:$E$12))),ROW($C$4:$C$12)^0),ROW($A1))
-SUMPRODUCT(--(MMULT(--(RANK($C$4:$C$12,$C$4:$C$12) RANK($D$4:$D$12,$D$4:$D$12) RANK($E$4:$E$12,$E$4:$E$12)>=TRANSPOSE(RANK($C$4:$C$12,$C$4:$C$12) RANK($D$4:$D$12,$D$4:$D$12) RANK($E$4:$E$12,$E$4:$E$12))),ROW($C$4:$C$12)^0)
=INDEX(MMULT(--(RANK($C$4:$C$12,$C$4:$C$12) RANK($D$4:$D$12,$D$4:$D$12) RANK($E$4:$E$12,$E$4:$E$12)>=TRANSPOSE(RANK($C$4:$C$12,$C$4:$C$12) RANK($D$4:$D$12,$D$4:$D$12) RANK($E$4:$E$12,$E$4:$E$12))),ROW($C$4:$C$12)^0),
ROW($A1)))) 1
,"")

(requires being entered with ctrl shift enter)

Explanation: First an array is made of the sum of the 3 rankings: RANK($C$4:$C$12,$C$4:$C$12) RANK($D$4:$D$12,$D$4:$D$12) RANK($E$4:$E$12,$E$4:$E$12) This results in your so called Rank Sum - array.

Then - since RANK requires a range, not an array, we need an alternative to create a ranking of the array: MMULT can do that.

MMULT(RankSum>=RankSum,ROW(RankSum)^0) creates an array of the ranked RankSum, however. If 2 are ranked equally - for instance rank 1 - it's rank both as 2, not 1. Therefore I used SUMPRODUCT to calculate the number of items in the calculated MMULT-array that equal the indexed MMULT-array result as an alternative to COUNTIF, which is also limited to take a Range, not Array. So MMULTarray-SUMPRODUCT(--(MMULTarray=IndexedMMULTarray)) is your end result.

Calculation is based on your data being in B4:E12 and formula above is entered (with ctrl shift enter) in a cell in row 4 and copied down; I4 in the shared screenshot.

Even though this formula answers your question, I doubt this is what you thought what it would be. Changing the range to a different range by itself could be very teasing. And calculating the rankings manually and sum/rank them is probably easier to maintain. You may make it more dynamical by adding INDEX in the ranges.