+ Reply to Thread
Results 1 to 37 of 37

Soccer outcome prediction

  1. #1
    Registered User
    Join Date
    05-24-2016
    Location
    Greece
    MS-Off Ver
    2016
    Posts
    41

    Soccer outcome prediction

    Hello everyone. I want to know if there is any way to get predictions from my data in excel. I dont know if i need a vba or a formula can do the job. I am almost new to excel.
    What i want to do is to put odds for 1X2 and check in my data and return me the percentace of the outcome. I will attach my excel file here so i can explain more if you dont understand what i want to do

  2. #2
    Registered User
    Join Date
    05-24-2016
    Location
    Greece
    MS-Off Ver
    2016
    Posts
    41

    Re: Soccer outcome prediction

    here is the file
    Attached Files Attached Files

  3. #3
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    15,802

    Re: Soccer outcome prediction

    Short but useless answer -- Yes, it is possible to get predictions form your data in Excel. This kind of analysis is not preprogrammed into Excel, but someone with a solid understanding of the desired algorithms and Excel's worksheet functions (and/or VBA if that is your preferred programming language) should be able to program Excel to analyze the historic data and come up with future predictions.

    I am not very familiar with these algorithms. Everything I know about these algorithms is limited to a few introductory articles to Elo rating systems (https://en.wikipedia.org/wiki/Elo_rating_system ). What kind of rating or ranking system do you want to use for your analysis and prediction? If you will explain to us the desired algorithm and ask specific questions about the specific part of implementing that algorithm that you are having trouble with, I'm confident we can help.
    Quote Originally Posted by shg
    Mathematics is the native language of the natural world. Just trying to become literate.

  4. #4
    Registered User
    Join Date
    05-24-2016
    Location
    Greece
    MS-Off Ver
    2016
    Posts
    41

    Re: Soccer outcome prediction

    Screenshot_2.png
    Here is a picture so i hope i can explain what i want to do
    Where it says input i put odds for 1,X and 2 like the match i have there, so i put 1,57 for 1odds, 3,35 for Xodds and 4,60 for 2odds. What i want to do next is to automatically search the sheet1 (from the excel i uploaded before) and find matches that had exactly same odds and tell me % of outcome to be 1 X or 2. Lets say it found 100 matches with exactly same odds, where 80 of them came 1, 10 came X and 10 came 2...so it will return 1-80%, X-10% and 2-10%. Ihope i made it more clear what i would like to do so someone can help me

  5. #5
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    15,802

    Re: Soccer outcome prediction

    That sounds like the ratio of a COUNTIF() function to a COUNT() function. =COUNTIF(D2:D3000,1,57)/COUNT(D2:D3000)
    COUNTIF() function: https://support.office.com/en-us/art...4-1f464816df34
    COUNT() function: https://support.office.com/en-us/art...4-d23bf411294c
    Other functions: https://support.office.com/en-us/art...rs=en-US&ad=US
    Last edited by MrShorty; 05-25-2016 at 12:57 AM.

  6. #6
    Registered User
    Join Date
    05-24-2016
    Location
    Greece
    MS-Off Ver
    2016
    Posts
    41

    Re: Soccer outcome prediction

    Thanks for the answer Mrshorty i know already this functions the problem is that i want when i put odds for 1X2 to find the ammount of matches that had the same odds (lets say 1=1,55 , X=3,20 and 2=4,60) i think it will be like filtering, and then search what was the outcome of these historical matches (if i find 100 matches with these exact odds 80 came 1, 15 came X and 5 came 2) and say to me the percentance to come 1 is 80% etc... Like when i filter manually i search and it shows me only those matches with these odds.

  7. #7
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    15,802

    Re: Soccer outcome prediction

    It still sounds like a countif()/count() function. When I paste that function into your spreadsheet, I get 1.115% (31 incidences of 1,57 out of 2781 entries). Is that not correct?

  8. #8
    Registered User
    Join Date
    05-24-2016
    Location
    Greece
    MS-Off Ver
    2016
    Posts
    41

    Re: Soccer outcome prediction

    I have some issue with this formula cause count(d2:d2782) returns me 0 and i get this error #div/0! (I fixed this with counta() function, my excel is 2016) but beside this i want to make like a drop down menu and i want all three odds i put to search if they are in same rows. i will put a screenshot to explaine but what you say about countif its correct but not what fits to meScreenshot_1.png
    Here as you can see i filter odds for 1 X 2 and found 3 matches that fits to those odds and all of them came 1. This also can be seen in AJ column wich is the result. Sorry i have it in Greek. So the probability for a new match with exact odds is 100% to be 1, 0% to come X and 0% to come 2 off course those statistics will change by time when the matches that fits those critiria be more than 3 but i want to show you what i want to do.
    Last edited by dimitrisko; 05-25-2016 at 09:59 AM.

  9. #9
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    15,802

    Re: Soccer outcome prediction

    I don't think I am fully understanding, yet.

    cause count(d2:d2782) returns me 0 and i get this error #div/0! (I fixed this with counta() function, my excel is 2016)
    This suggests that your numbers are really text strings. Which is fine, as long as you recognize that these are not numbers and do not try to compare the number 1,60 to the text string "1,60". You may want to consider this aspect, because we frequently see problems caused by "numbers stored as text."

    Is it a COUNTIFS() function that you need instead? =COUNTIFS(D2:D3000,"1,60",F2:F3000,"3,30",H2:H3000,"4,40") returns 3, just like you show. The denominator is still the same COUNTA() function (2781).

    I do not understand how column AJ is derived from D:H, so I'm not sure how AJ fits into the analysis.

  10. #10
    Registered User
    Join Date
    05-24-2016
    Location
    Greece
    MS-Off Ver
    2016
    Posts
    41

    Re: Soccer outcome prediction

    Yes that works great but i want to make it something like vba?! Well this function returns me that there are 3 matches that has those critiria but i want to somehow make a vba where it ask me to put these odds and after it find those 3 matches, go to AJ where i store the outcome of the match, and tell me the possibility off the outcome. In the previews example we have three matches that meets same critiria and we see that all of them came 1. So the % is 100 for 1 and 0 for X and 2. If the critiria i give show me 10 matches where 6 came 1, 3 came X and 1 came 2 (from AJ column) then the percentance changes. As you can see the picture 6 posts before i want to put (where say input odds) and when it meets the critiria to give me the percentance of the outcome (1%,X%,2%).
    I tried to create a list (with data validation) but i dont know what else to do to make them work the way i want.

  11. #11
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    15,802

    Re: Soccer outcome prediction

    It still feels like a combination of COUNTIFS() (which is available to VBA, if you really want to use these worksheet functions in VBA: https://msdn.microsoft.com/en-us/lib.../ff197608.aspx ). It seems to mostly be about defining all of the pertinent conditions for the numerator and denominator in the ratio.

    If we make the previous functions the denominator: =COUNTIFS(D2:D3000,"1,60",F2:F3000,"3,30",H2:H3000,"4,40") returns 3
    For the numerator add the AJ condition: =COUNTIFS(D2:D3000,"1,60",F2:F3000,"3,30",H2:H3000,"4,40",AJ2:AJ3000,"1") should return 3, so the ratio of these is 3/3 or 100%
    2nd condtion: =COUNTIFS(D2:D3000,"1,60",F2:F3000,"3,30",H2:H3000,"4,40",AJ2:AJ3000,"X") returns 0, so the ratio returns 0/3 or 0%

    If you want to apply a filter to the list along the way to doing this, I could see using the advanced filter tool instead of autofilter. Advanced filter will allow you to copy the filtered list into another block of cells, then have your counting functions working on the filter's output block. https://support.office.com/en-us/art...8-3f16abdff32b

  12. #12
    Registered User
    Join Date
    05-24-2016
    Location
    Greece
    MS-Off Ver
    2016
    Posts
    41

    Re: Soccer outcome prediction

    I saw the links but i dont understand too much about vba..I did advanced filter but in countif()/count() and i have this #DIV/0!. Do you know any way to bypass this and show me 0?

  13. #13
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    15,802

    Re: Soccer outcome prediction

    If the error is "real" and expected, then you can simply nest your function inside of an IFERROR() function. =IFERROR(your function here,0) If your function evaluates to an error value, then the IFERROR() function will return 0.

    If the error should not occur, because the count is erroneously returning 0, then explore what that is occuring. As I noted above, all your numbers appear to be "numbers stored as text", which causes the COUNT() function to return 0 because the COUNT() function ignores text (including numbers stored as text). As you noted above, this can be corrected by using the COUNTA() function which includes text values in its count.

  14. #14
    Registered User
    Join Date
    05-24-2016
    Location
    Greece
    MS-Off Ver
    2016
    Posts
    41

    Re: Soccer outcome prediction

    Well i fixed it with iferror function but now i have another problem. I will put a screenshot to show youScreenshot_1.png
    As you can see the function is correct but the problem is i have 3 over (O) and 3 gg but under OVER and GG gives me 0? What i did wrong i cant figure out



    Sorry for keep replying. I solved it again..I am almost new and i forget. Its counta() that i had to use!
    Last edited by dimitrisko; 05-26-2016 at 10:07 AM.

  15. #15
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    15,802

    Re: Soccer outcome prediction

    What result do you expect? #O's/#GG's which would be 3/3, or 3/6, or ???

  16. #16
    Registered User
    Join Date
    05-24-2016
    Location
    Greece
    MS-Off Ver
    2016
    Posts
    41

    Re: Soccer outcome prediction

    It's over/under from AM and gg/nogoal from AP but i solved it i use counta() because they are text

  17. #17
    Registered User
    Join Date
    05-24-2016
    Location
    Greece
    MS-Off Ver
    2016
    Posts
    41

    Re: Soccer outcome prediction

    I found the problem..It was again counta. I have some issues though with a macro in the sheet named "ΠΡΟΓΡΑΜΜΑ" can someone resolve it?
    i will attach the file here! The problem is at button "ΑΝΑΖΗΤΗΣΗ"
    Attached Files Attached Files

  18. #18
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    15,802

    Re: Soccer outcome prediction

    I do not see a button with that name in the workbook, nor a macro with that name. The macros associated with the two buttons are not available. Check that you uploaded the correct test file.

    Also, try to explain the problem. What kind of error are you getting? A compile error, and what does it say the error is? A runtime error (and what error and where it occurs in the procedure)? It just does the wrong thing?

  19. #19
    Registered User
    Join Date
    05-24-2016
    Location
    Greece
    MS-Off Ver
    2016
    Posts
    41

    Re: Soccer outcome prediction

    I upload it again but when i dowloaded from here i can see macros. In command button "ΑΝΑΖΗΤΗΣΗ" i put a macro that do advanced filtering.
    In sheet "ΠΡΟΓΡΑΜΜΑ" from A2:f2 i make a list with data validation from sheet "ΑΡΧΕΙΟ", and the problem is when i press "ΑΝΑΖΗΤΗΣΗ" i get other staff than i have chosen
    for example i choose from the list 3-1-1 and when i press the button in C15 i get values from sheet1 but 1-3-1 instead of what i was looking for. Another problem is that when
    i put more than one (add numbers to the other list) i get nothing when i press "ΑΝΑΖΗΤΗΣΗ". 'ΔΙΑΓΡΑΦΗ' button is for clearing the staff i searched. If you cant see again the macros i 'll put a screenshot of the code
    Screenshot_1.png
    Attached Files Attached Files

  20. #20
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    15,802

    Re: Soccer outcome prediction

    I think the problem for me is that my computers read the greek characters in some of your button, macro, and sheet names wrong, so that those connections get broken (For some reason, I get Arabic characters instead of Greek characters). However, if I change the names so that everything seems properly connected, it all seems to work correctly. The macro that clears the "extract" range clears the range just fine. The macro that executes the filter seems to execute it just fine, and I get the 3-1-1 entries. At this point, I am unable to replicate your problem.

    Changes made to this file:
    1) Sheet names changed to English Sheet1 and Sheet2. Checked that the criteria and extract named ranges refer to the correct sheet.
    2) Macro names changed to English trymacro1 and trymacro2. Assigned these macros to the buttons. Changed name of button to indicate which macro is assigned to which button.
    3) Stop statements added to each macro so that VBA enters debug mode when macro is executed.

    I did not change any of the code or the contents of the workbook, and both macros seem to execute correctly.
    Attached Files Attached Files

  21. #21
    Registered User
    Join Date
    05-24-2016
    Location
    Greece
    MS-Off Ver
    2016
    Posts
    41

    Re: Soccer outcome prediction

    Thanks for the help but now with stop when i put the button prompts the widow and dont excecute the macro.
    Screenshot_1.pngScreenshot_2.png

  22. #22
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    15,802

    Re: Soccer outcome prediction

    As I indicated, the Stop statements cause VBA to enter debug mode where you can debug problems with the code. You can look at variables or cells to make sure they contain the expected values. You can step through the code one line at a time to see where errors occur. What you describe is the behavior of the Stop statement. Discussion of debugging tools available in VBA: http://www.cpearson.com/excel/DebuggingVBA.aspx

    With the code paused in debug mode, I would step through the code one statement at a time (I use the F8 key) to see if the code will run to completion.

    You can remove or comment out the Stop statements when you decide they are no longer serving any purpose, or when you finished debugging and the code works without error.

  23. #23
    Registered User
    Join Date
    05-24-2016
    Location
    Greece
    MS-Off Ver
    2016
    Posts
    41

    Re: Soccer outcome prediction

    Well i figured what is the problem (possible) is but i dont know how to approach it. as you see i put in cells Z10:AE10 the values off the list to run the advanced filtering that i put at the macro but when a filter is empty lets say A2, it count 0 and in sheet1 there is no value zero. I think this is the problem but how to no put zero if i dont filter one column?

  24. #24
    Registered User
    Join Date
    05-24-2016
    Location
    Greece
    MS-Off Ver
    2016
    Posts
    41

    Re: Soccer outcome prediction

    Also when i filter in sheet1(ΑΡΧΕΙΟ) as i put here in screenshot1Screenshot_1.pngScreenshot_2.png i find 5 matches as you can see but in sheet2(ΠΡΟΓΡΑΜΜΑ) when i put same critiria and press the button i get nothing.

  25. #25
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    15,802

    Re: Soccer outcome prediction

    I cannot readily recreate your results. When I execute the macros in the copy of the file that I have, I get three results -- the same as the first three in the screenshot you show of sheet 1. I tried to find the other two matches in the database, and could not find those 2 records anywhere in the database.

    I do not know why you got nothing. I can get three of those five records, and the other two records do not seem to be present in the database that I have. At this point, check that the code is identifying the correct database range (while in debug mode, add a watch for Sheet1.Range("A1").CurrentRegion.address or something like that) and make sure that it is identifying the correct range for the filter. You might do the same for the criteria and extract ranges, too.

  26. #26
    Registered User
    Join Date
    05-24-2016
    Location
    Greece
    MS-Off Ver
    2016
    Posts
    41

    Re: Soccer outcome prediction

    Well take a look at this. This is something i couldn't expect.Screenshot_1.png
    From A2:F2 is what i want to search for but look down what happens. In 2-4-1 it recognize it 4-2-1 that's why i get nothing sometimes. How to disolve it? why it takes some of my numbers in different position to understand it (to search for 4-2-1, i hava to put 2-4-1 in e2)

  27. #27
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    15,802

    Re: Soccer outcome prediction

    I still cannot recreate what you are seeing. In the copy that I have, {2-2-1,2-2-1,2-3-1,2-3-1,2-4-1,2-4-1} returns no records. I can find three records with the inverted numbers (the same first two as your screenshot shows, and the third seems to match the partial row in the picture), but they are ignored by the filter as specified.

    Perhaps one thing to look at. I do not know what error checking options you have turned on. I have almost all of them turned on, and of these a-b-c cells have a green error triangle in them. The error reported is "text date with 2 digit year". In my case, Excel is raising the error flag, but it is not converting the text to a date. Is it possible that your copy of Excel is choosing to convert the string to a date? Given the ambiguities between European and American conventions for dates (Is 2-3-1 February 3 or March 2?), I could see how this could contribute to a problem like you are describing. On my copy, all these cells are formatted as text and the ISTEXT() function returns TRUE. You might try some similar checks to see if your copy of Excel is possibly interpreting these text strings as dates somehow and creating the problem that way.

  28. #28
    Registered User
    Join Date
    05-24-2016
    Location
    Greece
    MS-Off Ver
    2016
    Posts
    41

    Re: Soccer outcome prediction

    Do you know how to avoid this recognition from excel and dont just ignore it? Cause i do istext() function and says true but excel keeps recognizing like thisScreenshot_1.png
    Last edited by dimitrisko; 05-28-2016 at 11:01 AM.

  29. #29
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    15,802

    Re: Soccer outcome prediction

    Have you been able to verify that it is actually converting the text to date? As I noted, I get the same warning message in 2007, but it does not effect the results -- which seem correct. Perhaps 2016 behaves differently from 2007 in this respect?

  30. #30
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    15,802

    Re: Soccer outcome prediction

    Might I suggest this test -- replace the "-" character to something that unambiguously makes these text strings. Maybe "a" ("2a2a1" or "2 2 1"). A simple find-replace command should do this easily. Removing the possibility that Excel will convert these to dates or something else might help debug the problem. Or maybe drop the "-" character completely and convet them to numbers (221,321)
    Last edited by MrShorty; 05-28-2016 at 11:19 AM.

  31. #31
    Registered User
    Join Date
    05-24-2016
    Location
    Greece
    MS-Off Ver
    2016
    Posts
    41

    Re: Soccer outcome prediction

    I tried the last one but it was worst because some forms i have 0-0-5 excel deletes zero's and if i add them again i keep having problem with search vba. I ll try to put ; instead of -

  32. #32
    Registered User
    Join Date
    05-24-2016
    Location
    Greece
    MS-Off Ver
    2016
    Posts
    41

    Re: Soccer outcome prediction

    Well everything worked except / and - . i put ; and everything worked fine!

  33. #33
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    15,802

    Re: Soccer outcome prediction

    That seems to further suggest that it is a "convert text to date" thing (if you wanted to try ":", I suspect that will also fail because of the association with time notation). Because I cannot recreate the results in 2007, I have put out a request to others on the forum to see if anyone with 2016 can recreate your results and otherwise offer input.

    Are you content to use ";" as your divider in these text strings? If it works to your satisfaction, then it might not be worth a lot of effort beyond what we have done to this point.

  34. #34
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,378

    Re: Soccer outcome prediction

    Cannot recreate in 2010 either
    If I've helped you, please consider adding to my reputation - just click on the liitle star at the left.

    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~(Pride has no aftertaste.)

    You can't do one thing. XLAdept

    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~aka Orrin

  35. #35
    Registered User
    Join Date
    05-24-2016
    Location
    Greece
    MS-Off Ver
    2016
    Posts
    41

    Re: Soccer outcome prediction

    It work's perfect ; for me no problem any more!! Thanks for your help. Also i tried ` , and . everything worked perfect except : and - becuase it refers as you noticed to time and date, but its not a problem for me!!

  36. #36
    Registered User
    Join Date
    06-30-2017
    Location
    Nigeria
    MS-Off Ver
    10
    Posts
    2

    Re: Soccer outcome prediction

    Thanks for the prediction work in excel, in fact it is fat antic please can you translate it to English for Me.

  37. #37
    Registered User
    Join Date
    06-30-2017
    Location
    Nigeria
    MS-Off Ver
    10
    Posts
    2

    Re: Soccer outcome prediction

    Mr dimitrisko Kindly help me with the soccer predictions in excel for 2017.Thanks

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. About prediction !
    By bkmgeorge in forum Excel General
    Replies: 2
    Last Post: 12-19-2014, 04:31 PM
  2. Backward prediction
    By arkiv in forum Excel General
    Replies: 1
    Last Post: 02-12-2014, 12:11 PM
  3. [SOLVED] Football Prediction help!!
    By Welsh in forum Excel Formulas & Functions
    Replies: 13
    Last Post: 06-21-2012, 12:47 PM
  4. Prediction formula
    By karnold in forum Excel General
    Replies: 6
    Last Post: 06-19-2012, 10:39 AM
  5. Prediction Sheet
    By excelit in forum Excel General
    Replies: 0
    Last Post: 03-22-2011, 04:49 AM
  6. Forecast Prediction
    By San in forum Outlook Programming / VBA / Macros
    Replies: 0
    Last Post: 07-25-2009, 11:25 PM
  7. Prediction Graphs
    By peabushmartin in forum Excel General
    Replies: 1
    Last Post: 09-21-2005, 01:25 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1