Frequency Function in Excel returns the Frequency Distribution of the supplied data array according to the bins that we supply. FREQUENCY Function in Excel returns multiple values and must be entered as an array formula with Ctrl + Shift + Enter, which we will explain in detail below.
Syntax of FREQUENCY Function in Excel:
data_array – An array of values for which you want to get frequency distribution
bins_array – An array of intervals (“bins”) for grouping the data_array.
Example of FREQUENCY Function in Excel
Let’s see how to create a Frequency Distribution using FREQUENCY Function step by step.
Step 1: Cell A2:A13 contains the score of the students and C3:C7 contains the Bin, on which the grouping of score is to be done
Step 2: Now select the cell range D3:D8 and in the formula tab type
As shown below
Note: we need to select D3:D8, because frequency of values greater than 80 is captured in D8.
Step 3: press Ctrl+Shift+Enter to get the Frequency Distribution of the score. So the output will be
Note that the bins_array values specify the maximum values for the first two Score ranges. Therefore, in this example, the Scores are to be split into the ranges 0-40 score, 41-50 score, 51-60 score, 61-70 score, 71-80 score and 81+.
- There are 4 values less than or equal to 40 (23, 17, 14, 13) so the Frequency for this bin will be 4
- There are 2 values between 41 and 50 (45, 48) so the Frequency for this bin will be 2 and so on.
- One value which is greater than 80 is captured in D8.
Notes: FREQUENCY Function returns multiple values and must be entered as an array formula with Ctrl + Shift + Enter.