Tutorial 3


Managing Data Contents and the Display of Data Values

Data Display vs. Data Values

The data cell display is limited by the physical size of the cell at the time the user is working with the spreadsheet. You may notice that if the cell size is too small, the display changes to a series of hash-marks, indicating that the available space is too small to display a meaningful value.

When we perform numerical calculations the desired accuracy depends on the problem we are solving. You know from buying gasoline, that even though the prices are quoted as $3.6999, you always pay the amount rounded to the nearest cent. At other times, we wish to perform the computation as accurately as possible.

Excel application provides us with two ways of adjusting the display of numerical data. The first one is through formatting. The Number menu in the Home toolbar allows us to select the number of decimal digits that will be displayed, and to choose whether the numerical data represents currency, time, date, percentage, etc. We will explore the properties of some of these data formats.

The second way is to define the appropriate format for the cell by selecting a specific format. The effect of selecting the format is that the value that the cell represents assumes to be the value that is displayed. Let us see on a concrete example what does this mean.

We set up a simple example. The value of the cell B4 is computed to be the square root of 10:

We change the width of the column B and increase the number of decimal points that are displayed by clicking on the Increase Decimal tab in the Number part of teh Home toolbar. For comparison we also include the result of the formula =SQRT(1000)/10 --- that should evaluate to the same number. We notice, that all digits beyond 15 significant digits are zeros, regardless of how many decimal digits we choose to display. The computer limits the size of all numbers, unless the programmer takes extraordinary measures to preserve higher accuracy. To do so is beyond the study of introductory computing. However, we wish to see whether these two values, computed in a slightly different way, are considered to be the same. We copy the two values to the adjacent column C and then run some tests. Each comparison is defined as shown above for the cells D4 and D6, or below for the cells B7 and B8. We see that regardless of teh data representation, the values are considered to be the same.

We now copy the cells below, but change the values in the cells B12 ans B14 to be rounded to six decimal digits. We see that while the displayed values are the same the comparison of the original value and the rounded value fails. We need to keep this in mind when designing programs. We must consider in each case wehther we want to retain the original accuracy of the computation or whether we want to round the value to a specific number of decimal digits.

Ordering the Values

We will now consider the ways in which the Excel application orders different types of data. If we know how to compare two data values and decides which one is smaller and which one is larger, or which one comes first (e.g. a word in a dictionary) and which comes later, we can arrange a list of data in a sorted order. We can also find the smallest value or the largest value.

Let us look at some of the data types for which Excel can determine the order. These are numbers, percentages, dates, letters, and text. Here are some examples with the comparisons to verify that Excel does understand the ordering:

For some of the data types the Excel also knows how to compute the next value. If our list of data starts with the numbers 1 and 2, we can extend the numbers to a list of numbers 1, 2, 3, 4, ... by highlighting the adjacent cells that contain 1 and 2 and dragging the (invisible) successor formula to the remaining cells. Let us see what happens if we try to extend our original data to the cells below in this manner:

We see that not only can Excel find a succcessor for numbers, it also determines the next percentage by replicating the difference between the first two values. It also correctly find the next date, including the knowledge of leap years. However, it does not know what is the next letter in a sequence that starts with A and B, and so when we try to extend these two values, it just repeats the pattern there. The same happens with the text in the cells E12 and E13.

Functions over ordered values:

We have already seen in our early examples that Excel defines functions that can find the minimum and the maximum values in a list of numbers, can compute the average, median, and other statistical function over numerical data. Let us see whether we can apply the MIN and MAX functions to values of types other than plain numeric data type:

The example shows that we can find the minimum or maximum date and percentage values, but we get a nonsense value, just number 0, when we try to find the minimum or the maximum value among letters or text type of data values.

However, we can sort the data so that the list is ordered either in the ascending order or in the descending order. This is such a fundamental operation that it appears as a separate entry in the Editing tab of the Home toolbar. It is not entered as a function. Instead, we select the list of data to sort and apply the sort operation to that list. The values in the cells in the selected list change so that they are now in the sorted order. We first show how each of our lists of data changes when we sort it in ascending order:

We had to fight Excel a bit when applying sorting to our selected data --- it kept asking us whether the data in adjacent columns should be included in sorting. The reason for doing this is that typically several adjacent columns represent information about one data object. In the following example we have five lines in an invoice, each representing the ordering of one kind of item:

We select the entire order and decide to sort it. Select the menu entry in the Data toolbar. This brings up a dialog box that allows you to select which column should determine the ordering and whether to sort in ascending or descending order. We show the results for sorting this list of orders in five different ways. Notice, that the information for each item remains in the same line, only the ordering of the whole lines have changed.

Practice these skills and explore the Excel behavior on the following problems:

  1. Create a spreadsheet MyFriends.xls that will help you remember your friend's birhtdays. (It is OK to use fictitious data.) For each friend record their name, the date of birth, the number of years you have been friends, and the state in which your friend lives.
  2. Find the date of birth of your oldest (in age) friend, find the largest number of years you have been friends with someone.
  3. Make a copy of your list and sort your list of friends by the number of years you have been friends.
  4. Make a copy of your list and sort your list of friends by the date of their birth.
  5. You really would like to just look at the day and month of their birthday and sort by just that information. Make a new column that records just the month, and another column that records just the day of their birthday. Compute the value that should be in that column by using the MONTH and DAY functions. Now sort the data first by month then by day. In the Sort dialog choose Add Level then instruct the sorting to be done first by months, then by days.

Last modified: Mon Sept 29 12:20:18 EDT 2008