Statistical
Benford first-digit probabilities and significant-digit extraction
Extract significant digits from numeric amounts and compare a sample count with a theoretical Benford probability; deviations alone do not establish fraud.
A descriptive digit check, not a fraud verdict
Benford probabilities are a potential model for some naturally generated positive magnitudes. Sequential invoice identifiers, constrained prices, assigned codes and small samples need not follow that distribution. Even genuine amounts spanning several orders of magnitude are not guaranteed to fit it. Six records here only demonstrate spreadsheet arithmetic.
Use transaction amounts, not invoice IDs. Negative amounts are analyzed by magnitude under this exercise’s explicit policy; zero has no leading significant digit. The helpers reject nonnumeric values and magnitudes outside1e-9 through1e12. Scaling by a power of ten handles values below1 without treating a decimal point or leading zero as a digit.
A departure from expected frequencies prompts investigation of data collection and model suitability, not an accusation. This sheet does not calculate a significance test, confidence interval or fraud score.
| Digit | Theoretical probability | Extraction | Interpretation |
|---|---|---|---|
| Digit 1 | 30.10% (0.3010) | Significant digits of absolute magnitude | Model expectation, not a fraud signal |
| Digit 2 | 17.61% (0.1761) | Significant digits of absolute magnitude | Model expectation, not a fraud signal |
| Digit 5 | 7.92% (0.0792) | Significant digits of absolute magnitude | Model expectation, not a fraud signal |
| Digit 9 | 4.58% (0.0458) | Significant digits of absolute magnitude | Model expectation, not a fraud signal |
Benford's Law expected first-digit probability LOG10(1 + 1 / d)
Evaluates exact theoretical frequency for any leading digit d = 1..9 in E2.
=IF(COUNT(E2)<>1,"Invalid inputs",IF(OR(E2<1,E2>9,E2<>INT(E2)),"Invalid inputs",ROUND(LOG10(1 + 1 / E2), 4)))Count transactions beginning with digit E2
COUNTIF counts the accepted helper digits. If reporting a share, divide by COUNT(C2:C7), not by all source amounts, and report excluded records. The scaling helper rounds its significand to12 decimal places; extremely close power-of-ten boundaries follow that precision policy.
=IF(COUNT(E2)<>1,"Invalid inputs",IF(OR(E2<1,E2>9,E2<>INT(E2)),"Invalid inputs",COUNTIF(C2:C7, E2)))Second significant digit of B4
This formula extracts the second significant digit, including zero as a valid second digit. The first-digit probability formula is not a second-digit probability model.
=IF(ISNUMBER(B4),IF(OR(ABS(B4)<1E-9,ABS(B4)>1E12),"Excluded",LET(magnitude,ABS(B4),significand,ROUND(magnitude/10^INT(LOG10(magnitude)),12),normalized,IF(significand>=10,1,significand),INT(MOD(ROUND(normalized*10,10),10)))),"Excluded")Try the example
Use the original values or change them to test your understanding.