Benford's law - Generalization to digits beyond the first -- Is there a VBA solution?
Results 1 to 5 of 5

Thread: Benford's law - Generalization to digits beyond the first -- Is there a VBA solution?

  1. #1
    Board Regular
    Join Date
    Nov 2014
    Location
    Mumbai, Maharashtra, INDIA
    Posts
    142
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Benford's law - Generalization to digits beyond the first -- Is there a VBA solution?

    I am looking for a VBA led output for 2nd, 3rd, 4th, etc, (Generalization to digits beyond the first).

    The refrence link is : Benford's law - Wikipedia

    Essentially the formula to code is below the text "And the probability that d (d = 0, 1, ..., 9) is encountered as the n-th (n > 1) digit is"

    Thanks

  2. #2
    Board Regular igold's Avatar
    Join Date
    Jul 2014
    Location
    Delray Beach, FL, USA
    Posts
    2,332
    Post Thanks / Like
    Mentioned
    17 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Benford's law - Generalization to digits beyond the first -- Is there a VBA solution?

    See if you can glean anything from here:

    Turn Excel Into a Financial Sleuth
    ​igold

    I'm a drinker with a coding problem...

    All code is written with Excel 2010 - Please test all code on a backup copy of your data.


  3. #3
    MrExcel MVP shg's Avatar
    Join Date
    May 2008
    Location
    The Great State of Texas
    Posts
    21,603
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    14 Thread(s)

    Default Re: Benford's law - Generalization to digits beyond the first -- Is there a VBA solution?

    To replicate the table,

    A
    B
    C
    D
    E
    F
    G
    H
    I
    J
    K
    L
    5
    Pos\Digit
    0
    1
    2
    3
    4
    5
    6
    7
    8
    9
    6
    1
    n/a
    30.10%
    17.61%
    12.49%
    9.69%
    7.92%
    6.69%
    5.80%
    5.12%
    4.58%
    C6: =LOG(1 + 1/C$5)
    7
    2
    11.97%
    11.39%
    10.88%
    10.43%
    10.03%
    9.67%
    9.34%
    9.04%
    8.76%
    8.50%
    B7: =SUMPRODUCT(LOG(1 + 1/(10*ROW(INDIRECT(10^($A7-2) & ":" & 10^($A7-1) - 1)) + B$5)))
    8
    3
    10.18%
    10.14%
    10.10%
    10.06%
    10.02%
    9.98%
    9.94%
    9.90%
    9.86%
    9.83%
    9
    4
    10.02%
    10.01%
    10.01%
    10.01%
    10.00%
    10.00%
    9.99%
    9.99%
    9.99%
    9.98%
    10
    5
    10.00%
    10.00%
    10.00%
    10.00%
    10.00%
    10.00%
    10.00%
    10.00%
    10.00%
    10.00%
    Last edited by shg; Aug 29th, 2017 at 08:23 PM.

  4. #4
    Board Regular
    Join Date
    Nov 2014
    Location
    Mumbai, Maharashtra, INDIA
    Posts
    142
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Benford's law - Generalization to digits beyond the first -- Is there a VBA solution?

    @shg, Thanks. This meets the requirement.

  5. #5
    MrExcel MVP shg's Avatar
    Join Date
    May 2008
    Location
    The Great State of Texas
    Posts
    21,603
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    14 Thread(s)

    Default Re: Benford's law - Generalization to digits beyond the first -- Is there a VBA solution?

    You're welcome.

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •