Pivot Table Analysis Apdata

Summarization and Analyzing the data with Pivot Tables M.N. Reddy, D.R.Rao, K.V. Kumar National Academy of Agricultural Research Management, Hyderabad The data given in Microsoft Excel Worksheet shows the Andhra Pradesh District wise data on Rice Area, Production, Yield, Total Rice Area Irrigated, Nitrogen consumption per ha during the period from 1970 to 2000. Calculate mean, Standard deviation, C.V. (%), Growth rates for each parameter and depict them on Andhra Pradesh Map with appropriate formulas and draw your inferences. Procedure calculating mean, Standard deviation, C.V. (%), Growth rates for Rice Yield Getting Data for Rice Yield Analyzing Data with Pivot Tables Excel provides many useful data analysis tools. One of the interesting, yet probably least used and understood is the pivot table. A pivot table is a tool that provides a different way of looking at your data. It provides a dynamic summary of data that it contained in a database. Using this Pivot Table one can summarize the categorical data very easily and efficiently. In this exercise you’ll learn to create a PivotTable report by using the PivotTable and PivotChart Wizard. What Types of Data You Can Use Excel data list or database External data source The external databases can also be used to prepare this report. Multiple consolidation ranges: If the data lists are available in different worksheets, then by using the option “consolidate” all the data ranges t can be used for Pivot table report. Another PivotTable report: It is useful when, already a pivot table is created, and the same data to be used to create multiple PivotTable reports. Data Preparation There are a few things to consider in preparing your Excel data: Data should be in categorical: In the data list, there should be more than one categorical variable (Institute, Head of Account, and Years etc. I the following example) and at least one variable for summarize (Example: Plan, Non-Plan, and Total etc.) Remove all automatic totals: If any formulas are existing in the data list, convert them into values. Otherwise it will give wrong results Query Data: If you want to include filtered data, use only the Advanced Filter command (Data – Filter- Advanced Filter). With this option the filtered data will be kept in different location. That filtered data only be used to generate Pivot table report For the given data using Pivot table and pivot chart wizard generate the following reports

142

1. 2. 3. 4. 5. 6. 7. 8.

Generate a table: Rows: District: Columns: Years and Data: Rice Yield Generate a table: Rows: States: Columns: Years: and Data: Production Generate a table: Rows: States: Columns: Years: and Data: Area Generate a table: Rows: States: Columns: Years: and Data: Fertilizer N Consumption Generate a table: Rows: States: Columns: Years: and Data: Fertilizer P Consumption Generate a table: Rows: States: Columns: Years: and Data: Fertilizer K Consumption Generate a table: Rows: States: Columns: Years: and Data: Total Rice Area Irrigated Generate a table: Rows: States: Columns: Years: and Data: Farmers Harvest Prices

Activate Microsoft Excel.  Click on Start  Click on Programs  Click on Microsoft Excel Open the Data File     

Select OPEN option from the FILE Menu Browse for the File APDATA.XLS from the DATA Folder Click on OPEN Observe that the data is starting from Cell A1 to Cell 683 Name this worksheet as SOURCE DATA

Copy the Worksheet     

Select Entire worksheet Click on COPY Icon Click on SHEET2 Tab Position the cursor in cell A1 Click on PASTE Icon

Preparing Pivot table  Position the Mouse pointer in A1 Cell  Click on Data Menu  Click on Pivot Table and Pivot Chart Option. Then it will display Pivot table report wizard.  Select Microsoft Excel List or Database option as for “ Where is the data you want to Analyze “  Select Pivot Chart (With table) as the option for “ What kind of report do you want to create”  Click Next to go to next step of the wizard  In the Next window, it is automatically selected the entire data range in the worksheet  Click Next to go to next step of the wizard  Click on Layout option to design required report  In the Layout window, user has to select the Categorical variables for Rows, and Column, and also select the variable for Data summarize. In this example

143

Preparing report for Rice Yield  Click on the field YEAR, drag and drop in the area named as COLUMN in the Layout Window  Click on the field DISTNAME, drag and drop in the area named as ROWS in the Layout Window  Click on the field Rice_TY, drag and drop in the area named as DATA in the Layout Window  Double click on the Variable Rice_TY in the Data area and select AVERAGE as the summarized function.  Click OK  Select the option NEW WORKSHEET to keep the results in the separate worksheet.  Click on FINISH to execute the required report  The report is displayed in the new worksheet  Name this worksheet as YIELD Calculation of Averages, Standard Deviation and Coefficient of Variation 1. 2. 3. 4. 5. 6. 7. 8.

