BENFORD'S LAW - Saurabh Gopal Agrawal 2013038

Saurabh Gopal Agrawal

2013038

Group - 6

BENFORD'S LAW:

                               

Benford’s Law is one of those mathematical laws that seems to defy common sense but works for most naturally occurring number sets.

It says that in most groups of naturally occurring numbers, the leading digit 1 will occur more than 2 as a leading digit and so on down to numbers starting with 9 occurring least often.

BENFORD’S LAW IN EXCEL:

Firstly, create a column of leading digits only using the LEFT() function. Despite what Excel documentation sometimes says, LEFT() works with numbers (not just text) and will ignore any currency symbol if defined in the cell formatting. For Benford’s Law use LEFT(<value>,1)

Then use COUNTIF to count the instances of each leading digit from 1 to 9 e.g. COUNTIF(<leading digit>,”1”) – remember that LEFT() returns a string/text value so the COUNTIF comparison is “1” not the digit 1 .

For a more exact set of comparison values use the formula =LOG10(1/<leading digit>+1).

 EXAMPLE WORKSHEET:

The leading digit values are shown in a separate column.

USE WITH CARE:

In the real world, Benford’s Law is often applied to check if data has been tampered with or outright made up. If someone has faked data or tinkered with the numbers that will affect the Benford’s Law distribution. This makes it a useful tool for auditors or others checking for fraudulent data.

But Benford’s Law needs to be used with care because not all data sets are distributed evenly or widely enough.

An example that would NOT work with Benford’s Law is a list of petty cash receipts, because the petty cash limit might be say $40 so most of the amounts will have leading digits between 1 and 3 only and probably many just under the $40 limit. Similarly a list of large check approvals, because of the arbitrary definition of ‘large’ in any organization. However if you had a list of all outgoings from small to large, Benford’s Law might apply.

A series of adult human heights or weights also don’t obey Benford’s Law because most people are within a narrow range of heights or weights (i.e. you won’t have adults weighing 10lb or 20kg). Telephone numbers won’t work because there are arbitrary prefixes or blocks of numbers issues. On the other hand a large list of street numbers from an address list probably will obey Benford’s Law.

In short, Benford’s Law is a useful tool for checking data, but it needs to be used with care and understanding of the data source. Large scale numbers without arbitrary limits work best. A history of Benford’s Law is littered with people who falsely claim fraud based on a mistaken understanding of the data source.

 Source: http://news.office-watch.com/t/n.aspx?a=1748