Microsoft Office Tutorials and References
In Depth Information
that a new observation has been added for Wednesday (now categorized as day 4),
9-Jan for zero dollars. Every day of the week will have an entry, although it may
be zero dollars in expenditures, and there may be multiple expenditures on a day.
Finally, although we are interested in individual expenditure observations, weekly,
and even daily, totals could also be quite valuable. In summary, the original data
collected needed substantial adjustment and summarization to organize it into more
meaningful and informative data to achieve our stated goals.
Let us assume that we have reorganized our data into the format shown in
Table 2.5. As before, these data are arranged in columnar format and each obser-
vation has 6 ﬁelds plus an observation number. We have made one more change to
the data in anticipation of the analysis we will perform. The Weekday ﬁeld has been
converted into a numerical value, with Sunday being replaced with 1, Monday with
2, etc. We will discuss the reason for this change later.
2.5.4 Analyzing Data
Now we are ready to look for insights in the data we have collected and summarized;
that is, perform analysis. First, focusing on the dollar value of the observations, we
see considerable variation in amounts of expenditures. This is not unexpected given
the relatively small number of observations in the semester. If we want to graphically
analyze the data by type of payment (credit card or cash payments) and the category
of expenditure (F, P, S), then we will have to further reorganize the data to provide
this information. We will see that this can be managed with the Sort tool in the
Data tab. The Sort tool permits us to rearrange our overall spreadsheet table of data
observations into the observations of particular interest for our analysis.
Dad suspects that the expenditures for particular days of the week are higher than
others from the data in Table 2.5. He begins by organizing the data according to day
of the week—all Sundays (1), all Mondays (2), etc. To Sort the data by day, we ﬁrst
capture the entire data range we are interested in sorting, including the header row
that contains column titles (Weekday, Amount, etc.), then we select the Sort tool
in the Sort and Filter group of the Data tab. Sort permits us to set sort keys (the
titles in the header row) that can then be selected, as well as an option for executing
ascending or descending sorts. An ascending sort of text arranges data in ascending
alphabetical order (a to z) and an ascending sort of numerical data is analogous.
Now we can see that converting the Weekday ﬁeld to a numerical value insures a
Sort that places weekdays in ascending order. If the ﬁeld values had remained Sn,
M, etc., the sort would lead to an alphabetic sort and loss of the consecutive order
of days—Friday as day 1 and Wednesday as day 7.
Exhibit 2.14 shows the data sort procedure for our original data. We begin by
capturing the spreadsheet range of interest that includes the observed data and titles,
now containing more than 60 observations due to our data summarization. In the
Sort and Filter group we select the Sort tool. Exhibit 2.14 shows the dialog boxes