Creating “Smart” Sliders in Xcelsius
By Loren Abdulezer
Fig. #1
In my previous article, I showed you how drill down charts work. This month, I want to look at another best practice for Xcelsius. Specifically, I’d like to show you how to manipulate data in a way that will make spreadsheet users envious.
The example I want to discuss relates to using "Smart" Components in Xcelsius. It is worth your time to try the sample model (Figure 1).
If you have not yet upgraded to Xcelsius XL - Professional or Enterprise XE (or later), you will need to upgrade or download the trial version if you want to play with this example’s source files. You can follow the links at the bottom of the sample Xcelsius model (Figure 1) to obtain the Xcelsius Trial Software, as well as the spreadsheet and the .xlf file for this article.
When you work with spreadsheets, one of the difficulties you may find is that there are very few safeguards when it comes to entering data. If, for instance, you are allocating a budget or assigning probabilities, you will want to be sure you never exceed 100% of the aggregate.
There are several features in Excel which can help you achieve this, but they are not as simple and intuitive as you might like. One of these features is conditional formatting. Conditional formatting can allow you to generate a visual signal by adjusting the cell's format, such as a background color or font style. Such visual cues are useful, but they will not stop you from entering invalid data. You can also use the data validation feature of Excel to set up rules that restrict data values that can be entered into a spreadsheet cell. This will work, but it doesn't allow you to adjust the data fluidly, the way you can with Sliders in Xcelsius. Both these techniques are discussed in my book "Excel Best Practices for Business".
Setting up a “Smart” Slider in Xcelsius
It helps to use a concrete example. Consider a spreadsheet that calculates damages for auto insurance. Don't bother worrying about deductibles or any complicated scenarios, for now; just pay attention to what's needed for setting up the Xcelsius sliders.
Your first step is to set up your spreadsheet for use with the Xcelsius Slider component (see Figure 2). The example spreadsheet and the Xcelsius files for this article can be found in the Article Reprints section of http://www.xcelsiusbestpractices.com.
Fig. #2
As you can see, the percentages in this spreadsheet should not exceed 100%. In the screen shot they add up to 96%. The sum of all the probabilities should add up to 100%, and not the 96% shown. This leaves you with a 4% unallocated percentage. From the numbers shown in Figure 2, the probability that no incidents occur could be increased from 81% to as much as 85% (= 81% + 4%). Alternatively, you could bump up the 5% figure for the probability that damages are $500 up to 9% (= 5% + 4%). You could have a hybrid, where the first probability is 83% and one of the others is 7%. There are virtually an infinite number of ways to divvy up the unallocated percentage. But, so long as you have the upper limit computed (which are the Max Percentages appearing in column C of Figure 2), Xcelsius will take care of the rest.
In Xcelsius, create a slider and link it to your spreadsheet as you normally would (this is shown for the first slider in Figure 3).
Fig. #3
In this case, link the value to cell B6 of the spreadsheet. You can set the Slider Title manually or link it as it is shown in Figure 3.
Now we get to the crux. Click on the Behavior tab in the slider's property panel, and link the 'Maximum Value' to cell C6 of the spreadsheet (see Figure 4).
Fig. #4
What you've just done is use a spreadsheet formula to let you dynamically adjust the slider limits. As you move any of the sliders, the limits for all the sliders are continually adjusted. In this way the total percentage will not exceed 100%.
- It's worth pointing out some quick observations. The ‘Minimum Value' in this example has been hardwired to 0.0. When you work with your own XVM (Xcelsius Visual Model), you may want to peg the 'Minimum Value' to something dynamically computed in the spreadsheet.
- Don't forget to set the 'Increment' value to an appropriate size. In this case, the sliders are showing percentages. The precision used is a tenth of a percent. In decimal representation, this is 0.001 or 1.E-003.
- The Lower and Upper Limit Behaviors of 'fixed' vs. 'adjustable' refers to whether or not you want the model user to adjust the limits. Leave these limits as 'fixed'.
In the Appearance tab, check the 'Show Limits' box (Figure 5).
Fig. #5
Also at that top of the Appearance tab you will see a Titles and Layout button. If you wish to set the number of divisions on the slider to 10, set the number of Ticks to 11 (Figure 6).
Fig. #6
Then, just replicate your settings for the remaining three sliders.
Hopefully, this is enough to get you started using “Smart” Sliders in Xcelsius. Incidentally, the concept of “Smart” Sliders applies to other Xcelsius components as well, such as Dials, Values, and Spinners.
Loren Abdulezer, CEO, Evolving Technologies Corporation http://www.xcelsiusbestpractices.com
©2005 Evolving Technologies Corporation - all rights reserved.