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. Read more of this post

Generating Random Times for Events

Generating Random Times for EventsIf you are ever in the need for sample data where the events (sales, support calls, etc.) occur several times throughout any given day, here is a neat little trick to accomplish that. One might think the RANDBETWEEN function, which generates a random integer between two numbers that you feed the function, would do the trick. Well, because RANDBETWEEN only returns whole numbers, the time of the day is always going to be at midnight.

To add increments that are less than one day, you would need to add numbers less than one (i.e. not whole numbers). For example adding 0.25 would take the time to 6 am; 0.5 to noon, and 0.75 6 pm. Luckily, adding a number less than one is easy. The RAND function, which generates a number between 0 and 1, does all of the work you.

To generate a list of random times, then, simply start with whatever date you wish in the first row of your data. All is needed in each cell afterward is the addition of Rand(). After adding Rand() to the previous cell, simply copy (or drag) the formula for as many entries as you wish. See attached for example. If, when you’ve finished the copying/dragging, you don’t see enough entries in each day (only 6 am, 2 pm, and 11:30 pm— 3 entries—for example), simply divide Rand() by 2 (or 3 or 4 or 5), to make the increment each time smaller. Pretty straightforward, huh?

Example: Generating Random Times and Dates

6 Workout Tips

In Today’s helping of Men’s Health Tips, they have 6 simple ways to get more out of your workouts.  Check it out!

Pivot Tables.. but first

I’ve been asked by many of you to put together something on Pivot Tables.  In doing so, I found the hardest part was compiling sample data that allowed me to highlight some of the cool features that Pivot Tables have to offer. After searching the web for sample data to use and coming up empty, I decided to go the long route and just start typing.  But as luck would have it, I stumbled upon a slick way or two to generate some useful data.  Attached is that discovery in two parts.  This is my first attempt at a video blog, so please provide any feedback, good or bad.
Part I


Part II

Trend and Growth Functions

Data can be analyzed in a lot of different ways.  Excel has dozens of tools and functions that make high-level statistical analysis not only possible, but in many cases fairly easy to use.

Researchers of all types (medicine, anthropology, sociology, entomology are a few of several fields) use statistics extensively.  Larger corporate companies also spend resources on analyzing, reporting, and making business decisions based on statistical analysis.

Read more of this post