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:
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:
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):
// Row in the pointTable matching scoring case:
// Column in the pointTable matching rank:
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.
Note you can also use an array literal to represent pointTable to get rid of the auxiliary reference:
"1-1-1-4",2, 2, 2, 0;
"1-2-2-2",3, 1, 1, 1;
"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.