Hi,
I am organising a football predictions spreadsheet where the users need to predict the top 6 and bottom 3 teams in the premier league. I am attempting to do the scoring using some sort of IF statement/Lookup formula looking at a worksheet that has the actual league standings and then returning points to the entry worksheet. So on both sheets I have the top 6 and bottom 3 teams listed in column with the scores to the right of each cell.
The problem is in the points scoring as say they predict Man City to finish 1st in the league and they do finish 1st, they score 3 points (ie correct position is 3 points). If Man City finish 2nd, 3rd, 4th, 5th or 6th, that person will score 1 point (ie, in the correct half of the league, but not spot on). However, if Man City finish 18th, 19th or 20th, they score -5 points. Likewise is they predict Burnley to finish 20th, and they do finish 20th, 3 points awarded, if they finish 18th or 19th, 1 points awarded (ie in right section of league, but not spot on). But if Burnley manage a top 6 position, they get -5 points.
So I want all the entries in my competition on separate worksheets and a single worksheet that these all look at which I can use to enter the actual league positions! Is there an formula I can use to score this way? I realise I might have to modify it for the top half and bottom halves.
Thanks in advance for any help.
I am organising a football predictions spreadsheet where the users need to predict the top 6 and bottom 3 teams in the premier league. I am attempting to do the scoring using some sort of IF statement/Lookup formula looking at a worksheet that has the actual league standings and then returning points to the entry worksheet. So on both sheets I have the top 6 and bottom 3 teams listed in column with the scores to the right of each cell.
The problem is in the points scoring as say they predict Man City to finish 1st in the league and they do finish 1st, they score 3 points (ie correct position is 3 points). If Man City finish 2nd, 3rd, 4th, 5th or 6th, that person will score 1 point (ie, in the correct half of the league, but not spot on). However, if Man City finish 18th, 19th or 20th, they score -5 points. Likewise is they predict Burnley to finish 20th, and they do finish 20th, 3 points awarded, if they finish 18th or 19th, 1 points awarded (ie in right section of league, but not spot on). But if Burnley manage a top 6 position, they get -5 points.
So I want all the entries in my competition on separate worksheets and a single worksheet that these all look at which I can use to enter the actual league positions! Is there an formula I can use to score this way? I realise I might have to modify it for the top half and bottom halves.
Thanks in advance for any help.
