43 Application of Software in Statistical Analysis – I Microsoft Excel

S. Gandhimathi

epgp books

 

 

Introduction

 

We can perform various statistical functions in excel. It starts with simple statistical functions such as the calculation of mean, median and mode to complex statistical function of probability distribution. The advanced statistical and econometric software’s such as SPSS, LIMDEP, E views etc, accept the data if it is in excel form. Excel is very convenient to enter the raw data and to perform very basic mathematical and statistical functions. Simple equations can be estimated through excel. The additional statistical function are added with the advanced version of excel. In this module we are going to discuss about various statistical functions and demonstrate selected functions with examples.

 

Objectives

 

In this module the following aspects are to be discussed

  1. To list out various statistical functions in excel
  2. To demonstrate selected statistical function in excel with example
  3. Statistical functions in Excel

The following are the important statistical functions in excel

Let us demonstrate some of the important statistical function with examples below.

 

4.Calculation of log values in excel Objective:

 

To find out the log value through excel for the data given.

 

Example

 

We can calculate log values in excel for the following data of family budget for annual household consumption expenditure

Procedure

  1. Open the excel sheet and enter the data in excel sheet.
  2. Enter the title as year in column A and budget in column B .
  3. Enter the formula in C2 cell as under = log(b2). Then copy this formula and paste it from c3 cell to c29 cellOrtrag the cell address of c2 cell up to c29 cell. The log values will be displayed for all the given values.
  1. Calculation of LN LOG Values

We can calculate the lnlog values in excel

 

Example

 

Calculate LN log values for the following data.

Objective

To find out LN log values for the data given through excel.

Procedure:

  1. Open the Excel data sheet, enter the data in Excel sheet.
  2. Enter the title year in A1celland value of raw cotton in B1 cell.
  3. Enter the year 1991, 1992 … in column A. Enter the value of raw cotton values in B column.
  4. Enter the formula in cell C2 as = LN (B2) Press enter.
  5. The above formula give the LN log value for value in B2 cell.
  6. To calculate the LN log values for the remaining values, copy the C2 cell address and paste it for the remaining cells. It will display the log values for the remaining values or trag the c2cell to the remaining cells.

6.Addition in excel

We can perform addition in excel

Sum up the cost of company A and company B in excel

Objective:

To find out total cost in excel for the given data.

Procedure:

  1. Open the Excel sheet
  2. Enter the title as cost of company A in cell A1 and cost of company B in cell B1
  3. Enter the value of cost of company A from cell A2 and cost of company B from B2 cell.
  4. Then enter the formula in cell C2 as = a2 + b2. Press enter.

The value will be displayed in cell C2. Then drag the cursor from C2 to C9 to get the other sum values or paste the cell address in the remaining cells

  1. Multiplication

Multiply quantity of output with price to find out the value of commodity.

Objective:

To find out the value of commodity by multiplying quantity and price in

Excel.

Procedure:

  1. Open the Excel sheet.
  2. Enter the title quantity in A1 cell and price in B1 cell.
  3. Enter the data on quantity from A2 cell and price from B2 cell.
  4. Enter the formula in cell C2 as = A2*B2 (Shift +*) Press enter.
  5. The value of commodity for remaining cell can be obtained by copying C2 cell and paste it in remaining cells.
  1. Division

Find out per capita income for the following data in excel

Procedure:

  1. Open the Excel sheet.
  2. Enter the title national income in cell A1 and values from A2 to A11.
  3. Enter the population in B1 and enter the values B2 to B11.
  4. Then enter formula in cell C2 as = A2/B2. Press enter.
  5. Then values will be displayed in cell C2. Then Drag from C2 to C11 to get other values.

Output

  1. Mean

Calculate the mean for the following data through Excel.

Objective:

 

To find out mean in excel for the given data.

 

Procedure:

  1. Open the Excel sheet
  2. Enter the title as Gross Domestic Product in cell A1 and data on Gross Domestic Product from cell A2
  3. Enter the following formula in cell C2 as = average (B2:B12). Press enter (or)Go to all formula à statistical à average.
  4. A dialogue box will be displayed asking the range of cell. In that give range as B2:B12. Press OK.
  5. The mean value will be displayed.

10.Median

Calculate the median for the following data through Excel

Objectives:

 

To find out median in excel for the given data.

 

Procedure:

  1. Open the Excel sheet
  2. Enter the title as Gross Domestic Product in cell A1
  3. Enter the data on Gross Domestic product from cell A2
  4. Enter the following formula in cell C2 as = average (B2:B12). Press enter OR
  5. Go to all formula à more function à statistical àmedian.
  6. A dialogue box will be displayed asking the range of cell. Enter the range as B2:B12. Press OK.
  7. The value will be displayed.
  1. Mode

Calculate the mode for the following data through Excel.

Meaning:

 

Mode is the frequently occurring value in a given set of data. The model value can be calculated by using the formula = Mode (range of cells). Press enter

 

Objective:

 

