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
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
Now comes the useful part: If people make numbers up, or if they use a random
number generator, the numbers won't follow this
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.
The Amazingly Useful SUMPRODUCT Function
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
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
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.
NPV and FV From Scheduled Cash
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
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
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.