Notice, for example, that although the Data column contains the value of 20, the Freq column shows zero for the bin value of 20. Third, the FREQUENCY function returns values that are greater than one bin and less than or equal to the next-higher bin. So in the figure, the value of 3 in cell E3, is the count of the numbers 10, 16, and 20. The extra cell, cell E3 in this case, contains the number of values less than or equal to the lowest bin number. Here, for example, I entered the FREQUENCY formula in a four-cell range to reference the three Bins cells. Second, you must enter one more FREQUENCY formula than the number of bins. To change the array into a normal function, select the cells in the array (here, E3:E6), press F2 to edit the function, then hold down Ctrl and press Enter to enter the formula in each cell as a normal function. Later, if you decide to change the number of bins, you first must change the array-entered function into a normal function, add or delete the rows you need, then re-enter the array. …held down the Ctrl+Shift keys, then pressed Enter to enter the formula as an array. Here for example, I selected the range E3:E6, typed the formula… The FREQUENCY function has three unusual characteristics.įirst, you must enter the formulas as a multi-cell array. In this figure, for example, the data_array is the range B3:B9, and the bins_array is the range D4:D6. The obvious way to generate these frequency distributions is to use Excel’s FREQUENCY function, which has this syntax: The distributions would compare the number of instances for nearly any criteria you can count: orders by product by month for two sales people, failure rates by month for two products, the number of sick days taken by age by gender in the past year, and so on. You could apply the logic of this chart to calculate frequency distributions for your own data. That is, over the past 30 years, Texas has tended to maintain a lower unemployment rate than California. The chart shows that the unemployment rates in Texas are clumped in the upper part of the chart. The source data consists of 361 unemployment rates for each state, extending from December, 1982, through December, 2012.

Then I summarize the data and created this chart, technically a histogram, which I originally had set up as swipe file. So I downloaded data from the Federal Reserve Bank of St. Because the Texas and California governors have been bickering over the Texan’s attempt to poach California employers, I got curious about the distribution of the unemployment rates in the two states.

Excel histogram from frequency table