VBA Code Simulations

[franb123]

Hello everyone

I'd like some help please, with some code in my Ratings spreaadsheet.

The essence of what I'm trying to do is this:

I have a set of Player Performance Ratings (Column AP) for an individual event (row). I also have a running average of those ratings in Column AQ from the earliest date (Column AL) to the current date.

Other fields to note are:

"Player Name" - Column D
"Opposing Team" - Column F
"Position" - Column AO
"Current Players Average.." Column AQ

Column AX is part of what I'm wanting to achive - Firstly, for the "Player" in question, it looks for the "Opposing Team" and also looks for a player in the opposing team from the same "Position" and then uses the random function to select an average RATING for A PLAYER FROM THE OPPOSING TEAM IN THE SAME POSITION AS THE PLAYER IN QUESTION from ratings determined previously. So AX8559 Rating would be from data from row 2 to row 8559, Rating AX8560 would be from row 2 to row 8660 etc.

The array formula in Column AX:

=IF(AO8559="","",INDIRECT("AQ"&LARGE(IF($F$2:F8559=F8559,IF($AO$2:AO8559=AO8559,ROW($AQ$2:AQ8559),0),0),RANDBETWEEN(1,COUNTIFS($F$2:F8559,F8559,$AO$2:AO8559,AO8559)))))

So, I could try and copy that array formula across 1000 columns to determine 1000 random average scores of opposing players average ratings in the same position - but as you could imagine, quite the tedious exercise!

Once 1000 random average scores of opposing players average ratings in the same position, I'd like to compare each random rating to the value in AQ of the current players average and get the probability (out of 1000 simulations) that the current players score exceeds the randowm sample score. This probability would be returned in Column AZ.

So this process would need to occur for every players match (row).

I don't want to reference the data later from a data table.

The probability values stored in Column AY would then be used to Rate each individual Player in another seperate calculation.

So would anyone please be able to help with VBA code that could help me achieve this?

I'm not even sure if it's possible, but I AM sure that someone in this forum would know! Thanks in advance...

I've deleted a few rows to ensure I could upload, but it does not compromise the essence of what I require.