Prediction league tables

[mattant]

Hey everybody,

I am a member of a football forum, on which we have a prediction league split between two divisions. I used to assist the co-ordinator of it and he has recently come to me asking if I know of any way we could simplify the process, as it requires a lot of work on his part.

My first thought was that something online may shed some light on it, but I realised that the scoring system is actually quite complex, so I realise that I may now need to create my own spreadsheet to cater for this.

I'll quickly outline the prediction rules so that whoever can help has the most information as possible:

Every fixture that is played by our team, every predictor needs to predict three things:
- Outcome of the match (i.e. what score it will be, e.g. 2-1 win)
- First goalscorer of our team
- Minute in which first goalscorer scores.

The score of each match is then calculated, as follows:

Correct score = 4 points
Exact time of first goal = 4 points
Correct time bracket of first goal = [u]2 points[u] [time brackets are in 15 minute slots i.e. 00-15, 16-30, etc...]
Correct time bracket of no goal scored = 1 point
Correct result [i.e. win/loss/draw] = 1 point
Correct goalscorer = 4/3/2/1 points [defenders get 4 points, midfielders get 3 points, strikers get 2, and prediction of no goalscorer gets 1]

What I need to do is find a way to input the predictions of each player into a table (with goalscorer and all) and then submit the actual score, result, first goal, first goalscorer, and have excel work out how many points each player gets for one week.

I realise this sounds complicated and I'm not even sure if it's possible to do this on excel, but I thought if I can find the answer anywhere, it will probably be here.

Many thanks,

Matt.

[XOR LX]

Hi Matt,

Sounds plausible to me. If you could drum up the initial spreadsheet and indicate where and what your expected results would be, perhaps including a few dummy examples, then I'm sure someone will be happy to work out the formulas to get you what you want.

The better and more thought-out your original design (the simpler the better!), the easier will be the formula work. Remember that Excel works best with your data arranged in tabular form, and if the number of these tables can be kept to a minimum (ideally one for data and then perhaps another in which to record your desired summary results) then all the better.

Regards

[Bhuvi]

Hi Matt,

According to your requirement I have made a layout in a sheet. It still needs to be worked on.

The sheet has two tabs "Match_Details" and "Predictors" . In the sheet match details has the actual results of the match and the predictors tab contains the predictions before the match.

We can put formulas in the sheet which matches the results with predictions relating the Match Id and provide the final prediction score.

Please let me know if I understood your requirements. Please add info/suggestions whatever you need to add.

I hope this sheets does fine on you Mac 2011, mine is ms excel 2007.

Regards,
Bhuvi


if you with to say thanks please click on the <----- star * below the post.

[mattant]

That looks really good Bhuvi!

I've added my own prediction as well as a few comments, please let me know what you think.

Format_2-1.xlsx

Now, what we would need is a system, whereby the points won week by week can be accumulated, so as to create a league table, like so:

Let's assume we are two players (Bhuvi and mattant).

In week one (Match ID #1), you pick up 9 points, while I pick up 2. In week two, you pick up 6 points and I pick up 12. In week three, you pick up a max. 15 points and I get 11.

That means you have a grand total of 30 while I have 25. I need some way to show that you are top and I am bottom. Is this possible to accumulate into a table to create an ongoing league table?

Matt.

P.S. Thank you for your words of advice XOR LX

[Bhuvi]

Hi Mattant,

In the attached sheet's tab "Predictors" you have to enter the name and match id in col B and C and the predictions in col D to col I.
Then the columns J to N automatically calculates the points according to the conditions specified given that the details of the match Id are present in the tab "Match_Details". Col A and col J to N are formula fieds, please drag the formulaes down when you enter new rows of data.

In the tab "Match_Details" you have to enter all the details of a match from columns A to I. Col J is a formula field please drag the formula down when you enter new rows of data.


In the tab "Ref Sheet" you need to maintain the data for teams, players and members. Then you not need to enter the team and player details in the other two sheets as you would get a drop down to select.

I hope this meets your requirements, Please go through the sheet and let me know of any shortcomings.
There is always a scope for improvement.

Regards,
Bhuvi

<If you wish to say thanks then please add to my reputation by clicking on the <----- star * below this post>

[mattant]

That looks brilliant to be honest Bhuvi! It's really amazing what you can do with Excel

The only thing that I'm missing is a league table that tots up the total points over the season, but I'm not sure if that will be too much to ask!

Sorry for the delay by the way - I was away for a few days

[Bhuvi]

Hi Mattant,

Good to see you back! and Thanks for the appreciation, I really enjoyed working on this task

The points per week for every player can be totalled up using a pivot table.

In the attached sheet I have added a pivot table on tab "Pivot" which summarizes data from the tab "Predictors".
When you add rows of data to predictors just refresh the pivot table (click on the pivot>> right click>> refresh).It would give you the week wise horizonatal sum of points for each player.

Regards,
Bhuvi


<click on the <<----- Star * below the post if my reply helped>

[mattant]

That looks brilliant Bhuvi thank you so much! I'll take it back to my mate and see what he says about it.

Thanks a lot,

Matt.

[mattant]

Bhuvi, just would like to say thank you, the table has worked out fantastically and is saving my mate a lot of time and effort!

I'll link you to the page, in case you'd like to see how it's being used. http://leedsunitedfanclub.hoop.la/to...s-leeds-united

Thanks again, you were a massive help.

[Bhuvi]

Hi Matt,
I felt great to see this sheet being used for points calculations posted online It was always a pleasure for me to work on this.
You are welcome