Overview
- Microsoft Excel is an excellent tool for learning and executing statistical functions
- Here there is 12 Statistical functions in Excel that you must master for a successful analytics career
We are going to excel in statistics!
“Statistics is the grammar of science”. – Karl Pearson
Let's make it a little more relevant to us: statistics is the grammar of data science. You will notice that almost all successful data science or analytics professionals have a solid understanding of statistics., but does that mean you need a master's degree in the subject?
Absolutely not!
We will approach this article using the famous Pareto Principle: the 80% consequences come from 20% of the causes. Therefore, we will focus mainly on the 20% of the concepts and functions that are useful the 80% weather. Now, the question as an analyst is, How to start implementing statistics?
Of course, you can implement and practice these concepts in programming languages like Python and R. But wait a second and think: What is the most used tool in most organizations?
It's Microsoft Excel! Honestly, Excel is the Swiss Army Knife for analytics professionals that helps you focus on what's important (statistics in our case) and handles the rest of the calculations and customizations by itself.
This article is for everyone who is starting their journey towards statistics using Excel.. we will use 10 Key Statistical Functions in Excel to Answer Questions for a Fictitious Sports Company, Khelo, while we examine your data.
If you are a beginner in the world of analytics and Excel, I highly recommend that you follow these free courses:
How we will approach these statistical functions 1st in Excel
I have divided these statistical functions in Excel into two categories:
- Basic statistical functions
- Intermediate statistical functions
Let's start!
Understanding the data and posing the problem
We will solve some key questions about our fictitious sports apparel and equipment company: Khelo. These are the columns we have:
- Team
- Number of items sold
- Cost of each item
- % Off
- Income
In the course of this article, we will answer these questions:
- How many items have discount?
- How many items / equipment sells the store?
- What is the number of products sold without discount?
- Are there products sold that cost more than 2000 together with a discount rate higher than 50%?
- What is the average number of products sold?
- What is the median number of products sold?
- What is the most frequent discount percentage?
- What is the standard deviation of the number of products sold?
- Is there a relationship between the number of products sold and the discount percentage?
Basic statistical functions in Excel
MS Excel provides a variety of useful statistical functions. Let's start with some of the basic but extremely powerful features. Honestly, You will find that you are using the basic statistical functions on 90% of time and 10% the remainder of your time is taken by intermediate and advanced functions.
We will mainly talk about the different types of counting functions here. They are very similar to other functions like sum, maximum, minimum, average.
1. Counting function
We use the count function when we need to count the number of cells that contain a number. Remind ONLY NUMBERS! Let's see the function:
- COUNT (valor1, [value2],…)
Then, let's try to find the answer to our first question: How many items had a discount?
There is 11 discounted products.
2. Counta function
While the counting function only counts numerical values, the COUNT function counts all cells in a range that are not empty. The function is useful for counting cells that contain any kind of information, including error values and empty text.
- TELL (valor1, [value2],…)
3. Countblank
the COUNTBLANK The function counts the number of empty cells in a cell range. Cells with formulas that return empty text are also counted here, but cells with zero values are not counted. This is a great function to summarize empty cells while analyzing the data.
There is only 2 non-discounted items.
4. Countifs function
Countifs is one of the most used statistical functions in Excel. The COUNTIF function applies one or more conditions to the cells in the given range and returns only those cells that meet all the conditions.
- COUNTIFS (criteria_range1, criterios1, [criteria_range2, criteria2]…)
Intermediate statistical functions in Excel
Here we will discuss some of the intermediate statistical functions in MS Excel related to central tendency and dispersion. These functions are very useful in our day to day as an analyst.
5. Average function
The most common function that we habitually use in our daily life is the average (or average). The AVERAGE function simply returns the arithmetic mean of all cells in a given range:
- AVERAGE (number 1, [number2],…)
6. Median function
The outlier problem can be solved using another function for central tendency: the median. The median function returns the mean value of the given cell range. The syntax is quite simple:
- MEDIAN (number 1, [number2],…)
7. Mode function
For numerical values, the mean and median are usually sufficient, but what about categorical values? Here, the mode enters the scene. The mode returns the most frequent and repeated value in the given range of values:
- MODE.SNGL (number 1,[number2],…)
Good, this is a simple one. Let's find the most frequent discount value given by the sports store:
8. Standard deviation function
The standard deviation is one of the ways to quantify the dispersion. It is a measure of the dispersion of the values of the mean value.
Here, we will use the STDEV.P function which is used to calculate the standard deviation based on the entire population given as arguments:
- STDEV.P (number 1,[number2],…)
As expected, the standard deviation of the quantity sold is less, which means that the dispersion is smaller while the standard deviation of the cost of the products is high.
9. Quartile functions
This is another function with abundant applications in the industry.. Helps us divide the population into groups. QUARTILES.INC returns the quartile of a data set, based on percentile values of 0 a 1, inclusive.
For instance, you can use this function to know the 25% top of your customer base.
- CUARTIL.INC (headquarters, room)
10. Correlation function
The CORREL function () it's my personal favorite. Provides really powerful information that is not obvious to the naked eye. the CORREL The function returns the correlation coefficient of two cell ranges. But what is that? Basically, tells us how strong the relationship is between the two variables.
Note: Does not show any cause and effect relationship.
The correlation value range is between -1 Y 1.
Let's go to our last and most interesting question: Is there a relationship between the quantity of goods sold and the discount percentage?
Good, the correlation turns out to be ~ 0.8, which is quite high. These seem to be positively related, which means more discount, more quantity sold.
Final notes
In this article, we analyze more than 10 statistical functions for beginners and intermediates in MS Excel, ranging from simple count () up to advanced run (). Statistics is one of the most important tools in an analyst's kit and you can achieve many of your statistical goals simply by using Excel..
I recommend that you check out the following additional resources in Excel:
I will cover advanced statistical functions in the future.. Let me know some of your favorite statistical functions and I will try to incorporate them in my next articles..
