Microsoft Office Tutorials and References
In Depth Information
cell of the corresponding cells. If the difference is not 0, then return the phrase
“Not Equal”, otherwise return “Equal.”
5. Use a logical IF function in cell A1 to test a value in B1. Examine the contents
of B1and return “In” if the values are between and include the range 2 and 9. If
the value is outside this range, return “Not In.”
6. Use a logical IF function in cell A1 to test values in B1 and C1. If the contents of
B1 and C1 are, 12 and 23, respectively, return “Values are 12 and 23”, otherwise
return “Values are not 12 and 23.”
7. Use a logical IF function in cell A1 to test whether or not a value in B1 is
an integer. Use the Mod function to make the determination. Return either
“Integer” or “Not Integer.”
8. What type of analysis does cross-tabulation allow a data analyst to perform?
9. What types of data (categorical, ordinal, etc.) will PivotTables and PivotCharts
permit you to cross-tabulate?
10. Create a PivotTable from the data in Exhibit 5.2 (minus Case 13) that performs
a cross-tabulation analysis for the following conﬁguration: Region on the Row
ﬁeld; Income in the Values ﬁeld; Product 2 on the Column ﬁeld; and Age on the
a. What are the counts in the Values ﬁeld?
b. What are the averages in the Values ﬁeld?
c. What are the maximums in the value ﬁeld?
d. What Region has the maximum count ?
e. For all regions taken as a whole, is there a clear preference, good or bad, for
f. What is the highest average income for a region/preference combination?
g. What combination of region and preference has the highest variation in
11. Create a cell for counting values in range A1:A25 if the cell contents are equal
to the text “New.”
12. Create a PivotChart from the PivotTable analysis in 10c above.
13. The Income data in Table 5.6 is organized into two categories. Re-categorize the
Income data into 3 categories—0–24,000; 24,001–75,000; 75,001 and above?
How will the Exhibit 5.23 change?
14. Perform the conversion of the data in Table 5.7 into a column chart that presents
the same data graphically?
15. Create a weighted average based on the sum of incomes for the various cate-
gories. Hint—The weight should be related to the proportion of the category
sum of income to the total of all income.
16. Your boss believes that the analysis in Exhibit 5.23 is interesting, but she would
like to see the Age category replaced with Region. Perform the new analysis
and display the results similarly to those of Exhibit 5.23.
17. Advanced Problem —A clinic that specializes in alcohol abuse has collected
some data on their current clients. Their data for clients includes the number