Select entire worksheet Click on COPY Icon Click on SHEET3 tab Position the cursor in Cell A1 Select PASTE SPECIAL from the EDIT Menu Select VALUES as the option for PASTE Click OK to convert all formulas into Values Name this sheet as CV

Calculation of Averages 9. 10. 11. 12.

Position the cursor in Cell AG1 Type the Column heading as MEAN Position the cursor in Cell AG2 Type the following formula to Calculate the Average =AVERAGE (B2:AF2)

13. Position the cursor on AG2 and click on COPY Icon 14. Block the cells from AG3 to AG23 and click on PASTE Icon to fill the values by using the above formula AG2 Calculation of Standard Deviation 15. 16. 17. 18.

Position the cursor in Cell AH1 Type the Column heading as STANDARD DEVIATION Position the cursor in Cell AH2 Type the following formula to Calculate the Standard Deviation =STDEV (B2:AF2)

19. Position the cursor on AH2 and click on COPY Icon Block the cells from AH3 to AH23 and click on PASTE Icon to fill the values by using the above formula AH2.

144

Calculation of Coefficient Variation (%) 20. 21. 22. 23.

Position the cursor in Cell AI1 Type the Column heading as CV (%) Position the cursor in Cell AI2 Type the following formula to Calculate the CV = AH2/AG2 * 100

24. Fill the cell from AI3 to AI23 using the COPY Icon from the Cell AI2 Calculation of State wise Growth rates 1. 2. 3. 4. 5. 6. 7.

Block the entire data from Cell A1 to AF1 from the CV Worksheet Click on COPY Icon. Position the cursor in Cell A1 Select PASTE SPECIAL from the EDIT Menu Select VALUES as the option for PASTE Click OK to convert all formulas into Values Name this sheet as Growth Rates

Converting into Natural Logarithmic values 8. 9. 10. 11. 12.

Block the data from Cell A1 to AF1 Place the Pointer in Cell A25 and Click on PASTE Icon to copy the column headings. Block the data from Cell A2 to A23 Place the Pointer in Cell A26 and Click on PASTE Icon to copy the Row headings Position the cursor in Cell B26 and type the formula to convert the Value in Cell B2 into Logarithmic values. =LN (B2)

13. 14. 15. 16. 17.

Copy the formula in Cell B26 to Cells from Cells C26 to AF26 Similarly copy the same formula to other cells i.e Cell B27 to A47 Position cursor in Cell AJ25 Type the Column head as SLOPE Position the cursor in Cell AJ26 and type the formula as shown below = SLOPE (B26:AF26, $B$25: $AF$25)

18. 19. 20. 21.

Copy the cell AJ26 to AJ27 to AJ47 Position cursor in Cell AK25 Type the Column head as Growth Rates Position the cursor in Cell AK26 and type the formula as shown below =(EXP (AJ26)-1)*100

22. Copy the cell AK26 to AK27 to AK47 Arranging the data for Mapping 1. 2. 3. 4.

Block the cells from A1 to A23 Click on COPY Icon Click on Sheet5 Position the Cursor in Cell A1 and Click on PASTE Icon

145

5. Similarly COPY the data of MEAN, Standard Deviation, CV and Growth Rates from respective columns from the Growth Rates Sheet 6. Name this Sheet as Summary Classify the Mean, as shown below Mean

Code

< 1500 1500.1750 1750.2000 2000.2250 2250.2500 > 2500

1 2 3 4 5 6

7. To get the above classification use the following formula in cell F2 =IF (B2<=1500,1,IF (IF(B2<2500,5,6)))))))

(B2<1750,2,

(IF

(B2<2000,3,IF

(B2<2250,4,

8. Copy the same formula from Cell F2 to Cells F3 to F23 9. Classification for C.V (%) and Growth Rate (%) as per the following codes C.V (%) <15 15.25 25.35 > 35

Code 1 2 3 4

Growth rate (%) <1 1.2 2.3 >3

Code 1 2 3 4

Depict the Classifications OF Mean, C.V (%) and Growth Rate (%) on Map

146