Calcuating probabilities

[.... deleted ....]

You get better help on your question if you add a small excel file, without confidential information.

Please also add manualy the expected result in your file.

1. Scroll down to the window below your post Additional Options
2. In the frame Attach Files you will see the button Manage Attachments
3. Click the button.
4. A new window will open titled Manage Attachments - Excel Forum.
5. Click the Browse... button to locate your file for uploading.
6. This will open a new window File Upload.
7. Once you have located the file to upload click the Open button. This window will close.
8. You are now back in the Manage Attachments - Excel Forum window.
9. Click the Upload button and wait until the file has uploaded.
10. Close the window and then click Submit.

Originally Posted by jnel1610

I want to calculate the probability of how often this slot machine will hit 1200+.
[....]
10/25/15 3280 11:12 PM
10/25/15 4740 11:01 PM 0:11
10/25/15 2640 6:58 PM 4:03
10/25/15 1600 6:06 PM 0:52
10/25/15 1250 2:08 AM 15:58
10/24/15 1508 7:04 PM 16:56
10/24/15 1218 4:57 AM 14:07
10/24/15 1500 12:05 AM 4:52
10/23/15 25615 11:03 PM 22:58
10/23/15 1203 5:57 PM 5:06
10/23/15 3900 5:33 PM 0:24

The answer is more complicated than I realized. Presumably, your example has only the date and time of 1200+ payoffs. In order to estimate the probability, we need a count of the number of plays [errata] in between. (I am assuming you have a lot more data.)

[Errata] But in real-life, more than just the number of payoffs and plays. Too complicated to explain in a sound-bite like this. Read the "slot machine" wikipage.

In the meantime, I notice that your delta time calculations (last column) are incorrect in a couple rows, highlighted in red.

I suspect you calculated the time difference using only the third column; for example, =C1-C2. You discovered that causes an Excel error in a couple rows (cannot format negative time). So you changed those formulas to form =C2-C1, apparently.

That is incorrect. Assuming your data is always contiguous days, as in your example, the correct time-only formula is the form =MOD(C1-C2,1) formatted as Custom h:mm.

However, allowing for gaps of more than 24 hours in the data (just in case), a better formula is the form:

=A1+C1 - (A2+C2)

In other words, combine each date with corresponding time, then subtract the two.

[.... deleted ....]

I saw your file.

Make just a small one (file) and post the expected result in your file (as requested).

Mark the related cells with the same color for more explaination.

I find that I must first understand the math behind a problem before I can effectively program it into Excel. As joeu2004 indicates, it appears that the statistics and probability behind slot machines can get pretty involved -- probably more than can be expected from an internet forum post.

If you know the equations and algorithms you want to use for this analysis and can explain them to us, we can likely help you program those into Excel. If you are not sure how to perform this analysis, then I would suggest you research out the probability theory behind slot machines until you find the desired equations.

As joeu2004 indicated, one simple estimate of probability is "number of successes"/"number of trials" (now we are starting to sound like binomial statistics). If we had a complete list of trials, this would normally be a simple COUNTIF()/COUNT() type function in Excel. This approach will not work until we figure out how many total trials we have.

It is more of a rate than a probability, but we could compute "number of successes"/"day" (or other unit of time). In Excel, this might be calculated as COUNT(B:B)/("last day"-"first day"). I'm not sure what meaning this would have when some days have no successes and other days have a dozen successes.