This started out as an idea using Excel and Magic Assistant and eventually turned into 3 5 iterations.
The goal is to create quantity of cards to pull from specific categories, those categories being
- creatures,
- instants,
- sorceries,
- enchantments/artifacts.
How I have my cards sorted, by color (one column, except for 5+, those are in their own column (left most)), then by category, and finally by Mana Point breakdown, ending in either 4+ (non creature) or 5+ creature.
I then capture this information by measuring the width of these categories (when the cards are stacked) in mm on graph paper (makes organization so much easier). I then input these data points into the spreadsheet into the boxed-in areas under input Quantities. This becomes the basis for the weights for the weighted sum algorithms that are derived from the ratios between mana color categories.
The Outputs are represented in the boxed-in areas under the Results Dashboard tab (see example under V5.3)
V5.4
Fixed Mana Allocation, set to # of Cards for Factoring Influence and added a Row()/1000 as a signature to avoid duplicate rank bug
Google Drive Link
V5.3
Bolded Colors
Fixed algorithm for factored importance for mana allocation. Derived off of a proper sumproduct of mana cost * quantity in that mana cost category (vs avg Mana Cost * quantity).
V5.2
V5.1
new features
Assumes no distribution other than from your cards.
Inputs
The boxed area is where you input the measurement of these pre-defined categories which make the frequency bin formula work. This is nothing more than a fancy weighted sum algorithm.
5 Colors
4 Colors
3 Colors
5 Colors (v 5.0)
Magic RNG v5.xlsx
V4.2
Cleaned up data (sorry for the discoloration, screenshot captured my f.lux)
2017-12-17 RNG Magicv4
V4.1
Removed unneeded old data which was initially used to model a complete rebuild. Makes the algorithm easier to read. Meta tab for analysis of card population, meta model, monte carlo, and analysis of monte carlo results
Google Sheet Version
2017-12-17 RNG Magicv4
V4.0
I set some design goals and used measurements of the thickness in CM of each category of my cards and calculated that into the ratio’s for distributing cards in both aspects of Mana Cost and Categories within specific color combinations.
Data collection and post analysis outlier highlights
2017-12-17 RNG Magicv4
V3.0
Does not require any specific Microsoft product to operate (doesn’t rely on any scripting), which means I can host it on google sheets.
V2.0
Learned I could utilize sql in excel from here: http://analystcave.com/excel-using-sql-in-vba-on-excel-data/
Here is the version with SQL
Formatting Cards-All-2017-01-22 
Formatting Cards-All-2017-01-22
I ended up buying 4000 cards for $50 and didn’t feel like manually inputting them all to get cards names, so I made this final version that generates just enough meta data for me to do random pulls, and all I need to do is setup the categories correctly into their respective mana pools (in my case creatures: 1, 2, 3, 4, 5+, non-creatures: 1, 2, 3, 4+).
~V1.0
Here is one of the closer to original sheets I had developed which has a pivot chart screenshott’ed which includes 2500 cards imported from Magic Assistant
This version didn’t use any sql
Formatting Cards
