CODEWITHSUNDEEP
exceldistributionstatisticsbenfords-lawvba
socksxbirkenstocks
socksxbirkenstocks

Reputation: 21

Benford's Law in Excel

I have a large data set, and I was hoping to test it against Benford's Law.

So far, I have been extracting the first digit, and creating a chart based on that

=LEFT(A1,1)*1

I multiply the formula by 1 because LEFT turns the digit into a string.

I was hoping to automate this procedure. Is there a way to do this with VBA or a simpler/more elegant formula for this?

Upvotes: -1

Views: 2781

Answers (2)

chuff
chuff

Reputation: 5866

Here's a thought.

Let's assume your data is in cells A1:A100 and your workbook is named "Benford".

  • Using the Name Manager on the Insert tab of the ribbon, create a new named range, say, "digits", defined as =VALUE(LEFT(A1:100,1)). The Name Manager will automatically treat the result of this equation as an array.

  • Insert a chart into the workbook without selecting a data range. The chart will be totally blank.

  • Select the chart and then in the Chart Tools tab, press the 'Select Data' button.

  • When the 'Select Data Source' dialog box opens up, again do not specify a data range, but instead click on 'Add Series'.

  • In the 'Edit Series' box that comes up, set the 'Series value' to =Benford!digits. The workbook reference is important: Excel won't accept the bare defined name here.

  • If initially the data does not show on the chart, just manually recalculate the sheet with the F9 key.

You can then add titles, axis labels, etc., as needed.

This can be automated via VBA; whether that makes sense depends on your needs.

Upvotes: 0

B-Rell
B-Rell

Reputation: 636

I'm sure you can use Google as well as anyone. I searched for "excel formula for benford's law". I'm pretty sure the first result is what you are looking for.

Here's the link: http://www.isaca.org/Journal/Past-Issues/2010/Volume-1/Pages/Using-Spreadsheets-and-Benford-s-Law-to-Test-Accounting-Data1.aspx

It covers testing lead digits, how to use benford's law and some useful do's and don'ts. This should get you most of the way.

FYI - the ISACA is an auditing standards organization. Their content is designed to be useful in professional applications.

Good luck!

Upvotes: -1

Related Questions