Cheltenham spreadsheet
Context
The purpose of the below spreadsheet is to calculate the amount of points gamblers are making over cheltenham.
Each participant picks one horse a day for every race.
- If there horse finishes 1st they get 5 points
- 2nd 3 points
- 3rd 1 point
Spreadsheet
| Name | Race 1 | Race 2 | Race 3 | Race 4 | Race 5 | Race 6 | Race 7 | Points | Race 1 results | Race 2 results | Race 3 results | Race 4 results | Race 5 results | Race 6 results | Race 7 results | Race 8 results | |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| Daniel | 1 | 5 | 17 | 24 | 3 | 2 | 8 | 1800 | 1st place | 2 | 1 | 3 | 1 | 2 | 12 | 2 | 7 |
| Bob | 2 | 23 | 5 | 2 | 24 | 4 | 1 | 2nd place | 3 | 5 | 5 | 2 | 24 | 3 | 3 | 6 | |
| Mark | 6 | 5 | 7 | 5 | 1 | 7 | 3 | 3rd place | 1 | 4 | 12 | 3 | 1 | 1 | 4 | 5 | |
| Luke | 8 | 4 | 9 | 3 | 6 | 0 | 5 | ||||||||||
| etc |
Problem
I am trying to match the players guesses to the results inputted on the right so that I will get a total column calculating the points.
Attempt
This is what I was trying but am not getting correct results as highlighted by first Points cell.
=IF(B2=K2,I2 5,If(B2=K3,I2 3,If(B2=K4,I2 1)))
Microsoft excel
Is there an easier way of doing this in excel?
CodePudding user response：
For each player and each course, try
=choose(iferror(MATCH(B2,K$2:K$4,0),0) 1,0,5,3,1)
then sum each row
