#### skully43

##### New Member
Hi, I am trying to put together an Excel spreadsheet for a football compitition I am running at work but quite honestly really do not know where to start. I will try and give you a brief outline of how the compition works Simply predict the final position of every team in the Premiership, plus the top three in the Championship, League One and League Two. Also, predict the winners and runners-up in the FA Cup and League Cup.

Scoring System

You score points based on the difference between your predictions and their actual positions. For example, if you were to predict Reading to end 13th and they end 19th, then you score 6 points. Similarly, if you were to predict West Ham to end 1st and they end 9th, then you score 8 points. In the FA Cup and League Cup, points are scored based on which round your prediction is knocked out of the competition.

what I am after is a spreadsheet that I can enter scores throughout the season and that works out where the people in the compition currently stand

any help very very gratefully recieved

Ash

### Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"
Are you sure ?

On your scoring system, the further away from the final outcome you are, the higher the nuber of points.

i.e If you predicted Chelsea to win the Premiership, and they did, then you would score 0. If you predicted Chelsea to come last then you would score 24 points.

Scoring

That is exactly correct, so the idea is to get the lowest total amount of points at the end of the season. What I am trying to simplfi is the weekly task of updating those in the compition. I send out an email informing them of there current points total if the seaon were to end there and then based on where teams are in the league at that point. Obvously for cup compitions everyone would score 0 points until that compition started

I've put something together that may work for you. It's set up for 15 players, but could be bigger if necessary.

I also assumed that 0 would be the "best" score possible, ie no minus numbers are allowed.

http://www.sendspace.com/file/xnf8yn

All very simplistic, feel free to change as you see fit.

ps an IT Director making Football spreadsheets? I'm working for the wrong company!

Spreasheet

Thanks I'll take a look later tonight,

And errr ummm I am putting this together in my spare time

Ash

Correct formular

Hi,

Just noticed one thing on the spreadsheet you put together, lets say as you suggested that you predicted Reading to finish 10th but in fact they won the league. In that instance you would recieve 9 points, because they were 9 places away from where you predicted. To confirm the aim is to get the lowest score possible, and therectically you could get 0 points

thanks

Ash

Sorry, if Reading come 1st and you predicted 10th, do your rules indicate a score of zero or minus nine?

Scores

neither, if you predicted Reading to finish 10th but they acctually finish 1st then you would score 9 points as 1st is 9 places from 10th in the same instance if you predicted Reading to finish 10th but the finished 19th you would also score 9 points because 19th is 9 places from 10th position

So the scoring system is waited to ensure the winner is the person who most closesly predicts the correct position of each team regardless of the team finishing higher or lower than predicted.

Hope this makes sense, your profile says you are in the South West, where abouts?

Ash

OOps, my formula just became obsolete. Hope the layout works for you at least!

I live in Swindon, work in Chippenham

Formula

Any chance you could alter the formula to make it work? I am an expert on firewalls and VoIP but not on Excel

Not far from me, I work in Cirencester

Ash

Replies
2
Views
325
Replies
1
Views
216
Replies
0
Views
2K
Replies
2
Views
3K
Replies
0
Views
620

1,219,998
Messages
6,151,379
Members
451,024
Latest member
saotome1

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back