Award values after comparing two rows, avoiding double counting

[joachimh]

Hi,

Normally when I am struggling in Excel, I manage to find a solution on the web, but now I am for once actually stuck and would really appreciate any help I could get:

I'm hosting a competition where the participants will get points based on how well they guess on the final results (gold, silver, bronze) on a set number of sporting events. The sports have individual participants, so a nation can have many athletes competing in each event and therefore a nation can win both gold, silver and bronze in the same event.

You get 5 points per correct nation per correct medal, so for instance if you guessed that the U.S. would win gold and they do, then you get 5 points. For a correct top 3 nation, but in the incorrect place (i.e. the nation you guessed would win gold, get silver instead), you are awarded 1 point. However, if you have selected one nation to get two top three placements and that nation only get one top three placement you only receive points once.

I need a formula that correctly calculates the points (avoiding double counting). A double counting problem typically arises when I have a contestant that has selected the same nation to win all medals and that nation wins for instance only the gold medal. Then the contestant should get 5 points for the correct gold winner and nothing more. However, I can't find a match/lookup function that awards the 5 points without also awarding 1 point for having the silver nation top 3 and an additional point for having the bronze nation top 3.

A few examples:
Actual_Gold >< Guessed_Gold & Guessed_Gold = Actual_Silver, 1 point awarded
Actual_Gold = Guessed_Gold, 5 points awarded
Actual_Gold = Guessed_Gold & Actual_Silver = Guessed_Gold & Actual_Silver <> Guessed_Silver, 5 points awarded for correct gold, as there is no double counting 0 points are awarded for having Actual_Gold = Guessed_Silver.

I have attached a spreadsheet with a few possible outcomes and have included comments to each of them.

Thanks in advance.

[joachimh]

Found a (semi-optimal) solution myself.