Sign in to follow this  
Followers 0
timesle

SM Predict Excel Sheet

7 posts in this topic

Since a year and a half I have now been in charge updating the SM Predict scores and tables every week. When I started I created an Excel sheet where I could write down the scores each week - a simple sheet for numbers. But over the past 18 months I improved this sheet further and further and now it's not only a sheet to write down the scores - it's my biggest helper in calculating the scores and updating every week.

I guess I won't be in charge of the SM Predict forever. Actually I already can see me leaving quite soon. Of course my followers won't have to use the sheet if they don't want to. But I can guarantee that it can be helpful if you know how to use it.

As my trains of thought were quite confuse from time to time, the sheet is easy to understand for me but it might be a bit complicated for an outsider. So I decided to create a tutorial how to use this excellent sheet.

Here you can download a blank sheet:

Shelbourne FC likes this

Share this post


Link to post
Share on other sites

Re: SM Predict Excel Sheet

SM Predict Excel Sheet Tutorial

Sheet 1 - "Score"

predictsheet1bezs1.jpg

The "Score" sheet contains the scores of each week and of each user. First I used this sheet to write down the score of each user just to be able to remember later what has happened in earlier rounds.

The value in the cell "A2" is calculated based on the amount of user names in the cells below. This cell is very important for almost every macro and should never be changed.

Basically it would be possible to update the scores if you just write the individual scores where they belong in this sheet. However I also recognized early that it's often kind of annoying to search the user. That's why I created the "Edit Sheet", which will be explained later.

Nowadays it's not necessary any more to change something in the "Score" sheet.

Share this post


Link to post
Share on other sites

Re: SM Predict Excel Sheet

SM Predict Excel Sheet Tutorial

Sheet 2 - "Edit Sheet"

unbenanntwmz7c.jpg

The "Edit Sheet" - as explained before - was created in order to avoid long searches for names when updating the score of an individual user.

Moreover it was necessary to add a new user when updating the score - if the user hasn't predicted before.

To Add a new user:

  1. Enter a name in the cell C4 - which is currently filled with "New Guy"
  2. Simply click on "Add"
  3. Then the user with the name "New Guy" will be added

To Update the score:

  1. Choose a Predictor in the Cell C12.
    unbenannta0kvn.jpg
  2. Choose the week you want to update.
    unbenanntdzkjk.jpg
  3. Add the score manually in C16 (currently "3") and click on "Update".
  4. The value will be written in the sheet "Score" at the respective place.

Share this post


Link to post
Share on other sites

Re: SM Predict Excel Sheet

SM Predict Excel Sheet Tutorial

Sheet 3 - "Predict-table"

This sheet was created in order to calculate both the overall table and the table of an individual week.

To update the overall table:

  1. Simply click on the button "Update the table".
  2. The complete table already is selected and can be copied with "Ctrl" + "C"

unbenannt1eb06.jpg

To update an individual round:

  1. First chose a week/round in the cell M1.
    unbenanntxfra0.jpg
  2. Then click on "Update the Round".
    unbenannti2q75.jpg
  3. Again complete results of this round will be selected and can be copied directly with "Ctrl" + "C".

Gozzy likes this

Share this post


Link to post
Share on other sites

Re: SM Predict Excel Sheet

SM Predict Excel Sheet Tutorial

Sheet 4 - "Complete Week Update"

This addition followed soon after I implemented the "Edit Sheet".

As ideally not only one guy is responsible for updating the SM Predict - it's quite clear that when one updates the scores in his sheet, the other one will either have to insert the data in his sheet again or they have to share their updated files via E-Mail. Both opportunities weren't ideal to me.

unbenannttvz6y.jpg

Above you see a minimized picture of the sheet.

To update a complete Week:

  1. First you have to copy the part with the results (with "Ctrl" + "C" ) posted by the guy who calculated the scores for a round and who posted it (normally) as the last post in the individual "Post your scores"-thread.
    unbenanntodbmj.jpg
    Warning:
    It's important that the format retains unchanged with the spaces between scores/names and the equal sign otherwise the used macro will screw things up.
  2. Then you have to select the color-coded Cell B3 in the sheet and paste the copied selection with "Ctrl" + "V". It should look like this now:
    unbenanntguu39.jpg
  3. Then select in H3 the week you want to update.
    unbenanntxhuyj.jpg
  4. Finally click on the button "Update the complete week".
  5. Now everything should be updated (even new predictors should have been added).

