{"id":397,"date":"2019-08-02T08:52:05","date_gmt":"2019-08-02T08:52:05","guid":{"rendered":"http:\/\/hsp16.epgpbooks.inflibnet.ac.in\/?post_type=chapter&p=397"},"modified":"2019-08-05T05:24:10","modified_gmt":"2019-08-05T05:24:10","slug":"application-of-software-in-statistical-analysis-i-microsoft-excel","status":"publish","type":"chapter","link":"https:\/\/ebooks.inflibnet.ac.in\/hsp16\/chapter\/application-of-software-in-statistical-analysis-i-microsoft-excel\/","title":{"rendered":"Application of Software in Statistical Analysis \u2013 I Microsoft Excel"},"content":{"raw":"
\"epgp<\/a>\r\n<\/span><\/div>\r\n \r\n\r\n \r\n

Introduction<\/strong><\/p>\r\n \r\n

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\u2019s 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.<\/p>\r\n \r\n

Objectives<\/strong><\/p>\r\n \r\n

In this module the following aspects are to be discussed<\/p>\r\n\r\n

    \r\n \t
  1. To list out various statistical functions in excel<\/li>\r\n \t
  2. To demonstrate selected statistical function in excel with example<\/li>\r\n \t
  3. Statistical functions in Excel<\/strong><\/li>\r\n<\/ol>\r\n

    The following are the important statistical functions in excel<\/p>\r\n\"\"<\/a>\r\n\r\n\"\"<\/a>\r\n\r\n\"\"<\/a>\r\n\r\n\"\"<\/a>\r\n

    Let us demonstrate some of the important statistical function with examples below.<\/strong><\/p>\r\n \r\n

    4.Calculation of log values in excel <\/strong>Objective:<\/strong><\/p>\r\n \r\n

    To find out the log value through excel for the data given.<\/p>\r\n \r\n

    Example<\/strong><\/p>\r\n \r\n

    We can calculate log values in excel for the following data of family budget for annual household consumption expenditure<\/p>\r\n\"\"<\/a>\r\n\r\nProcedure<\/strong>\r\n

      \r\n \t
    1. Open the excel sheet and enter the data in excel sheet.<\/li>\r\n \t
    2. Enter the title as year in column A and budget in column B .<\/li>\r\n \t
    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 <\/span>all the given values.<\/span><\/li>\r\n<\/ol>\r\n\"\"<\/a>\r\n
        \r\n \t
      1. Calculation of LN LOG Values<\/strong><\/li>\r\n<\/ol>\r\n

        We can calculate the lnlog values in excel<\/p>\r\n \r\n\r\nExample<\/strong>\r\n\r\n \r\n

        Calculate LN log values for the following data.<\/p>\r\n\"\"<\/a>\r\n

        \r\n

        Objective<\/strong><\/p>\r\n

        To find out LN log values for the data given through excel.<\/p>\r\n\r\n<\/div>\r\n

        Procedure:<\/strong><\/p>\r\n\r\n

          \r\n \t
        1. Open the Excel data sheet, enter the data in Excel sheet.<\/li>\r\n \t
        2. Enter the title year in A1celland value of raw cotton in B1 cell.<\/li>\r\n \t
        3. Enter the year 1991, 1992 \u2026 in column A. Enter the value of raw cotton values in B column.<\/li>\r\n \t
        4. Enter the formula in cell C2 as = LN (B2) Press enter.<\/li>\r\n \t
        5. The above formula give the LN log value for value in B2 cell.<\/li>\r\n \t
        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.<\/li>\r\n<\/ol>\r\n\"\"<\/a>\r\n\r\n\"\"<\/a>\r\n

          6.Addition in excel<\/strong><\/p>\r\n

          We can perform addition in excel<\/p>\r\n

          Sum up the cost of company A and company B in excel<\/p>\r\n\"\"<\/a>\r\n

          Objective:<\/strong><\/p>\r\n

          To find out total cost in excel for the given data.<\/p>\r\n

          Procedure:<\/strong><\/p>\r\n\r\n

            \r\n \t
          1. Open the Excel sheet<\/li>\r\n \t
          2. Enter the title as cost of company A in cell A1 and cost of company B in cell B1<\/li>\r\n \t
          3. Enter the value of cost of company A from cell A2 and cost of company B from B2 cell.<\/li>\r\n \t
          4. Then enter the formula in cell C2 as = a2 + b2. Press enter.<\/li>\r\n<\/ol>\r\n

            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<\/p>\r\n\"\"<\/a>\r\n\r\n\"\"<\/a>\r\n

              \r\n \t
            1. Multiplication<\/strong><\/li>\r\n<\/ol>\r\n

              Multiply quantity of output with price to find out the value of commodity.<\/p>\r\n\"\"<\/a>\r\n

              Objective:<\/strong><\/p>\r\n

              To find out the value of commodity by multiplying quantity and price in<\/p>\r\n

              Excel.<\/p>\r\n

              Procedure:<\/strong><\/p>\r\n\r\n

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

                  Find out per capita income for the following data in excel<\/strong><\/p>\r\n\"\"<\/a>\r\n\r\nProcedure:<\/strong>\r\n

                    \r\n \t
                  1. Open the Excel sheet.<\/li>\r\n \t
                  2. Enter the title national income in cell A1 and values from A2 to A11.<\/li>\r\n \t
                  3. Enter the population in B1 and enter the values B2 to B11.<\/li>\r\n \t
                  4. Then enter formula in cell C2 as = A2\/B2. Press enter.<\/li>\r\n \t
                  5. Then values will be displayed in cell C2. Then Drag from C2 to C11 to get other values.<\/li>\r\n<\/ol>\r\n\"\"<\/a>\r\n\r\nOutput<\/strong>\r\n\r\n\"\"<\/a>\r\n
                      \r\n \t
                    1. Mean<\/strong><\/li>\r\n<\/ol>\r\n

                      Calculate the mean for the following data through Excel.<\/p>\r\n\"\"<\/a>\r\n\r\nObjective:<\/strong>\r\n\r\n \r\n

                      To find out mean in excel for the given data.<\/p>\r\n \r\n\r\nProcedure:<\/strong>\r\n

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

                        10.Median<\/strong><\/p>\r\n

                        Calculate the median for the following data through Excel<\/p>\r\n\"\"<\/a>\r\n\r\nObjectives:<\/strong>\r\n\r\n \r\n\r\nTo find out median in excel for the given data.\r\n\r\n \r\n\r\nProcedure:<\/strong>\r\n

                          \r\n \t
                        1. Open the Excel sheet<\/li>\r\n \t
                        2. Enter the title as Gross Domestic Product in cell A1<\/li>\r\n \t
                        3. Enter the data on Gross Domestic product from cell A2<\/li>\r\n \t
                        4. Enter the following formula in cell C2 as = average (B2:B12). Press enter OR<\/li>\r\n \t
                        5. Go to all formula \u00e0 more function \u00e0 statistical \u00e0median.<\/li>\r\n \t
                        6. A dialogue box will be displayed asking the range of cell. Enter the range as B2:B12. Press OK.<\/li>\r\n \t
                        7. The value will be displayed.<\/li>\r\n<\/ol>\r\n\"\"<\/a>\r\n\r\n\"\"<\/a>\r\n
                            \r\n \t
                          1. Mode<\/strong><\/li>\r\n<\/ol>\r\n

                            Calculate the mode for the following data through Excel.<\/p>\r\n\"\"<\/a>\r\n\r\nMeaning:<\/strong>\r\n\r\n \r\n

                            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<\/p>\r\n \r\n\r\nObjective:<\/strong>\r\n\r\n \r\n

                            To find out the mode value in excel for given data.<\/p>\r\n \r\n

                            Procedure:<\/strong><\/p>\r\n\r\n

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

                                Meaning:<\/strong><\/p>\r\n

                                Mean deviation is called as average deviation. It is useful for finding the dispersion. Formula as = AVEDEN (Range of cells). Press Enter.<\/p>\r\n

                                Objective:<\/strong><\/p>\r\n

                                To calculate the mean deviation in Excel for the given data.<\/p>\r\n \r\n\r\nProcedure:<\/strong>\r\n

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

                                  Result:<\/p>\r\n

                                  The mean deviation is calculated for the given data as 272.7273.<\/p>\r\n\r\n

                                    \r\n \t
                                  1. Standard Deviation<\/strong><\/li>\r\n<\/ol>\r\n

                                    Calculate standard deviation for the given data in Excel.<\/p>\r\n\"\"<\/a>\r\n\r\nObjectives:<\/strong>\r\n\r\n \r\n

                                    To calculate standard deviation for the given data in Excel.<\/p>\r\n \r\n\r\nProcedure:<\/strong>\r\n

                                      \r\n \t
                                    1. Enter the data on GDP in Excel.<\/li>\r\n \t
                                    2. Enter the variable name as Gross Domestic Product in A2 cell.<\/li>\r\n \t
                                    3. Calculate the standard deviation for the given data by using following formula: STEDEV (A2:A12). Press enter.<\/li>\r\n<\/ol>\r\n\"\"<\/a>\r\n\r\n\"\"<\/a>\r\n

                                      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.<\/p>\r\n\r\n

                                        \r\n \t
                                      1. Click micro soft excel office button<\/li>\r\n \t
                                      2. Click excel option<\/li>\r\n \t
                                      3. A dialogue box will be displayed. In the dialogue box, select Add ins, select analysis toolpak.<\/li>\r\n<\/ol>\r\n\"\"<\/a>\r\n\r\n\"\"<\/a>\r\n\r\nClick ok\r\n\r\n \r\n\r\nClick data in the main menu\r\n\r\n \r\n\r\nNow we can see the data analysis under data main menu\r\n\r\n\"\"<\/a>\r\n

                                        In the data analysis of excel, the following statistical analysis can be performed<\/p>\r\n\r\n

                                          \r\n \t
                                        1. ANOVA<\/li>\r\n \t
                                        2. Descriptive statistics<\/li>\r\n \t
                                        3. t test<\/li>\r\n \t
                                        4. Correlation<\/li>\r\n \t
                                        5. Regression<\/li>\r\n<\/ol>\r\n
                                            \r\n \t
                                          1. Descriptive Statistics in Excel<\/strong><\/li>\r\n<\/ol>\r\n
                                              \r\n \t
                                            1. Enter data in the excel sheet<\/li>\r\n<\/ol>\r\n\"\"<\/a>\r\n\r\n\"\"<\/a>\r\n\r\n\"\"<\/a>\r\n

                                              15.\u2018t\u2019 Test \u2013 Paired Two Samples<\/strong><\/p>\r\n

                                              Estimate the paired sample \u2018t\u2019 test, independent sample \u2018t\u2019 test, for the following data.<\/p>\r\n \r\n\r\n\"\"<\/a>\r\n\r\nProcedure:<\/strong>\r\n\r\n \r\n

                                              Enter the data on sugar level before food\u00a0 and after food in Excel worksheet.<\/p>\r\n

                                              Go to office button\u00e0 click Excel option \u00e0 select add in \u00e0 select analysis toolpak. Click Go. Click OK. The data analysis option will be displayed in the main menu of data.<\/p>\r\n\r\n