Download Using Excel for Statistical Analysis: Essential Tools and Techniques and more Study notes Statistics in PDF only on Docsity!
Using Excel for Sta�s�cal Analysis
MS Excel is one of the most commonly used tools for data analysis. The convenience of use and cost are two very important reasons why most data professionals prefer using Excel for sta�s�cal data analysis. However, using Excel for sta�s�cal analysis requires clarity of thought, data analysis knowledge, and strong decision-making skills. Whether you are performing sta�s�cal analysis using Excel 2010 or Excel 2013, you need to have a clear understanding of charts and pivot tables. Most data analysts using Excel for sta�s�cal analysis depend largely on these two Excel features. Having knowledge of the essen�al sta�s�cs for data analysis using Excel answers is a plus.
Data Analy�cs Excel Course
Remember to install Data Analysis ToolPak if you are using Excel for sta�s�cal data analysis. In this discussion, we explain in details the essen�al sta�s�cs for data analysis using Excel and how to perform descrip�ve analysis using Excel. In this blog, I have tried to explore the func�onali�es of MS-Excel as a poten�al tool for sta�s�cal analysis and suggested some simple tricks and techniques that will save both �me and energy.
Using Excel for Sta�s�cal Analysis: Pivot Tables
A PivotTable is an Excel tool for summarizing a list into a simple format. It helps you analyze all the data in your worksheet so as to make be�er business decisions. Excel can help you by recommending, and then, automa�cally crea�ng PivotTables, which are a great way to summarize, analyze, explore, and present your data.
pivot-table
A pivot table may be used as an interac�ve data summariza�on tool to automa�cally condense large datasets into a separate, concise table. You can use it to create an informa�ve summary of a large dataset or make regional comparisons between brand sales.
pivot-table
You can create PivotTables from lists, as you define which fields should be arranged in columns, which fields should become rows, and what data you wish to summarize.
Using Excel for Sta�s�cal Analysis: Descrip�ve Sta�s�cs
Descrip�ve Sta�s�cs tool in the Data Analysis add-in can be used on an exis�ng data set to get up to 16 different descrip�ve sta�s�cs, without having to enter a single func�on on the worksheet. Descrip�ve Sta�s�cs gives you a general idea of trends in your data including:
The mean, mode, median and range
Variance and standard devia�on
Skewness
Sample Variance
Kurtosis and Skewness
Count, maximum and minimum
Descrip�ve_Sta�s�cs
Descrip�ve_Sta�s�cs
Descrip�ve Sta�s�cs is useful because it allows you to take a large amount of data and summarize it. For example, you may want to represent the incomes of a community. Instead of showing it on an excel, you may summarize it, it becomes useful: an average wage, or a median income, is much easier to understand and then analyze the data.
Descrip�ve_Sta�s�cs
You can find descrip�ve analysis by going to Excel→ Data→ Data Analysis → Descrip�ve sta�s�cs. It is the most basic set of analysis that can be performed on any data set.
Data Analy�cs Course by Digital Vidya
Using Excel for Sta�s�cal Analysis: ANOVA (Analysis of Variance)
Analysis of variance (ANOVA) is a sta�s�cal technique that is used to check if the means of two or more groups are significantly different from each other. ANOVA checks the impact of one or more factors by comparing the means of different samples.
ANOVA_intro
Rank_ And _Percen�le
In the le� table, we have our data on the revenues of different products. And we want to rank this data of products based on their revenue. With the help of rank and percen�le, we can get the table shown on the right. You can observe that now the data is sorted and respec�ve rank is also marked with each data.
Percen�le shows the category in which the data belongs, such as top 50%, top 30% etc. In the summary table, the rank of product 7 is 4. As the total number of data is 7, we can easily say that it belongs to the top 50% of the data.
Using Excel for Sta�s�cal Analysis: Regression
Regression is one of one of the best features in Excel. It is widely used for using Excel for sta�s�cal data analysis. Regression is a process of establishing a rela�onship among many variables; to establish a rela�onship between dependent variables and independent variables. Regression is great for use for using Excel for sta�s�cal data analysis. You, may, for example, want to see if there is an increase in the revenue of product, which is not due to increase in the adver�sement.
Regression_Analysis
If you performing sta�s�cal analysis using Excel 2010, Regression Analysis is the best way of mathema�cally sor�ng out which of those variables does indeed have an impact. It answers the ques�ons: Which factors ma�er most? Which can we ignore? How do those factors interact with each other? And, perhaps most importantly, how certain are we about all of these factors?
Regression Analysis
These factors are more commonly known as variables. You may have your dependent or independent variables. In order to conduct a regression analysis, you gather the data on the variables in ques�on. You may take all of your monthly sales numbers, the past five years and any data on the independent variables you may find useful. You may, for example, find out the average monthly rainfall for the past five years as well.
Using Excel for Sta�s�cal Analysis: Random Number Generator
If you are using Excel for sta�s�cal data analysis, on a regular basis, Random Number Generator must be your top choice for genera�ng a series of random numbers. This simple func�on in Excel gives you more flexibility in the random number genera�on process. It gives you more control over the generated data.
Random_number_generator_Excel
A random number is one that is drawn from a set of possible values, each of which is equally probable. In sta�s�cs, this is called a uniform distribu�on, because the distribu�on of probabili�es for each number is uniform (i.e., the same) across the range of possible values. For example, a good (unloaded) die has the probability 1/6 of rolling a one, 1/6 of rolling a two and so on. Hence, the probability of each of the six numbers coming up is exactly the same, so we say any roll of our die has a uniform distribu�on. When discussing a sequence of random numbers, each number drawn must be sta�s�cally independent of the others. This means that drawing one value doesn’t make that value less likely to occur again. This is exactly the case with our unloaded die: If you roll a six, that doesn’t mean the chance of rolling another six changes.
Random number generator Excel
Random number generator Excel
Two very essen�al sta�s�cs for data analysis using Excel:
The func�on RANDBETWEEN returns a random integer number
The func�on RAND () returns a random real number of a uniform distribu�on. It will be less than 1 and greater than or equal to 0.
Using Excel for Sta�s�cal Analysis: Sampling
Sampling is one of the most readily preferred Excel tools if you are using Excel for sta�s�cal data analysis. This op�on is used for crea�ng samples from a huge popula�on. You can randomly select data from the dataset or select every nth item from the set. For example, if you may want to measure the effec�veness of female call center employee in a call center, you can use this tool to randomly select few data every month and listen to their recorded calls and give a ra�ng based on the selected call.
