What do Benford's Law, Net Present Values, and arrays have in common? They all can be calculated with the help of Excel's SUMPRODUCT function. As you will see.

As far as that goes, what the heck is Benford's Law, and why should you care?

It's hard to talk about Benford's Law without using words like amazing, or fascinating. So I won't try.

Find some data from nearly any source: A list of trading volumes by company in the stock market, or the population by city in your area, or spending by account from your general ledger. Whatever. 

Now choose one of the numbers at random. There's about a 30% chance that its first digit is "1". There's about an 18% chance that the fist digit is "2". These percentages decline by first digit until there's only a 5% chance that the first digit is "9".

Isn't that amazing? The first digits of virtually any data follow this same pattern.

Now comes the useful part: If people make numbers up, or if they use a random number generator, the numbers won't follow this pattern.

Auditors use this insight to test whether numbers are "real" or whether someone made them up. This same insight can be used to learn whether managers are making up planning numbers or are basing their numbers on real-world facts.

In Use Benford's Law with Excel To Improve Business Planning we show how Excel 2003 users can use SUMPRODUCT and other functions to work with this fascinating characteristic of numbers. Excel 2007 users can use this link.

When SUMPRODUCT first was introduced several versions ago, I read its help file and played with it for a few minutes. But I didn't see much value in it, so I ignored it.

Boy! Was I wrong!

The SUMPRODUCT function can summarize data in just about any way that array formulas can do. But the function is quicker and easier to use...if you do it correctly.

Unfortunately, one aspect of using it correctly is to largely ignore Excel's help file topic about SUMPRODUCT.

The article, Use Excel's SUMPRODUCT To Summarize Worksheet Data, shows Excel 2003 users how to use SUMPRODUCT in a variety of ways. Excel 2007 users can use this link.

When readers ask questions that I think others would find useful, I try to take the time to frame my answer as an article.

Recently a reader asked about the XNPV and XIRR functions, which are new with Excel 2007.

These are really useful functions. Suppose you have a column of cash flows and a column of dates. The dates don't need to be periodic. These functions find the Net Present Value and the Internal Rate of Return for that schedule of cash flows.

My reader wondered if there's a way to do the same thing for Future Values.

It turns out that the SUMPRODUCT function can do exactly what he needed. Better yet, this function allows Excel 2003 users to calculate both the future and present values of the same cash flow schedule.

See Find Future & Present Values From Scheduled Cash Flows In Excel 2003 and Excel 2007.
 

Introducing Stuff We Use

Some of the most widely used anti-virus software on the market makes your fast computer run at a snail's pace. I know, because I used to use that software, and various Microsoft MVPs have confirmed the problem.

Some of the most heavily advertised remote backup services will create real problems for a business. I know, because I've read the fine print and sent their support departments a flurry of questions.

As I find software and services that work well for me, I'll describe them in this new section.

This month, in An Offsite File-Backup Service Designed for Most Businesses, I explain why I chose a backup service designed for businesses, rather than one of the heavily advertised services.

Enough for now.

More later,

Charley