Frequency function in Excel |
|
FREQUENCY function in Excel is a statistical function and
calculates how often values occur within a range of values, and then returns a vertical array of numbers. For example, use FREQUENCY to count the number of people who get a certain range of salary as shown in the Excel training video or to find out how many responses in a survey fell within a range of responses. Because FREQUENCY returns an array, it must be entered as an array formula. Syntax FREQUENCY(data_array,bins_array) Data_array is an array of or reference to a set of values for which you want to count frequencies. If data_array contains no values, FREQUENCY returns an array of zeros. Bins_array is an array of or reference to intervals into which you want to group the values in data_array. If bins_array contains no values, FREQUENCY returns the number of elements in data_array. Remarks FREQUENCY is entered as an array formula in Excel after you select a range of adjacent cells into which you want the returned distribution to appear. The number of elements in the returned array is one more than the number of elements in bins_array. The extra element in the returned array returns the count of any values above the highest interval. For example, when counting three ranges of values (intervals) that are entered into three Excel cells, be sure to enter FREQUENCY into four cells for the results. The extra cell returns the number of values in data_array that are greater than the third interval value. FREQUENCY ignores blank cells and text. |
|
| Example The example shown in the Excel training video demonstrates how you can calculate the number of people drawing a certain range of salary. One person receives a salary of less than or equal to 70000. Two people receive a salary of greater than 70000 and less than or equal to 79000. Four employees receive a salary of greater than 79000 but less than or equal to 89000. Two employees get a salary of greater than 89000. Similarly if you have data of a survey involving how many people watch a certain channel at a certain time you can calculate the 'frequency' quickly to arrive at which channel is the most watched! In another example you could have data of students marks in Math and you could find the frequency of a range of marks which could not only reveal the performance of the students in Math but also the efficacy of teaching. The formula in the example must be entered as an array formula. After entering the appropriate Excel data in the appropriate cells, select the range c2:c5, press F2, and then press CTRL+SHIFT+ENTER. If the frequency formula in Excel is not entered as an array formula, there will be only one result in cell c2 (1). | |