INTRODUCTION
So you must have heard this word FREQUENCY , may be not in context of EXCEL but of the sound , or hearing range etc.Frequency is the number of repetitions of the waves in one second. This is from where this word is most frequently introduced to us as kids.An analogy to this word arises when we need to find out the repetition of a number in a given range. For such cases EXCEL has a function named as FREQUENCY.FREQUENCY FUNCTION is found under the STATISTICAL FUNCTIONS category under the formula tab.
FREQUENCY FUNCTION FIND OUT THE “NUMBER OF TIMES” A PARTICULAR VALUE REPEATS IN A PARTICULAR RANGES AND THEN RETURNS THE NUMBER OF REPETITIONS.This function can be used in a particular scenarios where we want to find out the number of occurrences of any particular values say grades in a class or anything like that.In fact this function is a statistical function as when we create a table we need to find out the frequencies in different class intervals. But we can use it anywhere , where it fulfills our requirement.
In this article we’ll see how we can use this function, its syntax, how to use it in formula , its purpose and examples.
PURPOSE OF FREQUENCY FUNCTION IN EXCEL
FREQUENCY FUNCTION RETURNS THE NUMBER OF REPETITIONS OF A PARTICULAR VALUE IN A SPECIFIC RANGE.
Frequency function is helpful in finding out the frequency in a particular slab. The most apt example for describing this function is of a class of students.
If we want to analyze the data about the standing of the students in the different slabs say A GRADE, B GRADE AND SO ON. , we can easily use this function instead of counting the students manually.
PREREQUISITES TO LEARN FREQUENCY FUNCTION
THERE ARE A FEW PREREQUISITES WHICH WILL ENABLE YOU TO UNDERSTAND THIS FUNCTION IN A BETTER WAY.
- Some information about the FREQUENCY WORD’S MEANING is an advantage for the use of such formulas.
- Basic understanding of how to use a formula or function.
- Basic understanding of rows and columns in Excel.
- Of course, Excel software.
Helpful links for the prerequisites mentioned above What Excel does? How to use formula in Excel?
SYNTAX: FREQUENCY FUNCTION
The Syntax for the function is
=FREQUENCY (DATA , BIN ARRAY)
DATA is the range or array in which we want to find out the frequency of different numbers.
BIN ARRAY is the slabs of the numbers in which we want to find out the frequencies.
BIN ARRAY is like a limit. e.g. if we want to define three slabs like 0-10, 10-20, 20-30 , our bin array will be 10.20,30.
EXAMPLE:FREQUENCY FUNCTION IN EXCEL
DATA SAMPLE
Let us try to make a demographic profile of a group of persons. Suppose, the ages of different persons are given to us and we would like to know the number of persons between 20-30, 30-40 and 40-50 years separately. All the persons are from 20 to 50 years. The data is given below.
| AGES OF 100 PERSONS | ||||
| 48 | 33 | 39 | 39 | 28 |
| 20 | 23 | 39 | 50 | 24 |
| 34 | 27 | 44 | 23 | 46 |
| 23 | 38 | 44 | 28 | 35 |
| 39 | 24 | 26 | 38 | 47 |
| 50 | 47 | 41 | 47 | 41 |
| 21 | 40 | 44 | 47 | 43 |
| 32 | 37 | 41 | 40 | 26 |
| 27 | 36 | 47 | 35 | 35 |
| 47 | 39 | 49 | 34 | 21 |
| 42 | 23 | 41 | 34 | 37 |
| 50 | 28 | 37 | 26 | 28 |
| 35 | 34 | 27 | 28 | 22 |
| 30 | 48 | 50 | 47 | 43 |
| 46 | 33 | 34 | 33 | 36 |
| 22 | 45 | 28 | 37 | 47 |
| 25 | 37 | 28 | 37 | 28 |
| 31 | 27 | 43 | 22 | 24 |
| 26 | 38 | 30 | 31 | 20 |
| 46 | 35 | 36 | 34 | 23 |
STEPS TO USE FREQUENCY FUNCTION
STEPS:
- Place the cursor in the cell, where we want the result.
- It is an array function and will return an array with the number of items equal to the number of items in bin array, so select the range equal to bin array as shown in picture.
- Now enter the formula by starting =
- Formula for our example is =FREQUENCY(E4:I23,N6:N8) . Put it in the cell.
- BUT DON’T PRESS ENTER. ALTERNATIVELY PRESS CONTROL+SHIFT+ENTER. [This is a standard procedure of entering the array formulas]
- The result will fill up all the selected cells.
- We got our result as expected.
EXPLANATION OF THE FUNCTION
Formula for our example is
=FREQUENCY(E4:I23,N6:N8)
The first argument contains the complete data in which we want to find out the repetition of ages.
Second argument contains the bin array which contains the edge values of the slabs such as 30 40 and 50. The frequency will be found between the previous value and the current value.
KNOWLEDGE BYTES
WHICH VALUE IS INCLUDED IN THE BIN ARRAY IN FREQUENCY FUNCTION
A confusion may strike when creating a bin array for a problem about the values of bin array.It may create a confusion that when we create a bin array, which value will be included or excluded between the two limits.Suppose we have a bin array 10,20,30,40.So, always remember, the next value is included and previous value is excluded.The above mentioned bin array means 11-20, 21 to 30, 31 to 40.
