When you’re working with data, it’s often useful to know how many values fall within a given range. With a spreadsheet, you can perform this calculation using a formula. Let’s learn how to calculate frequency in Microsoft Excel.
How to Calculate Frequency in Excel
Consider a list of prices. You might want to know how many fall within given ranges that you choose for the dataset. You could count manually, but this would become difficult with a lengthy list of data. The key is to use Excel’s FREQUENCY function to do the work for you.
Your prices are listed in the range D1:D8 in your spreadsheet. Now, you’ll need to assign the ranges. In the case of the FREQUENCY function, these are known as bins. A logical starting place would be prices less than or equal to 50. In cell E1, type 50.
Hint: You won’t need lower or upper limits; Excel finds them automatically. Thus, there is no need to type 0.
To find prices between 51-60, type 60 into cell E2. For prices between 61-70, type 70 into cell E3.
Notice that you have one price ($74) that falls above $70. Excel will recognize this when you run the formula, and assign it to the last bin. Thus, cell E4 should be left blank.
It helps to input bin labels beside the numbers you’ve just inputted for easy review of the formula.
Now, click into cell G1 and begin typing your formula:
=FREQUENCY(
Click and drag to select your data array, D1:D8, then type ,. Your formula is now:
=FREQUENCY(D1:D8,
Complete it by selecting your bin array, E1:E3,then type ). The finished formula is:
=FREQUENCY(D1:D8,E1:E3)
Hit Enter on your keyboard, and Excel returns your solution, counting the number of values that fall within the bins, or ranges, that you designated.
As you can see, the FREQUENCY function makes data analysis easy in Excel.
Budget in Excel