Dynamic Chart Presentation Solutions That Pack a Punch in Microsoft Office Excel 2007

12/10/2008

Contents

Profile Comparisons on the Radar Chart

The first time anyone sees a radar chart without its data series, they immediately think of a spider’s web. So do these charts show what was the prey? Or do they plot the results of a managerial radar surveillance? Of course, they don’t. The data series for this extremely helpful chart type appear as lines that have been closed to form shapes and/or molded areas. The term “profile chart” would be more appropriate because this is exactly what this chart produces best: easy-to-understand profiles and specific shapes that form a whole. “The chart clearly comprises comparatively similar shapes that differ in some regard,” is an accurate description. You will immediately see why the radar chart is particularly good at any form of comparison and why it is my unrivaled favorite when it comes to comparative analyses for survey or measurement results.

The Focus 1 worksheet in the file 1006_RadarComparison is designed for the Full screen presentation view and is protected without a password.

Allow me to set the scene: a beverages manufacturer has conducted sensory product testing several times and presented the results in a dynamic radar chart. This is part of a market analysis whose results will influence the company’s new product range.

First, you need to know how this model works. Note the following points with reference to Figure 10-21:

You can use four OptionButtons to determine which test results you want to display: Tap Water, Mineral Water, Soda Pops, or Juices (the sensory rating of tap water is a benchmark for standardizing the testing modes).
When you click an OptionButton, the selection lists for both underlying ComboBoxes are updated accordingly; in other words, their content changes. These lists, which are managed in parallel, enable you to make selections according to specific product groups and then compare, at product level, the data for each list entry against the data for another list entry. You can also remove either data series to display individual profiles.
The double selection made in the ComboBoxes is combined to form comparison profiles within the chart. An area that belongs to the left ComboBox is compared against a shape with a blue outline. Each category in the radar chart has its own value axis. However, its scaling is shown only once and fixed in this example (0, 100, 20, which means that the testing subjects could award each drink a maximum of 100 points). The data series connect the six value axes. The closer a data series on a value axis approaches the outer edge of the radar chart, the better the product rating. The category abbreviations shown as data labels are age groups. For example, TS_C 23-32 stands for Testing Subjects, group C, aged between 23 and 32. In short, the profiles show the popularity of each drink in each age group.
The comparison shown in the figure, for example, clearly shows that Soda Pop 02 had half-decent to good results but it didn’t prove to be very popular in the group TS_B. Soda Pop 07 received similar ratings in four groups (the same profile with slightly lower values), but was much more popular among children and young adults. In short: The profiles show which beverage is going down how well in which age group.
The upper right corner of the sheet contains labels that were generated by formulas and indicate the product group currently being displayed, the number of participants, and the month in which the test took place.

Figure 10-21 Whatever anyone tastes can be measured and compared using this chart.

Here’s how to set up the worksheets:

The parameter sheet contains four editable lists that have alternating text. In other words, when you click an OptionButton in the focus sheet, the ListFillRange of the ComboBoxes is filled as required by the user.

The collated data, which has already been condensed and calculated according to the average and maximum values, is defined in the four Data . . . worksheets.

The Basis 1 sheet shows the entire chart basis (comprising formulas) whose structural components you already know from other rS1 models. Here, cell K7 contains the formula ="rD"&rL1.OptIndex&".Node". This allows you to correctly assume that the Lists 1 sheet must contain an index entry that has something to do with the OptionButtons. Let’s consider this in greater detail:

Unlike the OptionButtons for the form controls, the similar objects from the ActiveX controls group do not output any numbers here, but rather the values TRUE or FALSE, like the CheckBox and ToggleButton. However, formulas such as ="rD"&rL1.OptIndex&".Node" require numbers. As already presented in another context, a text such as rD1.Node, rD2.Node, and so on, should be created dynamically. Therefore, you must first convert TRUE or FALSE into a number. The MATCH function converts the value in cell L7 (rL1.OptIndex).

The =MATCH(lookup_value,lookup_array,match_type) function is equivalent to VLOOKUP. It does not provide cell content as results, but rather a position number. The position of lookup_value in lookup_array is determined here, whereby the latter is a one-column or one-row range. Consequently, you can use MATCH for providing number arguments required within other formulas.

Here, I use zero as the match_type. As a result, the function always returns the position of the first of possibly several values. This first value is the lookup_value, which ensures that the lookup_array elements do not have to be sorted alphanumerically.

Note the following points with reference to Figure 10-22:

Figure 10-22 One number is determined from TRUE and FALSE in the rL1.OptIndex cell.

The formula =MATCH(TRUE,$L$13:$L$16,0) provides the position number for the cell that currently contains the value TRUE in the range L13:L16. It must be one of these cells. In a defined group of OptionButtons (additional information provided in the following paragraphs), only one cell returns the value TRUE and consequently all other cells return the value FALSE. Let’s stick with our example: if you now click the Soda Pops OptionButton (the third button) in the Focus 1 sheet, the following happens:

The value in linked cell L15 (rL1.Opt03Sel) becomes TRUE and, at the same time, the values in cells L14, L13, and L12 become FALSE.
As a result, the MATCH formula returns the value 3 in cell L7 (named rL1.OptIndex).
Consequently, the rD3.Node reference text to be processed using INDIRECT is generated in cell K7 in the Basis 1 sheet. As a result, the data from the Data 3 . . . worksheet is now imported into the chart.
Furthermore, the constants in the third list in the Parameters 1 sheet become the ListFillRanges content for both ComboBoxes. This is ensured by the formulas in the ranges N12:N22 and O12:O22 in the Lists 1 sheet. For example, the formula in cell N19:
```
N19 =OFFSET(rP1.Node,$G19,rL1.OptIndex)
```

So when it comes to the dynamics of selection lists in ComboBoxes, there’s nothing to it.

Let’s return to the Basis 1 sheet and the formula in cell L12, for example:

L12 =IF(rL1.Bev01Sel=0,"",
    OFFSET(INDIRECT($K$7),$G12,rL1.Bev01Sel))

If you clicked the first entry (None) in the ComboBox, the cell appears to be empty. Otherwise, the specific value is read from one of the data sheets. Instead of working with #N/A, I am using empty text here (as an exception) for such a function. However, I’m merely using it to show that in this case (not in all cases) it can also successfully suppress data series. However, this is neither systematic nor methodical.

In conclusion, I wish to briefly describe how to set up the controls in the Focus 1 sheet.

If you have already worked with CheckBoxes, you will find it easy to set up OptionButtons because the options and properties are, for the most part, identical. However, note the following:

ActiveX controls of the OptionButtons category always occur in groups. To ensure that users can only ever select one of several similar elements, the group members must be “aware of each other” because only one of them can be assigned the value TRUE. This requirement is fulfilled by defining a group name that combines several OptionButtons to form one functional unit, irrespective of their formats or where and how they are positioned on the screen. The associated property is called GroupName. In our example, I used the group name oF1_ OptGroup. For informational purposes, this name is also shown in cell L11 in the Lists 1 sheet. Here, it appears as a heading for those four cells used to process the user’s selection.

The appearance of both ComboBoxes was approximated to match those of the data series in the chart, so that these controls have a similar “legend-like” appearance.

The background color is determined using the BackColor property.
You must use the BorderColor property to determine the border color of a ComboBox. This setting, in turn, only becomes effective if you also set the BorderStyle property to fmBorderStyleSingle.