fantasy basketball stat simulation


New Member
Nov 15, 2005
hello all. i've been trying to find out how to run a simulation using basketball statistics on excel -- i've been told by a few people that what i want to do can likely be done but none have been able to go into any further detail. i know exactly what i want, but have no idea how to do it. i came across this site and hope that someone here can help me.

what i have:
- the top 120 players in fbb from the 04-05 season (all are one of the following positions -- PG, SG, SF, PF, C)
- values for each of those players in each of the 9 standard scoring categories (field goal %, free throw %, 3-pointers made, points, rebounds, assists, steals, blocks, turnovers)

the setup:
- 12 teams, 10 players each
- 10 standard positions for each team (PG, SG, G, SF, PF, F, C, C, UTIL, UTIL) G can be filled by a player with either PG or SG eligibility, F with either SF or PF, and UTIL by any player
- previously mentioned 9 scoring categories

what i'd like to do:
- randomize the players on each team
- team values for each of the 9 statistical categories are calculated by adding the players' individual values
- teams are given points for each category based on the sum of the values -- as an example, the team with the highest rebound value sum gets 12 points, the least gets 1
- team category points are added together to get a total score, and teams are given a standing (1-12, first to last) based on this total score (highest to lowest)
- the individual category point values and overall team rankings are assigned to each player and calculated each time the teams are randomized -- a player will have an average for each stat category point and an overall team standing average, based on the teams they were part of

i'd have to assume that the following are huge issues:
- the teams need to be randomized
- any PG or SG can be used to fill the G position each time, any SF or PF can be used for the F position, and any player can be used for the UTIL spots


this article is where the idea for this came about. there is a bit more detail from the author there on his methods. he is a PhD of Economics and was likely using something a bit more advanced than microsoft excel when he ran his 40,000 sims, but i'm trying to make due with what i have

i actually have no idea if this is possible, but since no one has told me 'no' as of yet, i'm still holding out hope. i can go into more detail if anyone needs it -- thanks for checking out my post

Some videos you may like

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.

PA HS Teacher

Well-known Member
Jul 17, 2004
a player will have an average for each stat category point and an overall team standing average, based on the teams they were part of

I think I understand that each player will have an average rank in each category. (based on all of the fantasy teams, that player has been placed with thus far)

Do their individual Category statistics change? If a player has a 70% Free Throw Average, does that 70% change during the course of your simulations?


New Member
Nov 15, 2005
no -- the player's individual stat values will never change. what will change are the team's values in each category based on the group of players that end up being randomly placed together. a little more detail about that, as for % categories i don't use the numerical % itself, but rather a value based on the % and weighted by the player's attempts relative to every other player in the league:

mark blount made 71.34% of his free throws last season. his FT% value was (-0.2725)* -- he made below the league average (75.61%) so the value is a negative, but had a relatively low number of attempts (164) so it's a small number. his FT% value would remain (-0.2725) for every simulation, but what would change was his teams FT% value. if the other 9 player's values he was placed together with came to (5.0000), he would drop the team value to (4.7275). if it happened to be (-1.0000) he would drop it to (-1.2725). the individual player values never change -- the team values do every time due to the randomization of the teams

what i want to establish is what mark blount's 'average rank' is in FT% based on the simulations (and all other categories as well of course) -- i'm trying to establish this by seeing what value his average team would have and where that average value would place that team in the standings

*in case you are wondering -- the formula i use to arrive at that value is:

((his FT% - league average FT%) / stdev of all player FT%'s) * (his attempts / average league player's attempts)

Watch MrExcel Video

Forum statistics

Latest member