Docsity
Docsity

Prepare for your exams
Prepare for your exams

Study with the several resources on Docsity


Earn points to download
Earn points to download

Earn points by helping other students or get them with a premium plan


Guidelines and tips
Guidelines and tips

Using Excel for Statistical Analysis: Essential Tools and Techniques, Study notes of Statistics

An overview of using microsoft excel for statistical analysis, focusing on the essential tools and techniques such as pivottables, descriptive statistics, anova, moving average, rank and percentile, regression, and random number generator. It also mentions the availability of a data analysis course by digital vidya.

Typology: Study notes

2018/2019

Uploaded on 04/29/2019

naveeng123
naveeng123 🇺🇸

4

(1)

3 documents

1 / 6

Toggle sidebar

This page cannot be seen from the preview

Don't miss anything!

bg1
Using Excel for Stascal 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 stascal
data analysis. However, using Excel for stascal analysis requires clarity of thought, data
analysis knowledge, and strong decision-making skills. Whether you are performing stascal
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 stascal analysis depend largely on these two
Excel features. Having knowledge of the essenal stascs for data analysis using Excel answers
is a plus.
Data Analycs Excel Course
Remember to install Data Analysis ToolPak if you are using Excel for stascal data analysis. In
this discussion, we explain in details the essenal stascs for data analysis using Excel and how
to perform descripve analysis using Excel. In this blog, I have tried to explore the funconalies
of MS-Excel as a potenal tool for stascal analysis and suggested some simple tricks and
techniques that will save both me and energy.
Using Excel for Stascal 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 beer business decisions. Excel can help you by
recommending, and then, automacally creang PivotTables, which are a great way to
summarize, analyze, explore, and present your data.
pivot-table1
A pivot table may be used as an interacve data summarizaon tool to automacally condense
large datasets into a separate, concise table. You can use it to create an informave summary of
a large dataset or make regional comparisons between brand sales.
pivot-table2
You can create PivotTables from lists, as you dene which elds should be arranged in columns,
which elds should become rows, and what data you wish to summarize.
Using Excel for Stascal Analysis: Descripve Stascs
pf3
pf4
pf5

Partial preview of the text

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.