To find out the mode value in excel for given data.

 

Procedure:

  1. Open the Excel sheet
  2. Enter the title as Gross Domestic Product in cell A1
  3. Enter the data on gross domestic product from A2 cell in Excel sheet
  4. Enter the following formula in cell C2 as = average (B2:B12). Press enter (or)
  5. Go to all formula à more function à statistical àmode.
  6. A dialogue box will be displayed asking the range of cell. Enter the range as B2:B12. Press OK.
  7. The mode will be displayed.
  1. Mean Deviation

Meaning:

Mean deviation is called as average deviation. It is useful for finding the dispersion. Formula as = AVEDEN (Range of cells). Press Enter.

Objective:

To calculate the mean deviation in Excel for the given data.

 

Procedure:

  1. Open the Excel sheet.
  2. Enter the title as Gross Domestic Product in cell A1.
  3. Enter the data on gross domestic product from A2 cell.
  4. Enter the following formula tocalculate mean deviation =AVDEDV (A2:A12). Press enter.
  5. The output will be displayed in the cell, where the cursor is kept.

Result:

The mean deviation is calculated for the given data as 272.7273.

  1. Standard Deviation

Calculate standard deviation for the given data in Excel.

Objectives:

 

To calculate standard deviation for the given data in Excel.

 

Procedure:

  1. Enter the data on GDP in Excel.
  2. Enter the variable name as Gross Domestic Product in A2 cell.
  3. Calculate the standard deviation for the given data by using following formula: STEDEV (A2:A12). Press enter.

Various statistical analysis can be performed by using data analysis option in excel. Firstly data analysis in the tool bar must be created. The following are the steps in the creation of data analysis.

  1. Click micro soft excel office button
  2. Click excel option
  3. A dialogue box will be displayed. In the dialogue box, select Add ins, select analysis toolpak.

Click ok

 

Click data in the main menu

 

Now we can see the data analysis under data main menu

In the data analysis of excel, the following statistical analysis can be performed

  1. ANOVA
  2. Descriptive statistics
  3. t test
  4. Correlation
  5. Regression
  1. Descriptive Statistics in Excel
  1. Enter data in the excel sheet

15.‘t’ Test – Paired Two Samples

Estimate the paired sample ‘t’ test, independent sample ‘t’ test, for the following data.

 

Procedure:

 

Enter the data on sugar level before food  and after food in Excel worksheet.

Go to office buttonà click Excel option à select add in à select analysis toolpak. Click Go. Click OK. The data analysis option will be displayed in the main menu of data.

  • Click data analysis, select the ‘t’ test: Paired Two sample for Mean.
  • Click OK.
  • Give the variable one range as (A2:A8)
  • Give the variable two range as (B2:B8)
  • Give the output range as C1.
  • Click OK.

Output will be displayed in the given output range.

  1. t’ test: Assuming Equal Variance
  • Click data analysis, select the ‘t’ test assuming equal variance for menu.
  • Click OK.
  • Give the variable one range as (A2:A10)
  • Give the variable two range or (B2:B10).
  • Give output range F1
  • Click OK
  • Output will be displayed in the given output range.
  1. Correlation in Excel
  1. Enter the data on raw cotton in quantity and quality of cotton (Score values 5 very good, good, 3- Neither good nor bad 2- bad 1-very bad)
  2. Click data and click analysis
  3. Click correlation

Click ok. The output will be displayed in the specified output range

 

  1. Regression Analysis in Excel
  1. Enter the data in excel worksheet and click data and data analysis, select regression and click ok.
  1. A dialogue box will open. In the dialogue, select X input range as a2:b13 and Y input range as b2: b13. Select output range as new work sheet c2 .

Click ok. The output will be displayed in the specified output range.

19.Anova in Excel

  1. Open excel worksheet and enter the data on the availability of raw cotton in city A, B and C.
  2. Click data and click data analysis
  3. A dialogue box will open
  4. Select ANOVA Single factor
  5. Click OK
  1. A dialogue box will open. In the dialogue box, select input range as a2:c13 and output range as d2
  2. Select grouped by column and click ok.

The output will be displayed in the output file

In the dialogue box, select input and output range and click OK.

The output will be displayed in the output file

 

 

  1. Conclusion

 

Let us summarise, various statistical functions available in excel are listed. Entering of data for different analysis is more important. It must be given more attention. Selected statistical function such as the calculation of descriptive statistics, correlation, estimation of regression equation, ANOVA etc. are demonstrated with examples . This module may provide some idea to estimate various statistical functions in excel. Construction of charts in excel is not discussed as the scope of this module. Try to learn to construct charts in excel. More over all the available statistical functions are not demonstrated in this module. With some basic knowledge derived from this module, try for the remaining statistical function with real research data.

 

you can view video on Application of Software in Statistical Analysis – I Microsoft Excel

Web links

  • http://www.excelfunctions.net/Excel-Statistical-Functions.html
  • http://www.excel-easy.com/functions/statistical-functions.html