Cheat sheet
Every formula on one page. Print it the night before.
Linear programming
- The model
- Maximise or minimise one linear number, subject to linear limits.Max Z = c₁x₁ + c₂x₂ s.t. a₁x₁ + a₂x₂ ≤ b ; x ≥ 0
- Corner rule
- The best plan sits at a corner of the feasible region.
- Binding
- The resource is fully used, so its slack is 0.
- Shadow price
- Extra Z per extra unit of that resource, inside the allowed range.
- Engine
- Simplex LP, with the objective built from SUMPRODUCT.
Yes/no logic
- Not both
- At most one of the two is chosen.x1 + x2 ≤ 1
- Exactly one
- One and only one is chosen.x1 + x2 = 1
- k of n
- At least k of the options are chosen.Σx ≥ k
- 4 only if 3
- 4 cannot be chosen unless 3 is.x4 ≤ x3
- If 1 or 3 then 5
- 5 must come along with either.x5 ≥ x1 ; x5 ≥ x3
- Fixed charge
- Pay the setup cost the moment any amount is made.x ≤ M·y
- LP relaxation
- Drop the integer rule: for a max problem its answer is a ceiling.
Solver engines
- Simplex LP
- Everything linear: SUMPRODUCT objectives and linear constraints.
- GRG Nonlinear
- Smooth curves: squares, roots, exponentials. Bass and MDS fits.
- Evolutionary
- IF / MIN / VLOOKUP models: WSJ weights, city anchors. Set bounds on every variable.
- Reports
- Answer report gives slack; Sensitivity report gives shadow prices and ranges.
Distance
- z-score
- How many standard deviations from the mean.z = (x − mean) / sd
- Squared distance
- Add the squared gaps across every column.d² = Σ(a − b)²
- Jaccard
- Items in both baskets over items in either.J = both / either
- Order of work
- Standardise first, then measure distance. Otherwise the biggest column wins.
KNN
- The method
- Find the k closest rows, then take the majority class.
- Choosing k
- Odd, so votes cannot tie. Small k overfits, big k underfits.
- Scaling
- Standardise every input column before measuring distance.
Confusion matrix
- Accuracy
- Share of all rows called correctly.(TP + TN) / n
- Precision
- Of the rows called yes, how many were yes.TP / (TP + FP)
- Recall
- Of the real yes rows, how many were caught.TP / (TP + FN)
- Specificity
- Of the real no rows, how many were left alone.TN / (TN + FP)
- Cutoff
- Lower it and recall rises while specificity falls.
- Rare cases
- Accuracy looks fine even when every rare case is missed. Read recall.
Bayes
- Bayes' rule
- Turn P(B|A) into P(A|B).P(A|B) = P(B|A)·P(A) / P(B)
- Naive Bayes
- Multiply the clue probabilities inside each class, then normalise.P(C) · Π P(xᵢ | C)
- Naive
- Clues are treated as independent within a class.
- Laplace smoothing
- Add 1 to every count so a single zero cannot wipe out a class.
- Bayesian network
- A directed acyclic graph with a probability table at each node.
Clustering
- SSE
- Total squared distance from each point to its own centre.SSE = Σ (distance to own centre)²
- k-means
- Assign to nearest centre, move centres, repeat until nothing moves.
- Elbow
- Plot SSE against k and take the bend.
- Linkage
- Single chains, complete stays compact, average is in between, Ward's adds the least SSE.
Association rules
- Support
- Share of baskets holding both.#(A∧B) / N
- Confidence
- Of the baskets with A, share that also have B.#(A∧B) / #A
- Lift
- Confidence over the base rate of B.Lift = Confidence / Support(B)
- Reading lift
- Above 1 they sell together, 1 is independence, below 1 they substitute.
- Apriori
- Find frequent itemsets first, then turn them into rules.
Recommenders
- Families
- Popularity, content-based, collaborative (user or item), hybrid.
- Cosine similarity
- Angle between two rating rows, 0 to 1.cos = Σ(a·b) / (√Σa² · √Σb²)
- Prediction
- Similarity-weighted average of what the similar people gave.
- Cold start
- A brand-new user or item has no history, so nothing can be matched.
- Sparsity
- Most of the ratings grid is empty, so overlaps are thin.
PCA & maps
- PC1
- The direction with the most spread; PC2 sits at right angles to it.
- How many to keep
- Eigenvalue above 1, or enough components for 70–80% cumulative variance.
- Perceptual map
- MDS on similarity rankings: close means seen as similar.
- Naming axes
- Read what sits at each end and name the axis yourself.
Charts
- Change over time
- Line chart, one line per series.
- Compare categories
- Bar chart, sorted.
- Two numbers together
- Scatter chart.
- Share of a whole
- Stacked bar, or a small pie with few slices.
- Spread
- Histogram.
- Data-ink
- Every drop of ink should carry information. Drop the rest.
Excel recipes
- Standardise a column
- z-score against the column mean and standard deviation.=STANDARDIZE(B6, B$2, B$3)
- Squared distance between rows
- One function, no helper columns.=SUMXMY2($D$57:$G$57, D6:G6)
- kth smallest distance
- Pull the k nearest out of the distance column.=SMALL($H$6:$H$55, 1)
- Find which row it was
- Position of that distance inside the column.=MATCH(J6, $H$6:$H$55, 0)
- Read the neighbour's class
- Exact match lookup, FALSE every time.=VLOOKUP(K6, $A$6:$I$55, 9, FALSE)
- Confusion matrix counts
- Count rows matching actual and predicted together.=COUNTIFS($I$6:$I$55, 1, $L$6:$L$55, 1)
- Pick a cell out of a block
- Row and column numbers into a matrix.=INDEX($B$3:$K$12, MATCH(A16, $A$3:$A$12, 0), MATCH(B16, $B$2:$K$2, 0))
- Weighted total
- Multiply two ranges and add, in one step.=SUMPRODUCT($B$2:$D$2, B6:D6)
- Regression coefficients
- LINEST returns them right to left: slope of the last x comes first.=LINEST(Y_range, X_range, TRUE, TRUE)
- Naive Bayes CPT cell
- One conditional probability per value and class.=COUNTIFS($A$6:$A$1029, K10, $E$6:$E$1029, 1)/COUNTIF($E$6:$E$1029, 1)
- Count-both for a lift matrix
- INDEX picks a whole column, so one formula fills the grid.=IF($M9=O$7, "", COUNTIFS(INDEX($C$9:$H$2936, 0, $M9), 1, INDEX($C$9:$H$2936, 0, O$7), 1))
- Cosine similarity
- Co-rated films only; mask the blanks.=SUMPRODUCT(a, b)/(SQRT(SUMPRODUCT(a, a))*SQRT(SUMPRODUCT(b, b)))
- Random demand
- One draw from a normal distribution, for Monte Carlo trials.=NORM.INV(RAND(), mean, sd)
- Lock a reference
- Cycle the $ signs while the reference is selected: F4 (Windows) / ⌘T (Mac).B6 → $B$6 → B$6 → $B6