I moved the salaries off to the side (to incorporate some additions described below) and added a total cost column in CB using:
=IF($BU2="","",IF(SUMPRODUCT(--($CG$2:$CG$27=$BV2:$CA2)*$CH$2:$CH$27)<=$CE$2,SUMPRODUCT(--($CG$2:$CG$27=$BV2:$CA2)*$CH$2:$CH$27),"OVER BUDGET"))
It should spit back the total cost of that row's grouping or "OVER BUDGET" if they're over.
I don't think there is an issue with BR:BS. I think with 14 fighters and all of the available score permutations, you're just not seeing any repeated sets of top six fighters in the same order. Each set shows up in an equal percentage of sims because each set shows up only once. If you add many more sims, you'll start to see variations in BR:BS.
The problem I ran into is that adding thousands of sims is incredibly slow, so I wrote a macro to ease the process. It can be triggered with the "Add Simulations" button. Basically, it takes whatever formulas are in the last row (even if there's only one row), and fills those formulas down for as many rows/sims as you choose. Along the way, it copies/pastesvalues to ease the calculation time. It is designed to always leave the formulas in the last row so that you can drag them down further or run the procedure again to add more sims. It basically copy/pastesvalues 1000 rows at a time until it hits your selected total. For me, it starts to slow but still works when adding around 10000 sims at a time. It's very fast with only a few thousand.
In the code below, I've noted that the DoEvents line is optional. Keeping it makes you a bit less likely to slip into "not responding" mode when running the procedure on big numbers, but it may run a touch more slowly. Leaving it active also means that the cursor will 'flash' while the program is running, which can help you track the program's process - each flash indicates 1000 rows/sims added.
I also wrote a "reset sims" button that returns the formulas to row2 and deletes the other sim lines. Like the add sims procedure, it handles a few thousand quickly, but lags a bit with 7000+.
You may notice that I created some named ranges for BL:BS to handle the variable number of fights/sims. All told, the sheet should be a bit easier to experiment with now. Give it a look!
Bookmarks