# Question: Building on the previous problem another pivot table element we

Building on the previous problem, another pivot table element we didn’t explain is a calculated field. This is usually a new numerical variable built from numerical variables that can be summarized in the Values area. It acts somewhat like a new column in the spreadsheet data, but there is an important difference. Again, it is easiest to learn from an example. Open the file Elecmart Sales.xlsx and follow the instructions below.

a. Create a new column in the data, Cost Per Item, which is Total Cost divided by Items Ordered.

Then create a pivot table and find the average of Cost Per Item, broken down by Region. You should find averages such as $50.41 for the MidWest. Explain exactly how this value was calculated. Would such an average be of much interest to a manager at Elecmart? Why or why not?

b. Select any average in the pivot table and then select Calculated Field from the Formulas dropdown list on the PivotTable Tools Options ribbon. This will open a dialog box. Enter CF_CostPerItem in the name box enter the formula = TotalCost/ItemsOrdered, and click on OK. You should now see a new column in the pivot table, Sum of CF_CostPerItem, with different values than in the Average of CostPerItem column. For example, the new value for the MidWest should be $46.47. Do some investigation to understand how this “sum” was calculated. From a manager’s point of view, does it make any sense?

a. Create a new column in the data, Cost Per Item, which is Total Cost divided by Items Ordered.

Then create a pivot table and find the average of Cost Per Item, broken down by Region. You should find averages such as $50.41 for the MidWest. Explain exactly how this value was calculated. Would such an average be of much interest to a manager at Elecmart? Why or why not?

b. Select any average in the pivot table and then select Calculated Field from the Formulas dropdown list on the PivotTable Tools Options ribbon. This will open a dialog box. Enter CF_CostPerItem in the name box enter the formula = TotalCost/ItemsOrdered, and click on OK. You should now see a new column in the pivot table, Sum of CF_CostPerItem, with different values than in the Average of CostPerItem column. For example, the new value for the MidWest should be $46.47. Do some investigation to understand how this “sum” was calculated. From a manager’s point of view, does it make any sense?

## Relevant Questions

The file S02_18.xlsx contains daily values of the S&P Index from 1970 to 2009. It also contains percentage changes in the index from each day to the next. Create a pivot table with average of % Change in the Values area and ...The file S03_22.xlsx lists financial data on movies released since 1980 with budgets at least $20 million.a. Create three new variables, Ratio1, Ratio2, and Decade. Ratio1 should be US Gross divided by Budget, Ratio2 should ...The file S03_56.xlsx lists the average salary for each NBA team from 2004 to 2009, along with the number of team wins each of these years. Answer the same questions as in the previous problem for this basketball data.The file S03_64.xlsx lists monthly data since 1950 on the well-known Dow Jones Industrial Average (DJIA), as well as the less well-known Dow Jones Transportation Average (DJTA) and Dow Jones Utilities Average (DJUA). Each of ...Suppose that 18% of the employees of a given corporation engage in physical exercise activities during the lunch hour. Moreover, assume that 57% of all employees are male, and 12% of all employees are males who engage in ...Post your question