Creating a Table for showing Intervals between random numbers

Hi Friends,

I would like to create a table that provides the intervals between random numbers as they appear in a list.

For example, let's use dice. If I have a single dice which has 6 numbers and I roll that dice for say 100 rolls, I'm looking to show the number of times each number of the dice appeared AND the distance between the appearance of each number.

It would look something like this:

List of Random Numbers between 1 and 6	Numbers on Dice	Number of times appeared	First appearance at roll	Second appearance at roll	Third appearance, etc.. at roll
2	1	3	3	4	9
6	2	1	1
1	3	0
1	4	5	7	8	14
4	5	1	8
6	6	2	2	6
4
5
1

Any assistance is greatly appreciated. Thanks so much!!!

Wally

so, if you assume your values are in A2:A1001 (2 being in A2) with 1 to 6 in B2:B7 respectively then:

Formula:

Please Login or Register  to view this content.

the above calcs would replicate your table above {edit: I think your table is incorrect for #4}

given dimensions if the items (1 to 6) are likely to be less than the no. of times they can occur it might make sense, visually, to transpose your result table (i.e. 1 to 6 as headers) ?

Thanks you so much!!

This is exactly what I was looking for. I'm very appreciative. While visually it makes sense to use the 1 to 6 as headers, for this purpose, the display horizontally is what I was looking for.

Again, Thank you!!!