VLC Home
  Course Home
   
--  MS EXCEL CASE STUDY
-

Problem
-

Procedure
- Data Preparation
- Calculation of crop productivity, convert the data of "Total land holding", "Soil depth " amenable to analysis
- Summarize the data with suitable statistical measures and frequency distribution
- Regression Analysis
- View data using database features
- Represent the data through appropriate graphs and tables
   
 
Self Test
Ask a Question
Download PDF File


Calculation of crop productivity, convert the data of "Total land holding", "Soil depth " amenable to analysis

Calculation of Productivity

  1. Position mouse pointer on Cell F1
  2. Select COLUMN option from the INSERT Menu. Then it will insert one empty column
  3. Type CROP YIELD (Kg/ha) as the Column Heading in Cell F1
  4. Type the formula as shown below in Cell F2 = E2/D2*100
  5. Position the mouse pointer in Cell F2
  6. Copy the Same formula from Cell F2 to F3:F51
  7. Click on SAVE Icon to save this workbook

Convert the Formula into Values

  1. Select the data Range from F2:F51
  2. Click on Copy Icon
  3. Select PASTE SPECIAL Icon from the EDIT Menu
  4. Select VALUES options from the displayed PASTE SPECIAL Dialogue Box.
  5. Click OK
  6. Increase the column width and formatting cells for two decimals if needed.

Convert Soil Depth Values into Codes

Soil Depth                   Soil Type
    45                          Medium                  
    90                          Deep

  1. Position mouse pointer on Cell M1
  2. Select COLUMN option from the INSERT Menu. Then it will insert one empty column
  3. Type SOIL TYPE as the Column Heading in Cell M1
  4. Type the formula as shown below in Cell M2 =IF(L2=45,"Medium","Deep")
  5. Position the mouse pointer in Cell M2
  6. Copy the Same formula from Cell M2 to M3:M51
  7. Similarly Convert this data in Soil Type column into Soil as given below

    Soil Type                  Soil Code
       Medium                         1
       Deep                            2

Convert the data in the column M and N into values.

Arrange the data which* contains only text ( i.e. Farmers Category and Soil Type) at one side, so that numerical data will be available in continuous columns. For this

  1. Position the Mouse pointer in cell F1.
  2. Hold the Shift key and Block the data from cell F1 to F51
  3. Hold the CTRL Key and position the mouse pointer in L1 and Block the cells from L1 : L51 as did in the previous step. With this operation both columns are selected at a time.
  4. Click on COPY Icon
  5. Position the Mouse pointer in cell D1 and Click on PASTE Icon Block the Then these two columns pasted in Columns D and E.
  6. Now delete the Columns H and M
  7. Copy the Entire Data in New Worksheet as did earlier and rename the new worksheet as Descriptive