Showing Activity for Most Recent Days

I’m working on a dashboard for a company that sell tee shirts to high school sports programs.  They get their data from an online database which includes day and time of order as well as order amount. But, you could apply the same methodology to any data that is updated often, whether you are copying and pasting into Excel, importing from or linking to an Access database, or connecting to an ODBC data source. (Of course I didn’t use the company’s actual data; I used a cool trick to generate random dates and times for events for my sample data.)

At any given point, management of Acme SportsTees, Inc. would like to see the last 10 days of sales activity.  The trick to dashboards; and in my opinion, any process or task you are trying to automate; is removing from the process as much human involvement (thinking, typing, more thinking) as possible.  So, if you are able to connect directly to the data source, or at least reduce the actions required by the administrator to just copying and pasting, you will significantly reduce the opportunity for human error and a lot of headaches.

Once you’ve created a process for limiting errors, the first step is to find the most recent date each time the dashboard is ran or opened.  A manual process would involve searching (eyeballing) the data for the most recent date or just using a formula such as TODAY() or NOW().  Instead and more efficiently, we’ll search the whole column of order dates/times for the largest, which is the most recent.  It’s obvious, I’m sure, but Excel’s MAX() function does the trick.  If the date for our data is stored in column B, the formula is simply:

MAX(B:B).

Okay, since our data includes several orders (we hope) for each day, the most recent date retrieved will likely not be a whole number.  For example, instead of 40600, which is the serial number for today, “2/26/11”, the number retrieved might be 40600.45, which is “2/26/2011  10:48:00 AM”.   Because we will be working with whole days for totals, we’ll simply round the number retrieved down to the nearest whole number.  Instead of Max(B:B), then, we’ll use:

ROUNDDOWN(MAX(B:B),0).

Okay, now that we have the most recent date for which we have activity, we can work backwards, by subtracting one from each previous (above, in my example) cell.  So if our most recent date is retrieved and placed in cell F20, cell F21 will contain:

F20-1

To get the most recent 10 days, you’d repeat (copy or drag) this formula for eight more rows (for a total of 10).

Okay, now the fun stuff!

To get the total is a little bit tricky.  One way to do it would be to add a column where you reduce the date and time of the date/time of order field to just the day, or a whole number.  Using the same ROUNDDOWN function we used to get the first date in our dashboard summary, we could add a column on the datasheet:

=ROUNDDOWN(B2,0)

We could then fill or copy down, converting all values in column B to whole numbers.

But, of course, this adds one more level of human interaction.  And one more opportunity for error.  So we want to do the same thing more efficiently and automatically.  To do so, we will use SUMPRODUCT, one of the least understood but most versatile and powerful of Excel’s functions.  Using SUMPRODUCT, we can do in one step what we’d normally do in several.  To get the total sales for each day in our range (10 days in my example), we simply use:

SUMPRODUCT((ROUNDDOWN($B$2:$B$10000,0)=F20)*$C$2:$C$10000)

Unlike SUM(), or even SUMIF(), SUMPRODUCT() has a difficult time working with whole columns and headers.  To make the function work, then, we have to specifiy the range.  To be able to accomodate data as it grows larger, we make the range large.  I use 10,000 as the max, but you can make it larger (or smaller) dependent on your needs and expectations.  To help you understand what the function is doing, please see the attached file (link below).  Basically, we are checking for the rounded (down) value for all of the items in Column B ( the date/time of the sale) and comparing it to the date for which we are looking.  For all matches, it then sums the sale amount (Column C). And it’s all done in one formula, automatically.

Dashboard for Recent Days Sales