Help with creating an Excel Football (soccer) simulator

I am looking for some help in creating a football match simulator in Excel.

To set the scene here, I already use Excel to simulate the English Football League and cups largely based on the old dice game called Logacta. This initially started as a test project to design and football league table that automatically updated, with teams moving up and down the league as the scores changed. I achieved this using the RANK and VLOOKUP function and the project has mushroomed from there. I started by using the actual dice but found that restrictive and used the RANDBETWEEN function in Excel to simulate the dice options and then hit F9 to run each match result.

What do I want it to do?

What I want to do now is create and Excel sheet which goes into a bit more detail for each game and plays multiple games simultaneously at the touch of a button. Added into this, I need to able able to select a form factor for each team. So, like in Logacta where you choose a dice which would be more favourable to one team or the other based on their league position / points. I know the equations for this, as I have a tried and tested formula which works.

Ideally, I want it to be able to see the events (shots, penalties, maybe possession) for each game. My thought would be to have 180 events for each game (one event for every half minute). In effect, this would be Excel running a loop on a RANDBETWEEN type function and returning something like (shot, penalty, or possession). I have in essence done this but want to be able to have a time delay between each event and to have a button where I can start and stop a game without having hit F9 every time I want a new event to happen.

When Excel hit the ‘shot’ or ‘penalty’ option, it would then need to go to a sub RANDBETWEEN which would have other options ‘shot’ = (missed, saved, hit the post, shot blocked and GOAL!); ‘penalty’ (missed or GOAL!). I would need to control the first RANDBETWEEN function by the form of the team, so there would need to be say 6 options (i.e. the better team would be more likely to have more ‘shots’ or ‘penalties’)

I think I would be able to achieve this by designing a MACRO, but I am not sure quite how to achieve it?

What do I want it to look like?

Visually, it probably needs to look like a vide-printer or Sky Sports Soccer Saturday look. Either way, I need to be able to link it to the live league table on a separate Excel sheet, so as each goal goes in at simultaneous matches, I can see the changes.

What else?

I also want to able to run the same MACRO or sheet to play cup games, so I would need to factor in extra-time and penalty shoot-outs. Having said that, I designed my game where the penalty shoot out is done at 90 minutes and the winner holds an advantage in a golden goal extra time. So, if there are no goals in extra time, the winner of the penalty shoot out after 90 minutes wins the game. If either team scores in extra-time, then they win on the golden goal rule.

Any help or pointers in the right direction will be greatly appreciated here. I am actually doing an Excel course on MACRO’s, but haven’t come round to finding out how to solve this game query yet.

Dear Jeff

Thank you for getting back to me. Please accept my apologies if I have done something wrong here, I am new to the forum and am not used to everything yet.

I have an Excel sheet with most of the working on and will update it with a full description of everything I would like it to do. In essence, I can get Excel to do what I want to do manually with an F9 hit, but would like to be able to get Excel to run the RANDBETWEEN functions with a time delay, so I can see a game unfold in front of me. I am guessing that a MACRO will be able to do it, but I haven't learnt how to do that yet. I will re-post on this thread, when I have everything more sorted, with a layout design idea on an Excel sheet and my workings to date.

Many thanks for your help, it is much appreciated.

Regards

David

Attached is an Excel spreadsheet with my workings to date.

What do I want it to do?

My aim is automate what I can basically do manually now. The problem with my manual way is that I hit F9 and the whole game calculates immediately. What I would like it to do is hit a start button and for game to unfold automatically over a short time period in front of me on the ‘Live Score and Events’ tab
On the first tab, I have put a rough flow chart as to how I think this needs to work.

Step 1

The first part of the process will be to select a rating for each team, which in this case I have defined as colours, as the original game was based on coloured dice. On the tab called ‘Options’, you can see that there are eight colour options and I have set up arrays of 180 cells with a sliding amount of ‘Shot’ cells. The more ‘Shot’ cells, the greater chance of a goal being scored.
The colour selected for each team needs to refer to the array on the ‘Options’ tab. So, if Team 1 is rated Blue then it should run a RANDBETWEEN for cells F7 to F186, if Team 2 is rated Yellow then it should run a RANDBETWEEN for cells I7-I186. I can reduce the number of cells on the RANDBETWEEN if needs be, as long as I can get the right ratio of average ‘Shot’s’.

Step 2
A RANDBETWEEN is run for Team 1. If it returns a blank, then the game moves on. If it returns a ‘SHOT’, then it needs to do a RANDBETWEEN of the ‘OUTCOME’ array (cell K7-K44) on the ‘Options’ tab. Whatever the ‘OUTCOME’ is will determine if a goal is scored. The only caveat here is if the RANDBETWEEN returns ‘PENALTY’ (1 in 38 chance). In that case, it needs to run a RANDBETWEEN of the ‘PENALTY’ array (cell M7-M16) as well.
The same cycle as for Team 1, will need to happen for Team 2

