Creating (Somewhat complex) Scoring Formulas for an Online Game

[fasterpie]

I’m working on an online game where players predict movie box office gross totals for a certain block of time. Here is an example of a players guesses:

For the month of November:
1. The Hunger Games: Catching Fire - $200M
2. Frozen - $150M
3. The Wolf of Wall Street - $100M

I’m trying to figure out if there is a way to create some formulas to score their guesses based on accuracy. Here is what I need:

1. Create a formula that scores player based on the accuracy of the positions guessed. Let’s just say the player would get 1,000 points for guessing a movie’s position exactly, 500 for guessing a movie correctly in the Top 3, and -500 for guessing a film that did not make it into the Top 3. Using the Top 3 above, let’s say these were the actual top 3 films of November:

1. Hunger Games (+1,000 points for guessing a film in the correct spot)
2. Thor: The Dark World (-500 for not guessing this movie correctly)
3. Frozen (+500 points for being in the top 3)

2. Create a formula that scores a player based on the accuracy of the gross earned. The player would get 2,500 points for guessing within $50M of the gross, 1,500 points for guessing within $100M, 0 points for $150M, -1,200 for guess being off by more than $200M, and then, here is the tricky part, -400 points for every $50M they are off after that. Using the same 3 guesses from above, let’s say this was the actual result:

1. Hunger Games - $505M (-2,000 [-1,200 for being off by $200M - 400 for another $50M - 400 for being off by another $50M)
2. Frozen - $175M (2,500 points for being within $50M)
3. The Wolf of Wall Street – $195M (1,500 for being within $100M)

3. Create a formula that counts the number of position guessed correctly. For this, I would need 3 cells from one column to compare themselves to the 3 cells from another column. Again, using the sample above, the result we would get is “2” because the player correctly guessed 2 out of the Top 3 films correctly.

I think that about covers it. Let me know if I need to clarify anything. Thanks in advance!

[fasterpie]

Also, in the example used, the player guessed The Wolf of Wall Street would be part of the Top 3, but it ended up not being part of the Top 3, so I want to see if there is a way to automatically give it a score of 0 for accuracy (based on the gross). So in total, that film would earn a score of -500 for not being in the Top 3 and it would not be awarded points for being close to the final gross because films that are not in the Top 3 are automatically disqualified from those points. I've attached an Excel spreadsheet of what I want the score sheet to look like. Sample Scoring Sheet.xlsx

[fasterpie]

Would this require IF formulas? I don't have much experience using them, so any help would be greatly appreciated.