Section 2.2. Changing the Data


2.2. Changing the Data

You are not limited to the data you get. Sometimes it is helpful to change the form of a data item. You cannot create new information, but you can redefine existing data in ways that make it more useful.

2.2.1. Categorical Information

One of the data items we started with is Action Date. This is categorical information, but there are too many dates. If I use it as the row or column field I get too many categories. I can make this item more useful by changing it into a day of the week. We might find orders have different characteristics based on the day of the week they are received. I return to the data sheet and add a new column named Weekday, filling it out as shown in Figure 2-24.

Figure 2-21. Using the page field area


Figure 2-22. Drilling down


Figure 2-23. The details


Figure 2-24. Adding a new data item


I then go back to the pivot table, click on the pivot table menu and select PivotTable wizard . Clicking on the Back button brings up the dialog in Figure 2-25.

Figure 2-25. Changing the data range for a pivot table


Change the data range to DATA!$A:$L to add the new column, then click Finish, and the new data item appears in the field list.

There are times when you might need to change a scalar item into a categorical. I can convert Order Amount into a categorical item by adding a column that gives its quartile. The formula is shown in Figure 2-26.

The QUARTILE function has two parameters. First is a range that contains a list of numbers. Second is the quartile to be returned. It returns the maximum value of the quartile and the formula in Figure 2-26 returns the quartile number.

The new column holds a value that tells if the order amount is high or low in four steps. This can be used to define groups in the data. Using these two new data items, I built the table in Figure 2-27.

The first quartile is made up of orders with the lowest value. Here we see that the lower value orders are the oldest on average, and this is consistent across all five weekdays.

Figure 2-26. Quartile ranking of a numeric item


Figure 2-27. The table using the new data items


2.2.2. Scalar Information

You can also change a categorical into a scalar. This is most commonly done in modeling but it can be useful in research. We want to change Order Type into a scalar. We start by creating the table in Figure 2-28.

We use this table to create a column on the Data sheet. I have named the sheet with the pivot table PivotSheet. I add the new column to Data using the formula shown in Figure 2-29.

The VLOOKUP function replaces the Order Type with its average age. This column is linked to the pivot table so I need to copy it and paste special values to convert it to eliminate the formulas. The new column is added to the pivot table using the wizard. The new field, named S_OrderType, gives us a way to compare an office to the average performance for all offices with the mix of order types taken into account. The table in Figure 2-30 demonstrates this.

Figure 2-28. Order Type and average Order Age


Figure 2-29. Order type converted to a scalar value


Here we see St. Louis has an average order age of 30.55. But if St. Louis had average performance for all order types its average age would be 29.06. So, St. Louis is just a little worse than average. The best performer is Boston, on row 22. Its average age of 8.65 is far better than the average for its mix of order types.

This technique allows you to create a standard for comparing different categories, and it gives you a way to be fair when you set goals or measure performance.

Figure 2-30. Performance by city




Analyzing Business Data with Excel
Analyzing Business Data with Excel
ISBN: 0596100736
EAN: 2147483647
Year: 2006
Pages: 101
Authors: Gerald Knight

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net