Search code examples
google-sheetsgoogle-sheets-formula

Google Sheets. How to make the RANK.EQ function compares the second parameter if the main parameters are equal?

I have a table where the teams' places are determined depending on points. The lower point value gives you a better place.

Team 1: 18 points (the worst) it gives 7 place

Team 4: 5 points (the best) it gives 1 place

I use RANK.EQ function to determine the correct place. And it works well if all teams have different points.

But if two or more teams have equal points, we must consider the second parameter. The team that has a higher second parameter must have a higher place.

I attached a screenshot. Places are not correct there. The correct ones are in column "Must be".

enter image description here

Solution

  • In cases like this, a couple of things are commonly suggested:

    1. use a helper column,
    2. combine the two criterion values and
    3. apply a different weighting to each criterion.

    Since in this case the first criteria is based on the Lowest score and the second criteria is based on the highest score, the helper column (say Column E) would subtract the values rather than add them

    • Helper Column - Cell E2: =A2*100-A3 and copy every 6th row.

    • Ranking Column - Cell F2: =RANK.EQ(E2,$E$2:$E$32,true) and copy every 6th row

    RESULTS

    results