Share this post


Link to post
Share on other sites

Re: SM Predict Excel Sheet

SM Predict Excel Sheet Tutorial

Sheet 5 - "Input"

This sheet and sheet number 6 called "Calculation-Sheet" belong together. Still I will explain both individually. I've developed both sheets only last week. This means though they should work properly the probability for bugs is the highest at those sheets.

The idea behind these sheets was, that it should be possible to copy a whole prediction made by a user and save it temporary in Excel. Having noted the scores a user has predicted, it's rather easy to calculate the final scores of a round (Credits to Stuart H here, who gave me the idea for this at a special SM competition). However the main problem so far was to extract the numbers of the predictions of an individual user - especially when there are more than 2 numbers in a row (e.g. when the name of the clubs contains numbers like 1899 Hoffenheim). This problem has been solved by creating some functions that help to find a number or - if more than 3 numbers were found - that asks to enter a appropriate result.

Same of course applies for the FGS. Some users use different formations by entering the FGS. Normally the sheet can find the FGS and if it can't find it, you will be asked to enter one manually. However it might also occur that only parts of the FGS will be read in. So it's always advisable to check these again before submitting the results to the table. But step by step.

unbenanntjfqn0.jpg

Here you see the overview of the Input sheet.

To add a user's prediction to the excel sheet:

  1. First copy the whole prediction a user has made by selecting it and pressing "Ctrl" + "C".
    unbenanntcfb21.jpg
  2. Then select the cell B2 in the sheet.
  3. Press "Ctrl" + "V" to insert the copied selection.
  4. Now you should already see the results in the black box.
    unbenannt0bzji.jpg
    If there would be any problems (more than 2 numbers, no FGS found) then a message box would appear that would ask you to insert the correct data.
  5. By clicking on "Sort it" you will be asked for the name of the Predictor. The box provides already all names of predictors that have predicted already this season.
    unbenanntp7f3m.jpg
    (If it is a new predictor, then just enter his name.)
  6. Clicking on "OK" will transfer the data to the the "Calculation-Sheet".

Share this post


Link to post
Share on other sites

Re: SM Predict Excel Sheet

SM Predict Excel Sheet Tutorial

Sheet 6 - "Calculation-Sheet"

The data that is collected in the sheet "Input" is transferred to the "Calculation-Sheet" so that every individual prediction is listed here as you can see below.

unbenannt34jee.jpg

The cell A1 counts the number of collected predictions. This sell mustn't be changed as it will lead to bugs otherwise. Moreover you see cells C3 to R5 which have frames:

  • In row 3 it's possible to write down the teams whose matches are predicted in the individual week (necessarily in the order they have been chosen).
  • In row 4 you choose the match which is ESB by writing "ESB"
  • In row 5 you write down the actual results.

So for example in the image posted, Stoke played against Chelsea and the it was not the ESB game. It ended 0-4.

Below the framed cells the predictions of the users were transferred as well as their chosen FGS.

Right beside the above posted picture you find more things in this sheet posted in the second picture.

unbenannt72jb3.jpg

There are 4 buttons and more data.

The data is a calculation of the scores each user got for his predictions based on the predictions that were input and written in the first picture of this post. Furthermore the sum of their scores is calculated here as you can see in the far right of the picture.

What the buttons do:

"Clear Predictions":

This button clears all the predictions made and written down in this sheet.

"Clear All":

This button clears all the predictions made and written down in this sheet as well as the teams, ESB and the Actual scores.

"Clear Last Row only":

This button clears the last row of the predictions as long as the last row has a predictor name. This button can be used if a mistake was made by reading-in the predictions.

"Add Scores":

This button should be used as soon as all the games have been played and entered in the cells C5 to R5.

After clicking it, you will be asked to choose the week for this predictions.

If there is already data written in this week, a warning will appear and you have the chance to cancel the action in order not to overwrite the data.

If no data is written in this week or if you choose to overwrite it, the scores will be noted in the sheet "Score" and you will be forwarded to the sheet "Predict-table", where the table of the round you just have chosen will be updated.

AsianInvasian and lukelufc like this

Share this post


Link to post
Share on other sites
Guest
This topic is now closed to further replies.
Sign in to follow this  
Followers 0