XL Data Analyst™
Basic Version 2.0
Documentation Manual
Table of Contents
XL Data Analyst™
Basic Version 2.0
Documentation Manual
Table of Contents
Requirements for Running the XL Data Analyst on Excel 2007
”Installing” the XL Data Analyst
How the XL Data Analyst is Organized
Basic Operation of the XL Data Analyst
Confidence Intervals for a Percentage
Differences between 2 Group Percents
Using the XL Data Analyst with Your Own Dataset
Defining Variables in the Define Variables Worksheet
This manual describes the setup, functioning, and specifics of the XL Data Analyst – Basic Version – that accompanies Basic Marketing Research, 2nd edition, by Alvin Burns and Ronald Bush (Prentice Hall 2008). This textbook utilizes this data analysis system throughout, and it provides three XL Data Analyst data sets to users. One of these data sets, College Life E-Zine, pertains to a case study that is integrated throughout the textbook. This XL Data Analyst data set is used for illustrations in this manual.
The XL Data Analyst is a data analysis system developed to run on Microsoft Excel. It performs data analyses of various types and generates tables with professional appearance that can be copied into other applications such as Microsoft Word or PowerPoint. Alternatively, the format features of Microsoft Office programs allow users to apply unique table formats or format tweaks quickly and easily, while the Excel platform also allows users to create graphs using Excel’s chart features. Note: the next two figures are annotated screen captures that appear in the textbook.
For those analyses that utilize statistical tests, the XL Data Analyst displays “interpreted” output, meaning that the 95% level of confidence is applied, and the findings are explained in plain English.
Requirements for Running the XL Data Analyst on Excel 2007
The XL Data Analyst resides in an Excel file powered by the XL Data Analyst macro system. Thus, the requirements for running the XL Data Analyst are…
a. Start Excel
b. Open an XL Data Analyst File as you would open any Excel Macro Enabled (.xlsm) file.
c. Enable Macros when prompted. That is, click “Options” on the Security Warning, then select “Enable this content,” and click on OK.
d. Click on the Add-In tab, and use the XL Data Analyst Menu that appears on the Add-In menu bar.
”Installing” the XL Data Analyst
Because the XL Data Analyst is a macro system residing in an Excel file, there is no installation other than copying the file(s) onto your hard drive. This procedure can be accomplished downloading the files from the XL Data Analyst Basic website.
www.XlDataAnalyst.com/Basic
(capitals not required)
Downloading from the website is strongly recommended as the website provides the most recent upgraded XL Data Analyst files.
The “installation” procedure is as follows…
Go to the download area of the XL Data Analyst website, and click on the “Download” button for the 2007 version files. (Note: the website may appear different, subject to improvements and enhancements.)
How the XL Data Analyst is Organized
This section describes how data are organized, variable descriptions, value codes, and value labels. It also describes how these are related in the XL Data Analyst.
Users who will simply use the XL Data Analyst Basic files that are downloaded from the XL Data Analyst (for instance, those who will use the files only for one or more of the related cases in Marketing Research, 2nd edition) without modification may skip this section of the manual as this material is not critical to the use of the data sets in this context.
The XL Data Analyst has two essential worksheets named Data and Define Variables. The Data worksheet holds raw numbers and other data elements that constitute the dataset. As can be seen below, the Data worksheet is arranged in columns and rows. The columns are associated with variables (or questions on a questionnaire), while the rows are associates with respondents or subjects. Row 1 of the Data worksheet must contain variable labels although the “labels” feature of Excel does not need to be invoked. For more detail on the format of the labels on the Data worksheet, refer to “Using the XL Data Analyst with Your Own Dataset.”
The Define Variables worksheet is set up in parallel with the Data worksheet. As can be seen above, the Define Variables worksheet has the Data worksheet variable labels in its Row 1 (although lagged by one column). Beneath each Variable Label on the Define Variables worksheet is a Description of any length desired. The user places the Descriptions in their appropriate locations on the Define Variables worksheet. Also, beneath the Description is a set of Value Codes, or code numbers in the Data worksheet that correspond to the answers to the associated question on the survey, while beneath each Value Code cell are the associated Value Labels.
Thus, in the figure above, the Variable “ACCESS” is in column 1 of the Data worksheet. It is associated with Column 2 of the Define Variables worksheet, and the associated Description is “Do you have Internet access?” The answer codes are 1 or 2, and these pertain to “Yes” and “No,” respectively.
The Descriptions are vital as they appear in the various XL Data Analysis selection windows and they appear as table headings on the output.
The Value Codes are similarly essential in that they appear in various XL Data Analysis selection windows, and they appear in XL Data Analyst output tables.
For detailed information on Variable Labels, Descriptions, Value Codes, Value Labels and other related topics, please refer to “Using the XL Data Analyst with Your Own Dataset”
Basic Operation of the XL Data Analyst
All analyses in the XL Data Analyst utilize a simple three-step procedure.
1. Select the analysis from the XL Data Analyst menu.
2. Select variables in the XL Data Analyst window that appears. In some windows, users may be required to enter values (e.g. performing a hypothesis test requires that the hypothesis number be entered).
2. Examine and interpret the output.
As can be seen in the figure above, an analysis result is written on a new worksheet that is provided with a descriptive name. Subsequent analyses of the same type are placed on new worksheets that are numbered sequentially, such as Percents1, Percents2, and so on.
Pinning the XL Data Analyst Menu to the Quick Access Toolbar
Users may find the Add-Ins menu bar cumbersome to use, and they may opt to pin or “attach” the XL Data Analyst menu to the Quick Access Toolbar by right clicking on the XL Data Analysis menu heading on the Add-Ins menu bar and then clicking on the “Add Group to Quick Access Toolbar” option.
The pinning operation makes the XL Data Analyst menu available from the Excel Quick Access toolbar and independent of menu bars. All analyses examples in this section, and most examples in this manual are shown with the XL Data Analyst menu pinned to the Quick Access toobar.
The following sections describe the various analyses possible with the XL Data Analyst using screenshots that appear in Basic Marketing Research, 2nd edition, by Burns and Bush. Terminology is also specific to this textbook.
Summarization analyses involve percentage distributions for categorical variables and averages for metric variables.
Percent Analysis. Use Summarize-Percents, and select the variables. If more than one variable is selected, percentage distribution tables are created in the order of the variable selections.
Average Analysis. Use Summarize-Averages, and select the variable(s). If more than one variable is selected, the table will list the variables in descending order of the computed averages.
Generalization analyses pertain to confidence intervals and hypothesis tests for percents or averages.
Confidence Intervals for a Percentage. Use Generalize-Confidence Interval-Percent.
Confidence Intervals for an Average. Use Generalize-Confidence Interval-Average.
Hypothesis Test for a Percent. Use Generalize-Hypothesis Test-Percent.
Hypothesis Test for an Average. Use Generalize-Hypothesis Test-Average.
Differences between 2 Group Percents. Use Differences-2 Group Percents.
Difference between 2 Group Averages. Use Compare-2 Group Averages. Note: Multiple Target Variables may be selected.
Differences among 3+ Groups. Use Compare – 3+ Group Averages. This procedure is also known as “Analysis of Variance” (ANOVA). Note: Multiple Target Variables may be selected.
Difference between 2 Variable Averages. Use Compare – 2 Variable Averages. Note: Multiple pairs can be selected. Caution: For valid comparisons, the two variables should have the same measurement scale (i.e., both should be in dollars, number of times, 5-point scale, etc.)
Crosstabulation Analysis. Use Relate-Crosstabs. Note: Multiple Row Variables can be selected.
Correlation Analysis. Use Relate – Correlate. Note: Multiple Other Variables can be selected.
Regression Analysis. Use Relate – Predict (Regression). Note: Excel allows for a maximum of 16 Independent variables. If the user selects more than 16, the XL Data Analyst with issue a message as to the maximum of 16 and analyze only the first 16 variables selected by the user.
There are 2 output tables. The first one reports the regression analysis with all selected independent variables and significance finding for each. The second tables reports only significant (95% level of confidence) independent variables based on a backwards stepwise regression procedure. If this procedure finds no significant independent variables, the second table is not reported.
Table of Random Numbers. Use Calculate – Random #’s. Users may obtain tables of up to 9,999 random numbers with a maximum value of 100,000,000.
Sample Size Calculation. Use Calculate – Sample Size. The XL Data Analyst will calculate sample size using the standard sample size formula for a percentage at the 95% level of confidence where the user estimates p (percentage) and allowable error (e). The output provides sensitivity analysis for alternative levels of e (e ± .5 and e ± 1.0) at the constant value of p, and alternative levels of p (p ± 5% and p ± 10%) at the constant value of e.
Version 2.0 of the XL Data Analyst has four utilities added to assist users in various ways.
These utilities are: Clean-Up, Import Data, Filter Data, and Unfilter Data. The purpose and operation of each utility is described in the following passages.
Clean-Up. The Clean-Up utility is useful to users who set up their own data sets in the XL Data Analyst (Refer to INSERT SECTION NAME). It is also useful if users encounter errors while using the XL Data Analyst.
As noted in the message box that appears when the Clean-Up utility is activated, Clean-Up performs the following checks and fixes:
Import Data. The Import Data utility allows users to import a comma separated variable (.csv) data set into the current XL Data Analyst file. To utilize Import Data without problems, users must have their .csv files organized in the rows-by-columns arrangement noted for the Data worksheet, and the first row must have a variable label for each dataset column.
Users are first questioned about the nature of the data they wish to import.
Upon OK, the XL Data Analyst will provide a File Open menu that is limited to only comma separated variable (.csv) files. The user must navigate to find his/her .csv file to be imported.
The import operation may take a few seconds or longer, depending on the size of the user’s file that is being imported. Upon completion, the following messages are issued.
As can be seen in the figure below, the Import Data utility places the Variable label in the associated Description cells on the Define Variables worksheet. To complete the data import process and use the XL Data Analyst’s full functions, users should set up proper Descriptions, Value Codes, and Value Labels. After this work, it is strongly recommended to use the Clean-Up utility to check for errors.
Upon completing the import data process and setting up the Define Variables worksheet to pertain to the imported data, users must “Save as…” the file in order to save it under a unique XL Data Analyst file name. Until the “Save as…” operation takes place, the file will exist under the imported into XL Data Analyst file name.
Filter Data. Users who wish to analyze a subset of the full dataset may use the Filter Data utility to identify which elements in the full data set are selected an analyzed.
With Filter Data, the user is first prompted to use the Add-Ins tab for “Explain,” “Cancel,” or “Done.”
The user has full availability of Excel’s filter feature to select data based on the code numbers under each variable in the data set. Any number of variables may be used in the filter/selection process.
When the user has completed the filter selection process, he/she should use the “Done” button on Add-Ins menu bar. The XL Data Analyst will signal that the filtered data set is now available for analysis and that the original data set has been retained intact.
Unfilter Data. The Unfilter Data utility returns the original, full data set.
Using the XL Data Analyst with Your Own Dataset
Users can easily apply the XL Data Analyst to their own datasets. As noted earlier, there are two worksheets used to set up your dataset in the XL Data Analyst. These worksheets are fully established in the three XL Data Analyst datasets that may be downloaded from the XL Data Analyst Basic website. Users wishing to apply the XL Data Analyst to their own dataset may do so by correctly substituting their dataset in the Data worksheet and defining their variables, value codes, and value labels in the Define Variables worksheet. When completed, save the dataset under a new Excel file name. The XL Data Analyst system will be saved in that file.
Users have two options as to creating a Data worksheet with their datasets: (1) use the Import Data utility, and (2) establish the Data worksheet manually.
Variable Names in the Data Worksheet. The only requirement for the Data worksheet is to have unique variable names in row 1. Requirements for variable names are as follows.
· Variable names must be unique, not repeated.
· Variable names can be any length.
· Variable names should be letters and/or numbers.
· Upper- and/or lower-case letters can be used.
· Variable names should not have spaces in them.
· The Labels function of Excel does not need to be invoked.
c. Input Data into the Data Worksheet. Since Row #1 contains the variable names, the dataset necessarily should reside in Row #2 to the last row that constitutes the dataset. Users whose datasets are in spreadsheet organization may accomplish this with a copy and paste operation. Users whose datasets are in raw form (i.e. on questionnaires or otherwise not in spreadsheet organization) must enter their data in manually.
Once the Data worksheet has been established with the user’s own dataset, it is prudent to save the work as an Excel file in the user’s dataset name.
The Define Variables worksheet contains Variable Labels, Descriptions, Value Codes, and Value Labels.
If the user has used the Import Data utility, the prior XL Data Analyst Define Variables contents will be deleted. The new Variable Labels will be on the Define Variables worksheet in the Descriptions cells, and the Data and Define Variables worksheets will be linked properly.
Users who use the manual creation option must clear the contents of the XL Data Analyst file being modified for their own datasets. To clear the contents block B1-B4-to the end of the Define Variables current dataset variables definitions and use Clear Contents.
To link the Data worksheet to the Define Variables worksheet, do the following.
1. Block and copy the labels in Row 1 of the user’s new data set now established in the Data worksheet. The example below is for a user’s dataset called “Jack-In-The-Box-Survey.”
2. Move to the Define Variables worksheet, and place the cursor in cell B1. Do a Paste Special – Paste Link operation to link these variable names between the two worksheets.
3. Under each variable name in the Define variables, enter in the Variable Description, Value Codes, and Value Labels according to the following instructions.
Variable Description. In Row 2, type or paste in long descriptions of the variables. These descriptions will appear in XL Data Analyst tables. There is no limit to the length and any letter, number, punctuation mark or symbol is acceptable. If complete variable descriptions do not show completely in the Define Variables worksheet, block them and use Format Cells – Alignment – Wrap Text to make the complete descriptions appear.
Value Codes. Where the variables are coded with numbers that pertain to groups or categories (e.g. 1=male, 2=female; 1=married, 2=single, 3=single survivor, etc.) enter in the code numbers, each separated by a comma. Note: It is vital that users not use spaces in the value codes as the XL Data Analyst takes these into account. For example, if the user specifies 1, 2, then the XL Data Analyst will interpret the codes as “1” and “ 2” meaning that a “2” in the Data worksheet for that associated variable will be treated as different from a “ 2”code. Also, Clean-Up will not detect spaces in the value codes.
Value Labels. In the cells directly under the cells where value codes have been entered, enter the corresponding value labels, each separated by a comma. Value labels will appear in XL Data Analyst tables in place of the value codes.
Where variables are metric, that is, natural numbers such as age, number of times, dollar spent, etc, value codes and value labels are not entered.
As has been recommended, the user is reminded to use the Clean-Up utility to check this work. Also, the user is reminded that the new file must be saved with a “Save as…” Excel operation in order to be established as a separate XL Data Analyst file with a unique file name.
Excel will issue errors if it is required to perform operations such as division by zero. The XL Data Analyst has been programmed to inspect data involved with its analyses and to warn users with pop-up messages such as the following.
In addition, the XL Data Analyst will indicate “Error” on its output tables so users may determine the offending variable(s). See the following example.
Be certain to download the most recent version of these file from the XL Data Analyst Basic website. These files that pertain to the three case study datasets used in Basic Marketing Research, 2nd edition, that users download should not have errors. It is possible for users to encounter errors if they inadvertently change the Data worksheet or the Define Variables worksheet. If an error that causes a Visual Basic error message (see below) occurs, users are advised to re-download the file.
Users who apply the XL Data Analyst to their own data or who intentionally change the downloaded original XL Data Analyst files may encounter Visual Basic errors. Specifically, errors that are internal to the XL Data Analyst (i.e. Visual Basic errors) will be encountered if a user does not adhere to the requirements for XL Data Analyst. Below are Visual Basic error messages, and likely causes/solutions.
Cause: On the Define Variables worksheet, the number of value labels is different from its corresponding number of value codes for one or more variables.
Solution: Run Clean-up to check that the number of value codes is equal to the number of value labels for each variable.
Cause: The variable labels on the Data worksheet are not properly linked to the Define Variables.
Solution: Run Clean-up. Alternatively, re-link these by copying Row 1 (variables names) on the Data worksheet; place the cursor in cell B1 on the Define Variables worksheet, and Paste Special – Paste Link.
While extensively tested, the XL Data Analyst may issue an error that a user cannot solve. In such cases, users are requested to email to support@xldataanalyst.com the nature of the error so support can address the error. Please refer to the Support area on the XL Data Analyst website (www.xldataanalyst.com) for how to report these errors.