1

I'm using Google Sheets to keep track of some board game statistics and can't quite figure out how to handle a tiebreaking scenario. My group of friends would like to track a "wins/losses" category that works as follows: For a 4-person game, first place would get 3 pts, second place 2 pts, and 3rd place 1 pt. In the event of ties, players would split the available points equally. For example....

Game score: 21 34 46 30 Wins/losses pts: 0 2 3 1

Game score: 27 10 10 3 Wins/losses pts: 3 1.5 1.5 0

Game score: 25 25 10 3 Wins/losses pts: 2.5 2.5 1 0

Any suggestions on how to achieve this? I couldn't figure it out with Rank().

1

Approach 0 - Custom Functions

Custom function seems like the most appropriate choice for your case since you can code the scoring logic and points distribution directly. This also has the benefit of being extremely easy to modify and maintain should you wish to change the point system in the future. However, I have yet to look into the google app script api myself, so this answer will be purely in google sheet functions.


I'll also assume the following interpretation of your tie-breaker rule:
enter image description here

Approach 1 - Look-Up

Note that the table is exhaustive; this means we can simply do a look-up based on which case we are in. We can use rank() to extract the relevant information enough to distinguish among them:

sort(transpose(ArrayFormula(rank({27,10,10,3},{27,10,10,3}))),1,true)
gives transpose({1,2,2,4})

Then you can use index to get the points reward based on matching ranks. I'll call the above table pointTable (from the cell containing 1-1-1-1 to the bottom-right cell). Then for any given game scores of 4 players, the row we want can be found using the formula above (For the 1-2-2-4 format, we simply wrap the formula in a join() function appropriately). The table also has a nice property that the points given for player on the ith rank is on the ith column even in case of ties, so the column we want is simply given by the rank() function plus one (to account for the case-column on the very left):

=index(
    pointTable,
    // Row in the pointTable matching scoring case:
    match(
        join(
            "-",
            sort(transpose(ArrayFormula(rank($A1:$D1,$A1:$D1))),1,true)
        ),
        index(pointTable,,1)
    ),
    // Column in the pointTable matching rank:
    rank(A1,$A1:$D1)+1
)

You will have to remove the comments in the formula above before using. You may also need to adjust the references accordingly depending on where your data resides.

Both of these approaches also cover non-tied games (the very last row on the pointTable) so you can drag the formula across the whole range regardless of whether or not there are any tied games. They should also be relatively easy to modify per your needs. The actual sheet containing pointTable can later be protected and/or hidden if necessary.


Approach 2 - Calculations

Another approach is to actually calculate the points directly. This would be rather tough and extremely convoluted. Furthermore, your scoring logic would probably be scattered throughout the formula, making it hard to read and maintain so I wouldn't recommend it. Unless, of course, if we manage to get an elegant and human-readable solution.

If you still want to try this approach, particularly since it would require neither custom functions nor auxiliary ranges, one starting point would be getting something like approach 0 first (but perhaps in just a pseudo-code), then try to translate the logic over to google sheet functions.

Edit:

Note you can also use an array literal to represent pointTable to get rid of the auxiliary reference:

{
    "1-1-1-1",1.5,1.5,1.5,1.5;
    "1-1-1-4",2,  2,  2,  0;
    "1-1-3-3",2.5,2.5,0.5,0.5;
    "1-1-3-4",2.5,2.5,1,  0;
    "1-2-2-2",3,  1,  1,  1;
    "1-2-2-4",3,  1.5,1.5,0;
    "1-2-3-3",3,  2,  0.5,0.5;
    "1-2-3-4",3,  2,  1,  0
}

Replacing the two references to pointTable in the formula with this array will allow it to work without having an actual pointTable somewhere in the file, but I don't recommend this unless you know for certain that the rules won't change.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.