Search code examples
google-sheetsgoogle-sheets-formula

Sheets formula for sports predictor scores

I'm creating a Google sheet to keep track of a predictor competition for a group of friends.

The competition involves picking the winner for each round of a sporting event. Player will pick one name (from a fixed list of names) for each round of an cycling event. If this cyclist wins that round, players will gain 20 points. However, if the cyclist comes 2nd the player gets 10 points and if the cyclist comes 3rd the player gets 5 points.

I have the formula for when the results are in and there are cyclists in 1st, 2nd and 3rd positions and a player has selected their prediction. However, the formula is awarding the player 20 points when there are no results for a round yet and the player hasn't made a prediction. This is because the player's prediction is an empty cell which matches the value for the cyclist in 1st place - an empty cell.

I have included a screenshot of the spreadsheet and formula.

enter image description here

How can I have the player's score zero (C9:C11) when there are no cyclists in A9:A11?

Solution

  • You may try this in the merged section of C8:C11:

    =ifs(B8="",0,B8=A9,20,B8=A10,10,B8=A11,5,1,0)