Maths genius for probability figure adjustment

[Tom_space9]

Hi everyone,
I have used RAND() to a unique set of numbers based on their individual probability. Its easy to derive who gets first. But for all those maths geniuses out there, the 2nd number produced must be adjusted before I can look up the value, which is adjusted based on the probability of 1st. This would also need adjusting for the 3rd number based on the probability of 1st and 2nd being removed. So in effect I am searching for the correct formula for column "N" and "Q".
I know plenty of much smarter people than me are on here so thanks to everyone who can help.

[davsth]

This seems remarkably similar to this post last week
https://www.excelforum.com/excel-for...ml#post5524982

does that work for you?

[Tom_space9]

Thanks for your reply. I had seen that one and looked to modify it. When I downloaded your attachment and ran the macro it generated a list of 3 numbers for X amount of trials. In a number of the generated lists, numbers were repeated. I am looking for something different where it would generate a list of numbers based on probability of each number, though numbers would not be allowed to be repeated in the 3 number string generated.

[davsth]

Yes a line had a 3 where it should have a 2 i should have made options explicit

Please Login or Register  to view this content.

was the offending line

Let me know if that works

[Tom_space9]

Thank you so much. It generated exactly what I desired and I should now be able to expand on it to incorporate multiple events.

[Tom_space9]

Hello again Davsth,

I think the solution you provided was pure brilliance. How could I modify your macro if I wanted it to simulate a number of events. This I am guessing could range from 4 up until say 12 events. I would have the data for each event side by side. I am happy to start the generate numbers beginning cell "AA1". Though I would like to keep the choice for how many trials it produces.
So event 1 would start AA1 and x amount of trials, event 2 would start AD1 and x amount of trials, event 3 would start AG1 and x amount of trials and so on depending on number of events specified.
I really appreciate your help.

Tom

[davsth]

where this approach falls down is if you try and match everything. It finds the first, then keeps producing a second until it is different to the first, then third is different to the second and first etc etc, the more you do the more computationally slower it will be.

As it stands 12 events are impossible without repetition , you could extend the range in the vba to more than 10 rows (its currently 9 in the sample). However as the number of variables approaches the possible number in the sample size, the code will take increasingly long to run. currently 3 out of 10 isnt bad, 7 even runs ok.y vba is also not great and I am sure there would be a way with arrays to make this more computationally efficient

obviously you would extend the lookup range in the spreadsheet and the vba to what you needed it to be, that is easy!

[davsth]

A slight improvement, you enter the range for the probability lookup. You should be able to copy the code for your sheet

[Tom_space9]

Once again thank you so much. I understand what you mean about it keeps producing a 2nd until it is different from the 1st. Though running a big enough sample gives quite an accurate return for the true percentage for 2nd and 3rd. I honestly appreciate your help Davsth.

[davsth]

The percentage will be accurate, if you take a big enough sample. Its just as the sample size to be taken approaches the number of lines in the lookup table, the number of iterations increases markedly

I'm glad I could help