How to Calculate Frequency in Excel

A frequency function in the Microsoft Excel is a lovely feature that help make the company and private life quite easier in its own way. The function is a tool that easily calculates how often a specific value is displayed within its range of values, which would then return with a vertical array of the numbers.

Understanding Frequency Function’s Parameters

The frequency function in Microsoft Excel is a function that has two different arrays at performing its duty for the users. This is what have come to be known as data array and the bins array, which works together, and it is impossible to use it without inputting data into both of them.

Data Array: This is known as either an array or reference that is useful for setting values for what the user wishes to know its frequency. If there is no value inputted in the data array, then the frequency would most likely return with zeros.

Bins Array: The bins array is the part of frequency function that works as either an array or a reference that interval to the groups that values that are in the data array would easily use for calculating the frequency. If there is no value that has been inputted in bins array, then a frequency function would be returning that number of elements in the data array.

See also  How to Calculate Irr in Excel

Examples of Frequency function

Finding a Single Frequency

In this example, we have frequency, and we want to find a single frequency. We’d know that there are multiple frequency, but we are looking for one. This is one of the places that the frequency function in Microsoft Excel can be extremely useful.

Finding a Single Frequency

Multiple Frequency in Data

In the previous example, it was clarified that it is possible to find a single frequency, and there might be some curiosity to know about multiple frequency, which is why this example is placed, to satisfy that curiosity. In this example, we will use the previous data to find the multiple frequency in it. For the successfulness of this, we need to know how many times different values are repeated.

Multiple Frequency in Data

Frequency of Non Communication

We are a company that likes to communicate with our customers many times, and we are worrying that we might be losing touch with some of our customers. We want to know if there has been any customers that we have not contacted within the last week.

We’d know that if the customers have not been contacting us, then there are different risks associated with it. It might be that they do not use our products anymore, and we need to know if there is any non-communication with any of our customers.

Frequency of Non Communication

How frequently do Employee Make a Sales Yesterday and Today?

In this example, we want to find the possibilities of knowing how frequently the employees make sales for the days. We have a number that shows 2 times in one place, and there is a number that shows 4 times in the other, which means that there is a frequency between yesterday and today’s sales.

See also  How to Vlookup other Tab in Excel?

How frequently do Employee Make a Sales Yesterday and Today

Using Frequency Function in Exam

In this example, there is a teacher that would like to use the frequency function to make the assessments. We’d have students’ names and their grade on side of the names. Then we will have the grade evaluation in another column, and then finally use the frequency function to make the assessment.

Using Frequency Function in Exam

Breaking Ages into Different Ranges

This example is going to effectively make it possible to divide the ages into certain age groups. In the data range, we have people with different ages, and in the bin range, we’d have the age groups we have assigned to them.

Breaking Ages into Different Ranges

Height Division with Frequency Function

We are trying to setup a new basketball tournament, and there have been many people signing up for the tournament.

We have different people with various height, and we would like to know how many would be in each group, so we could identify if it is necessary to have more people to improve the whole process, and be more productive when the basketball tournament starts.

Height Division with Frequency Function

Handling Frequency with N/A Error

It is possible that the same formula would show error, which is why it is important to understand that the circumstances are the same, but it is different because of the N/A error showing in the result.

In this scenario, we are looking at the scenario where we divided the height into groups, but this time there is N/A error in the frequency. This is because the value we have added is too large, which makes it show an N/A error, and the only thing needed to be done is reducing the large value.

See also  How to use DATEDIF function

If you’d calculate numbers of the frequency, you would get 11, which is the number of people that is under the “People’s height”.

Handling Frequency with NA Error

Frequency of Company’s Performance in all Departments

In this example, we are a company that have about 1500 different customers, and we know that they have different needs, based on their requirements.

We want to know how frequently the customers are in need of a certain services, so we can assess how frequently the company is performing in all the 6 departments.

In the picture below, you would notice that 2 out of 6 are providing installation services, and another 2 out of 6 are providing technical services, while respective 1 out of 6 are providing sales and general services.

Frequency of Companys Performance in all Departments

Race Preparation with Frequency Function

We are preparing for a race, and as a school we are looking for people who could run the 100 meters within a specific time frame.

We are also prepared to know which ones we could have chosen under the circumstances that we do not find anyone who could run the distance, but we are going to use percentage to simplify the process with the excel function.

In this example, we’d have six candidates who have performed very well, and could participate in the race. In the picture showing below, 1 means 100%. Even though we are using FREQUENCY, but this time there is also a fixed table included, and we’d only use the frequency formula.

This example is meant to clarify that in tables, it is not allowed to use frequency, when it has a multi formula in it.

Race Preparation with Frequency Function