[Challenging] How can I organize, analyze, and predict conditional & dependent probabilities data using Excel?

[miraclewhip]

Before we get started

The only software I have available to me for the analysis and collection of this data is Excel. I will be hard coding all of the data as I collect it. The final product would hopefully be some sort of macro where I could enter the location of POI #1 and see a list of possibilities for POI #2. Once POI #2 is generated, then I would be able to see a populated list showing the probabilities of locations for POI #3, etc. If the VBA is too complex, any guidance on data collection and analysis would be appreciated.

Example

I have a simulation game that automatically generates points-of-interest (POIs) on a map.

This isn't completely relevant, but it is impossible to see the location of POI #2 through POI #7 until the prior POI is discovered by the player. So the player walks to POI #1, and POI #2 is discovered, etc.

The first POI generation is random. The first POI could populate either the Western, Central, or Eastern lanes.

Once the first POI is generated, the second and third POI are partially dependent on the location of POI #1.

What are you trying to do?

I also aim to determine what the probability is of POI #2 being located at coordinates x, given that POI #1 was located at y.

Lastly, I need to determine the probability of POI #3 being located at coordinates r, given that POI #2 was located at m.

After determining all of the above factors, I should be able to tell someone the probability that POI #3 will land at coordinates p, given that POI #1 was located at coordinates w.

The tl;dr of what I'm trying to accomplish

I essentially aim to predict the location of POI #3, given the locations of POI #1 and POI #2. Hypothetically, the data should become more accurate as POIs are generated.

Important side notes

I) The locations of POI #2 and POI #3 are not random. They depend in part on the location of POI #1 (POI #1 & POI #2 in the case of POI #3)

II) I have no idea how many possible positions there are

III) The only way for me to collect data is by running the simulation, which will give me a considerable amount of data

IV) This data needs to be collected and analyzed for several different maps. I'm thinking to just analyze one map per spreadsheet to make things easier from a formatting standpoint.

V) In the simulation, the locations are returned as town names and not coordinates, so please don't get caught up on any math things.

VI) I am using the most recent version of Excel on Windows 10, and I am open to use of macros/VBA.

The data

So what I need to determine here is how to organize this data as I collect it, how to analyze & interpret the data after it is collected, and lastly how to use the data to predict the outcomes of the game (in other words, how can I determine the location of POI #3 given the locations of POI #1 and POI #2).

That's all(ot)!

I know this was really wordy, but I'm hoping someone will find this a fun challenge. Otherwise I'm going to lose my mind trying to solve this.

Video Explainations

Here is a short video from the game that my simulation was modeled after. Note that the map only currently shows the position of POI #1. As he states, once Lumber Yard is randomly chosen as POI #1, there is a 100% chance that the objective will stay in that lane for the rest of the map.

What I aim to do is run my simulated events (exactly similar to this game, and modeled in the SDK) until I have enough data where I can determine the probability of every possible outcome for POI #1, #2, and #3, and the dependent probabilities between them.

Video #1, Explaining

Video #2, Example

Here's one last video that quickly gives an example of what he's talking about in the second video.

Video #3, further explanation

Note that he has this information because of his own independent analysis of the game, and the information is not available without creating some sort of analysis like I am attempting to create here.

This final video is an example of me collecting data from the simulated events. In the video I collect two different samples. Clearly the collection method is not ideal, and the analysis will be near impossible I assume. So how can I do this better?

Video #4, Data collection

If the video is hard to see, essentially I am just running the simulation and recording the location of POIs with the hope to analyze the data down the road.

Thanks to everyone in advance. I know this is very wordy, but I'm hoping the videos will prove fruitful.

 

[miraclewhip]

I should note that in video #4, the collection of data is coming from the Russian starting location (Northern), and the United States starting location (Southern), and building sequentially towards the middle of the map. Assuming the fourth POI from each team's starting location is neutral, each team has a total of 4 POI's.