Step 3
I want this process to loop 180 times (1 loop for every half minute of a 90-minute football match). Ideally, I would like the loop to trigger the game clock on ‘Live Score & Events’ tab to show a half minute increase on each cycle.

Time Delay
What I would like to do is incorporate a slight time delay between each event and loop, so the game unfolds over say 3-5 minutes. If I know how to do this, I can fine tune the time delay anyway.

Live Score & Events – VISUAL TAB

This is where I would like to see the match events unfold. The teams selection is a manual choice, and the logo automatically changes. The teams goals can read off the events table below, with a simple COUNTIF as it is doing now.
In terms of the events, I would only like them to show if the RANDBETWEEN goes to ‘SHOT’, with the layout being something like what I have on the tab. Ideally, I would like to see the time of the event, the outcome and if it is a penalty, then the outcome of the penalty.

If I can incorporate a ‘control button’ to start and stop the game loop that would be really useful

In the middle, I would like the time to tick over in half minute increments if possible.

My guess is that it will need to be a MACRO that does this, so any pointers in the right direction will be really helpful. Ultimately, I want to be able understand the MACRO (if that is the solution), so I can fine tune it as I go along. For information, I am currently doing some MACRO training, so I have a basic idea of how they work.
Any help or pointers that people can give here will be much appreciated.

Dear Jeff

Thank you so much, this is pretty much what I am looking for.

A couple of things:

1. Can I get a drop down menu (cell B4 and H4) where I am able to select the colour array for each team?
2. When the outcome is 'penalty', it doesn't seem to then go to choose either 'Goal!' or 'Missed' and put that result in either column E or K
3. It would be great to get a slight time delay (say 3 seconds) between the 'Shot' and then the 'Outcome'. If it then goes to 'Penalty' again a slight time delay (say 3 seconds again) before it display's either 'Goal!' or 'Missed'
4. It would be useful if the game stopped at 45 minutes (which is half-time in a soccer match) and then I have to hit start again to start the second half
5. It would be nice if the display flashed when a goal is scored, so the cell where the outcome 'Goal!' displays flashes a couple of time's. I can probably work out how to do this though.

I will have a look at the VBA you have done, to see if I can understand how this has been put together so I can make tweeks myself as I go along.

Again, thank you for your help, it will really help me along in learning to use VBA

Dear Jeff

Thank you. I have just made a payment to Commercial Services, as your help has been terrific.

I am going to try and pick my way through the VBA code to understand how each step works and then make the alterations you suggested.

Many thanks

David

Dear Jeff

I have just done the thread in Commercial Services and added your username in the title.

Hopefully, I have done it correctly?

Many thanks

David

Sure! I can help you get started with creating an Excel football (soccer) simulator. Here's a step-by-step guide to help you:

Design the Spreadsheet:

Open a new Excel spreadsheet and create different sheets for various components like teams, fixtures, and results.
On the "Teams" sheet, list all the teams participating in your simulation. Include columns for team names, ratings, and any other relevant information.
On the "Fixtures" sheet, set up a schedule for the matches. Include columns for home team, away team, date, and any other necessary details.
On the "Results" sheet, you can record the outcomes of each match. Include columns for home team, away team, goals scored, and any other statistics you want to track.
Enter Team Information:

Populate the "Teams" sheet with the names of the teams participating in your simulation.
Assign ratings to each team based on their strength or skill level. You can use a numerical rating system or any other method you prefer.
Generate Fixtures:

On the "Fixtures" sheet, create a schedule of matches. You can manually enter the fixtures or use Excel formulas to generate them automatically.
Ensure that each team plays against every other team at least once to simulate a league format.
Simulate Matches:

On the "Results" sheet, use Excel formulas or VBA macros to simulate the matches and calculate the results.
You can assign probabilities or random numbers to determine the outcome of each match based on the team ratings.
Update the results sheet with the goals scored and any other relevant statistics.
Calculate Standings:

Use Excel formulas or VBA macros to calculate the standings based on the results recorded in the "Results" sheet.
Create a separate sheet for standings and update it after each match.
You can sort the standings based on points, goal difference, or any other criteria you desire.
Analyze and Visualize:

Use Excel's built-in features like charts and graphs to analyze and visualize the simulation results.
Create graphs to display team performance, goal statistics, or any other insights you want to explore.
Iterate and Refine:

Tweak your simulation model based on your preferences and feedback from analyzing the results.
Experiment with different rating systems, match simulation algorithms, or other factors to improve the accuracy and realism of the simulation.
Remember to save your Excel file regularly and make backup copies to avoid losing your progress.

This is a basic framework to get you started with an Excel football simulator. You can customize and expand upon it based on your specific requirements and the level of detail you want to incorporate.