Chapter 8_M2

WORKING WITH FORMULAS AND FUNCTIONS CHAPTER - 8: USING FORMULAS FOR STATISTICAL ANALYSIS Structure 1.0 Learning Objectives 1.1 Introduction 1.2 Tests performed using Excel statistical Analysis 1.3 Summary 1.4 References 1.0 Learning Objectives After studying this unit, you will be able to:  Explain Statistical Analysis?  Explain the Varieties Tests performed 1.1 Introduction You can perform statistical analysis with the help of Excel. It is used by most of the data scientists who require the understanding of statistical concepts and behaviour of the data. Before starting, you need to check whether Excel Analysis ToolPak is enabled in Excel or not (it is an add-in provided by Microsoft Excel). To check whether it is enabled or not, go to Excel → Data and check whether data analysis option is there or not on the top right corner. If it is not there, go to Excel → File → Options → Add-in and enable the Analysis ToolPak by selecting the Excel Add-ins option in manage tab and then, click GO. This will open a small window; select the Analysis ToolPak option and enable it. Page 41 of 63 All Rights Reserved. Vol. TLE001/03-2022

WORKING WITH FORMULAS AND FUNCTIONS 1.2 Tests performed Using Excel Statistical Analysis  Descriptive Analysis  You can find descriptive analysis by going to Excel→ Data→ Data Analysis → Descriptive statistics.  It is the most basic set of analysis that can be performed on any data set. It gives you the general behaviour and pattern of the data.  It is helpful when you a have a set of data and want to have the summary of that dataset.  This will show the following statistic data for the chosen dataset. - Mean, Standard error and Median - Median, Mode and Standard Deviation - Sample Variance - Kurtosis and Skewness - Range, Minimum, Maximum, Sum and Count  ANOVA (Analysis Of Variance)  It is a data analysis method which shows whether the mean of two or more data set is significantly different from each other or not. In other words, it analyses two or more groups simultaneously and finds out whether any relationship is there among the groups of data set or not. For example, you can use ANOVA if you want to analyse the traffic of three different cities and find out which one is more efficient in handling the traffic (or if there are no significant differences among the traffic).  You will find three types of ANOVA in the Excel - ANOVA single factor - ANOVA two factor with replication - ANOVA two factor without replication Page 42 of 63 All Rights Reserved. Vol. TLE001/03-2022

WORKING WITH FORMULAS AND FUNCTIONS  If you have three groups of datasets and want to check whether there is any significant difference between these groups or not, you can use ANOVA single factor.  If the P-value in the ANOVA summary table is greater than 0.05, you can say that there is a significant difference between the groups.  Moving Average  Moving average is usually applicable for time series data such as stock price, weather report, attendance in class etc.  For example, it is heavily used in stock price as a technical indicator. If you want to predict the stock price of today, the last ten days data would be more relevant than the last 1 year. So, you can plot the moving average of the stock having a 10-day time period and you can then predict the price to some extent.  The same applies to the temperature of a city. The recent temperature of a city can be calculated by taking the average of last few weeks rather than previous months.  Rank and Percentile  It calculates the ranking and percentile in the data set. For example, if you are managing a business of several products and want to find out which product is contributing to a higher revenue, you can use this rank method in Excel.  Regression  Regression is a process of establishing a relationship among many variables.  Usually, we establish a relationship between dependent variables and independent variables.  For example, cases when you want to see if there is any increase in the revenue of product, which is not due to increase in the advertisement. Page 43 of 63 All Rights Reserved. Vol. TLE001/03-2022

WORKING WITH FORMULAS AND FUNCTIONS  Random Number Generator  Although you can find a simple function to generate a series of random numbers, this option in data analysis gives you more flexibility in the random number generation process. It gives us more control over the generated data.  Sampling  This option is the data analysis tool which is used for creating samples from a huge population.  You can randomly select data from the dataset or select every nth item from the set.  For example, if you want to measure the effectiveness of a call centre employee in a call centre, you can use this tool to randomly select few data every month and listen to their recorded calls and give a rating based on the selected call. 1.3 Summary  You can perform statistical analysis with the help of Excel  It is used by most of the data scientists who require the understanding of statistical concepts and behaviour of the data.  There are various tests mentioned in this chapter that requires excel to perform Statistical Analysis 1.4 References https://www.geeksforgeeks.org/statistical-functions-in-excel-with- examples/ https://magoosh.com/excel/using-excel-statistical-analysis/ https://support.microsoft.com/en-us/office/statistical-functions- reference-624dac86-a375-4435-bc25-76d659719ffd https://openoregon.pressbooks.pub/beginningexcel/chapter/2-2- statistical-functions/ Page 44 of 63 All Rights Reserved. Vol. TLE001/03-2022