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

#### miraclewhip

##### New Member
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.

### Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.
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.

Replies
2
Views
311
Replies
1
Views
181
Replies
3
Views
352
Replies
0
Views
386
Replies
4
Views
526

1,203,269
Messages
6,054,482
Members
444,727
Latest member
Mayank Sharma

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back