Download (direct link):
4 C«*t 1
' Ñè^ã 0
8 1 1
è » * \Smdfm I Ofttru I few C*U / | < >|
Figure 12-12: After entering the DCount functions to total flavor preferences.
DISPLAYING PERCENTAGES RATHER THAN TOTALS
As useful as the totals for each flavor may be, displaying them as percentages is usually preferred. Remember, the total changes each time new data is added to the list. In order to calculate percentages, you need to know the total number of respondents, which is a useful piece of information in itself. To find this, you can make use of a feature of the DCount function: when it is given a blank criterion, it counts all records in the list. Thus, the following function returns the total number of records in the list:
DCOUNT('Raw Data'A:F, 1, Criteria!G1:G2)
Note that this is dependent on cells G1 and G2 in the Criteria worksheet remaining blank. To include the total respondent count on the Analysis worksheet, follow these steps:
1. Insert two new rows at the top of the worksheet so that the existing content now starts in row 4.
2. Enter the text “Respondents” in cell A2.
3. Enter the function DCOUNT('Raw Data'A:F, 1, Criteria!G1:G2) in cell C2.
Now that you have the total number of respondents, you can modify the formulas in cells D4:D9 to display percentages rather than totals. For the vanilla category, for example, the calculation is simply the number preferring vanilla divided by the total number of respondents. For example, the new formula in cell D4 will be
262 Part IV: Case Studies
You can go ahead and change all six formulas, dividing each by the value in cell C2. After formatting cells D4:D9 as Percentage the worksheet will look like Figure 12-13.
bJS a e ñ P E 1 F i 6 1 H T
2 Pttfondni 3
- FlPfV«f** Q%
Choc olaie 33%
* * $}>»?»-TT) Cqi** 39% »%
a Ch*»* 0%
è « » »• Afw»)n*v. Crffir.4 / *««D4U / l< > 1
Figure 12-13: Displaying percentages for flavor preferences.
CREATE A CHART OF FLAVOR PREFERENCES
The final step in this analysis is to create a chart displaying the flavor preference data. This task is greatly simplified by Excel’s Chart Wizard. Here are the required steps.
1. Select cells C4:D9 in the Analysis worksheet.
2. Click the Chart Wizard button on the toolbar, or choose Insert ^ Chart. Excel displays the Chart Wizard dialog box.
3. Select Column under Chart Type, and Clustered Column under Chart Subtype, then click Next.
4. In the next dialog box (Step 2 of 4) there are no changes needed, so click Next to proceed.
5. In the next dialog box (Step 3 of 4), display the Legend tab and deselect the Show Legend option. Because this chart has only one data series, a legend is not needed. Then, click Next.
6. In the final wizard dialog box, select the As Object In option and make sure that Analysis is selected in the drop-down list. This creates the chart as an object embedded in the Analysis worksheet.
7. Click Finish to create the chart.
8. In the worksheet, drag the chart to the desired position and size.
When you have completed creating the chart your analysis worksheet will look like Figure 12-14.
Chapter 12: Connecting Excel and InfoPath
A 8 . Ñ . o e <5 " J ê-
' . I
- liw ÐãÈãøñ»! Vvdi os
? Ch*-y os
9 Olh* 0*
x / *? *» Ø* / 14 >|
Figure 12-14. Displaying a chart of flavor preferences.
TESTING WITH MORE DATA
It’s always good idea to test an analysis worksheet with additional data. You can see that your functions and chart work when there are three records in the list -will they still work properly when the list expands? To check this:
1. Go back to the InfoPath form and add a number of additional data records to the three you entered earlier.
2. Save the file.
3. Switch to Excel and display the Raw Data worksheet.
4. Click the Refresh button on the List and XML toolbar. Excel imports the new records from the InfoPath form.
5. Display the Analysis worksheet. It should have automatically updated to reflect the added data, as shown in Figure 12-15.
When designing a data entry and analysis solution that uses InfoPath and Excel, there are a few other concerns that you need to keep in mind.
264 Part IV: Case Studies
Figure 12-15: The analysis worksheet automatically updates with new data.
One concern is data validation. InfoPath always validates a form when it is being filled out, but it does allow the user to save a form that contains validation violations. Likewise, Excel can validate XML data against its schema when it is imported, but this is an option that must be specifically enabled for an XML list (in the Properties dialog box). It is somewhat pointless to have validation required in both InfoPath and Excel. When designing the solution, you need to make a choice: