Problem Statement

Benford's law, also called the first-digit law, refers to the frequency distribution of digits in many (but not all) real-life sources of data. In this distribution, the number 1 occurs as the first digit about 30% of the time, while larger numbers occur in that position less frequently: 9 as the first digit less than 5% of the time. This distribution of first digits is the same as the widths of gridlines on a logarithmic scale. Benford's law also concerns the expected distribution for digits beyond the first, which approach a uniform distribution. This result has been found to apply to a wide variety of data sets, including electricity bills, street addresses, stock prices, population numbers, death rates, lengths of rivers, physical and mathematical constants, and processes described by power laws (which are very common in nature). It tends to be most accurate when values are distributed across multiple orders of magnitude. A set of numbers is said to satisfy Benford's law if the leading digit

d

{\displaystyle d}

(

d ∈ { 1 , … , 9 }

{\displaystyle d\in {1,\ldots ,9}}

) occurs with probability For this task, write (a) routine(s) to calculate the distribution of first significant (non-zero) digits in a collection of numbers, then display the actual vs. expected distribution in the way most convenient for your language (table / graph / histogram / whatever). Use the first 1000 numbers from the Fibonacci sequence as your data set. No need to show how the Fibonacci numbers are obtained. You can generate them or load them from a file; whichever is easiest. Display your actual vs expected distribution.

For extra credit: Show the distribution for one other set of numbers from a page on Wikipedia. State which Wikipedia page it can be obtained from and what the set enumerates. Again, no need to display the actual list of numbers or the code to load them.

Let's start with the solution:

Sub BenfordLaw()

Dim BenResult(1 To 9) As Long

BENref = "30,1%|17,6%|12,5%|9,7%|7,9%|6,7%|5,8%|5,1%|4,6%"

For Each c In Selection.Cells
  If InStr(1, "-0123456789", Left(c, 1)) > 0 Then
    For i = 1 To 9
      If CInt(Left(Abs(c), 1)) = i Then BenResult(i) = BenResult(i) + 1: Exit For
    Next
  End If
Next
Total= Application.Sum(BenResult)
biggest= Len(CStr(BenResult(1)))

txt = "#   |   Values    |     Real     |  Expected   " & vbCrLf
For i = 1 To 9
  If BenResult(i) > 0 Then
    txt = txt & "#" & i & " | " & vbTab
    txt = txt & String((biggest - Len(CStr(BenResult(i)))) * 2, " ") & Format(BenResult(i), "0") & " | " & vbTab
    txt = txt & String((Len(CStr(Format(BenResult(1) / Total, "##0.0%"))) - Len(CStr(Format(BenResult(i) / Total, "##0.0%")))) * 2, " ") & Format(BenResult(i) / Total, "##0.0%") & " | " & vbTab
    txt = txt & Format(Split(BENref, "|")(i - 1), "     ##0.0%") & vbCrLf
  End If
Next

MsgBox txt, vbOKOnly, "Finish"

End Sub

}