Put a Stopwatch in your Dashboard
By Loren Abdulezer, CEO, Evolving Technologies Corporation
a Crystal Xcelsius Consulting Partner


Dashboards are natural tools for training and simulation. In a training scenario, it may be helpful to develop some metrics to measure progress. You could, for instance, devise a routine to track the number of correct answers to a series of questions. A first step is to count the number of questions answered correctly. Another revealing metric is how long it takes to answer question. This article shows how to implement such features in Crystal Xcelsius.


In my article last month, I outlined a simple standalone application. I purposely deferred discussing some features. One of these is the measurement of elapsed time and how it could be used within a dashboard. Most of the work in devising a system to measure elapsed time and running average is done at the spreadsheet level. The key ingredients involve the Source Data Component, a Toggle Button, and use of the Excel NOW function.

Generating the Data for the Simulation

Before measuring elapsed time, you need to work with some test data. Try to imagine a simulation where a person is being asked to add a pair of numbers together (see Figure 1).

Figure 1: Format for a Quiz or a Learning Drill

In this situation you are given a pair of numbers to add, and a place to type in the answer, which presumably gets graded. This is simple enough, but there is a subtle challenge. It is fairly obvious that you would not be expecting only one question. You could be given a list of ten questions, each on a separate line. This is wasteful in space, and would be problematic if you had to answer a lot of questions. You might, for instance, be asked to answer as many questions as you can in the space of five minutes.

The best format for this is to populate the same question box with a new question as soon as you finish answering a question. Think of it as an endurance test.

It is relatively easy to generate a random number. You could write an equation in Excel like:


Every time the spreadsheet is recalculated, this formula generates a new number between 0 and 10.

Locate the file called Article13QuizMaker.xls, open it and look at the formulas near the top left of the spreadsheet (see Figure 2).

Figure 2: Generating random test data

If this number (calculated in cell B9), and another number is calculated in C9, they could be combined in a single expression using a formula like in cell D9:

=B9&" + "&C9&" ="

The correct answer for this question can be computed in cell E9, which is simply:


Keeping the data all in one place and time

There's just one problem. When you type in the answer, a new pair of numbers and question is automatically generated. There's no direct or easy way to hold on to the set of numbers. What you have to do is employ a magician's slight of hand.

The objective is to get the data for the current question displayed and your answer synched up so that results can be compared. The way to accomplish this is to copy and paste the data (and your response) into frozen values that can be analyzed. The Source Data Component does this. It copies a row of data drops and inserts it into row 10. When you type your answer into the dashboard it triggers a recalculation that and at the same time replicates the random test data and your answer to row 10. Along with this data is a snapshot of the date and time (see the formula in cell H9). This can be used to compute how much time has elapsed since the last question was answered. The information on dates and times are calculated in cells K13:L18.

The incrementing mechanisms to keep a running tally of the total number of questions asked, total correct answers, and the amount of time elapsed is based on the techniques described in one of my previous articles (Taking Dashboards to the next level: Dashboard Simulators).

Here is the final dashboard:

It is interactive

Closing Thoughts

There are plenty of enhancements that could be made to a dashboard of this kind. Rather than generating a straight set of random numbers, you could adjust the level of difficulty to match the degree of correct responses and rapidity with which the respondent answers. The dashboard could also connect to third party data sources in real time to retrieve questions or record testing progress. At the very least, putting stopwatch your dashboard can make it both lively and revealing.


2006 Evolving Technologies Corporation-all rights reserved.

Loren Abdulezer is the CEO of Evolving Technologies Corporation, and author of the best-selling Escape from Excel Hell and the recently published Excel Best Practices for Business. He is also Technical Editor of the book, Crystal Xcelsius for Dummies.

ETC, an Xcelsius Consulting Partner, is a technology consulting firm based in New York City. More information about Xcelsius can be found on Loren's web site: XcelsiusBestPractices.com. He can be reached at la@evolvingtech.com.

Close window