Charts & Data Visualization
Calculation of Pi in Spreadsheets: Unit-Circle Scatter Dartboard & Archimedes Bounds
Approximate Pi visually using a Monte Carlo unit-quarter-circle scatter indicator (X^2 + Y^2 <= 1 -> Pi_hat = 4 * Hits / N) and bound Pi deterministically via Archimedes’ inscribed/circumscribed regular N-gon perimeters.
Compare stochastic O(1 / SQRT(N)) Monte Carlo darts against Archimedes’ deterministic O(1 / N^2) polygons!
Plotting uniform points (X_i, Y_i) inside the unit square [0, 1] x [0, 1] creates the classic Monte Carlo Pi Scatter Chart: points satisfying Euclidean squared radius X_i^2 + Y_i^2 <= 1 fall inside the unit quarter-circle of exact area PI() / 4, so multiplying the hit fraction by 4 yields the unbiased estimator Pi_hat = 4 * (Hits / Total_Points), with standard error SQRT(PI() * (4 - PI()) / N) ~= 1.642 / SQRT(N)!
In contrast, Archimedes of Syracuse (~250 BCE, Measurement of a Circle) trapped PI() strictly between the semi-perimeter of an inscribed regular N-gon L_in = N * SIN(PI() / N) and a circumscribed regular N-gon L_out = N * TAN(PI() / N): already at N = 96 sides, 3.14103 <= PI() <= 3.14271!
| Method & Resolution N | Lower / Stochastic Estimate | Upper Deterministic Bound | Absolute Error vs PI() |
|---|---|---|---|
| Archimedes Hexagon (N = 6) | 6 * SIN(30°) = 3.000000 | 6 * TAN(30°) = 3.464102 | Width = 0.464102 |
| Archimedes 96-gon (N = 96) | 96 * SIN(1.875°) = 3.141032 | 96 * TAN(1.875°) = 3.142715 | Width = 0.001683 |
| Monte Carlo Sample (N = 10,000) | Pi_hat ~= 4 * (Hits / N) | 95% CI +/- 0.0322 | Converges as N^(-0.5) |
Archimedes Inscribed Regular N-gon Lower Bound N * SIN(PI() / N)
Evaluates exact inscribed semi-perimeter N * SIN(PI() / N) for polygon side count B2 >= 3.
=IF(B2 < 3, "Requires Polygon Sides N >= 3", ROUND(B2 * SIN(PI() / B2), 6))Monte Carlo Unit-Quarter-Circle Pi Estimate 4 * Hits / N_trials
Multiplies observed quarter-circle hit count B3 (0 <= B3 <= B4) divided by total uniform trials B4 > 0 by 4.
=IF(OR(B4 <= 0, B3 < 0, B3 > B4), "Invalid Hit or Trial Count", ROUND(4 * (B3 / B4), 4))Try the example
Use the original values or change them to test your understanding.