EDUCBA

EDUCBA

MENUMENU
  • Explore
    • Lifetime Membership
    • All in One Bundles
    • Fresh Entries
    • Finance
    • Data Science
    • Programming and Dev
    • Excel
    • Marketing
    • HR
    • PDP
    • VFX and Design
    • Project Management
    • Exam Prep
    • All Courses
  • Blog
  • Enterprise
  • Free Courses
  • Login
Home Excel Excel Resources Statistical Functions in Excel Excel Frequency Formula

Excel Frequency Formula

Pradeep S
Article byPradeep S
Madhuri Thakur
Reviewed byMadhuri Thakur

Updated June 8, 2023

FREQUENCY Formula in Excel

Frequency Formula in Excel

The FREQUENCY formula is a prebuilt integrated function categorized under a STATISTICAL group of formulas in Excel.

Start Your Free Excel Course

Excel functions, formula, charts, formatting creating excel dashboard & others

It’s a statistical calculation that lets you know how many times a specific value falls within a specific range, e.g., Suppose a table contains multiple age groups of people. We can group them based on the age parameter, i.e., Age grouping (frequency of appearance for Different age groups i.e.

  • Child (0-12 years),
  • Adolescence (13-18)
  • Adult (19-59)
  • Senior Citizen (60 years and above)

The frequency formula in Excel calculates or determines the Dataset’s frequency distribution, i.e., how many times that value appears within its range of values.

Definition of Frequency formula in Excel

  • The frequency formula calculates or determines the supplied vertical array’s frequency distribution based on the Bins or intervals we supply.
  • The frequency formula counts the values in a range of Excel cells or arrays that fall within a range or bin.

The syntax for the Frequency formula in Microsoft Excel is as follows:

Frequency Formula

The Frequency Formula in Excel has two arguments which are as below:

  • Data Array: An actual range or array value is where you need to find out Dataset’s frequency distribution.
  • Bins Array: A range or array of intervals (BINS) for grouping values.

The frequency formula should be entered as an array formula for multiple frequencies. If we want to find out multiple Bins or return multiple frequency values, it should be entered as an array formula with the CTRL +SHIFT +ENTER option in Excel.

How to Use Frequency Formula in Excel?

Frequency Formula in Excel is very simple and easy to use. Let’s understand the working of the Frequency Formula in Excel with some examples.

You can download this Frequency Formula Excel Template here – Frequency Formula Excel Template

Example #1 – To find out a single Frequency in Excel

In the below-mentioned example, the Table contains a different number of age group entities. I need to determine the frequency of the age group under or equal to 10 years. Here Frequency formula counts how often values occur in a range or array in Excel.

  • Now, Let’s apply the frequency formula in cell C2. Select cell C2 where the frequency formula needs to be applied.

Example 1

  • Click the insert function (fx) button under the formula toolbar; a dialog box will appear, Type the keyword frequency in the Search for a Function box; the frequency will appear in the Select a Function box. Double click on the Frequency function.

Frequency Formula Example 1-2

  • A dialog box appears where arguments for the frequency function need to be filled or entered. i.e. =FREQUENCY (data_array, bins_array)

Frequency Formula Example 1-3

  • Data Array: It is an actual range or array value where you need to find out Dataset’s frequency distribution; here, the range or array is from A2 to A18, So select the column range. i.e. A2:A18.

Frequency Formula Example 1-4

  • Bins Array: A reference or range or array of intervals (“BINS”) for grouping values.

As we want to find a single frequency here. I need to find out the frequency of the age group under or equal to 10 years only here; therefore, we need to enter cell ref or age as 10 or B2. Click ok after entering the two arguments. =FREQUENCY (A2:A18,B2)

Frequency Formula Example 1-5

So the output will be as given below.

Result of Example 1

You can observe that the frequency Excel formula found 2 values or members where their age is less than or equal to 10 years of age in the above single frequency calculation. (Age > or = 10 years)

Example #2 – To Find Multiple Frequency in a date range

In the below-mentioned example, the Table contains a member with different age entities. I need to determine or calculate the frequency distribution of different age groups of 17 members aged between 9 and 80.

  • Now, Let’s apply the frequency formula in cell E2. Select cell E2 where the frequency formula needs to be applied.

Example 2

  • Click the Insert Function (fx) button under the formula toolbar. A dialog box will appear, Type the keyword frequency in the Search for a Function box, and the frequency will appear in the Select a Function box. Double click on the Frequency function.

Frequency Formula Example 2-2

  • A dialog box appears where arguments for the frequency function need to be filled or entered. i.e. =FREQUENCY (data_array, bins_array)

Frequency Formula Example 2-3

  • Data_Array: It is an actual range or array value where you need to find out Dataset’s frequency distribution; here, the range or array is from A2 to A18, So select the column range. i.e. A2:A18.

Frequency Formula Example 2-4

  • Bins_Array: A reference or range or array of intervals (BINS) for grouping values.

As we want to find out multiple frequency values here. I need to find out the frequency distribution of four different age groups here; therefore, we need to enter four bins array values, So that the four bins array values or range will get selected, i.e., D2:D5. Click ok after entering the two arguments. FREQUENCY(A2:A18,D2:D5)

Frequency Formula Example 2-5

Note: Different age groups are split into 4 age groups to be four bins_array values.

The result will be as given below.

 Frequency Formula Example 2-6

Now, you can observe that only the first cell of the Frequency column has a value; the rest are blank.

Select a cell range from E2 to E5 to obtain the other values. Once it is selected, Click inside the dataset of the frequency formula bar. Now, you can see all three ranges of values get selected in three different colors; press CTRL + SHIFT + ENTER for the array formula to apply.

Press CTRL + SHIFT + ENTER

Now, you can see the frequency result column; it displays all the missing values.

Result of Example 2

In the formula toolbar, you can observe the curly brackets for an array formula.

Things to Remember

  • Frequency Formula ignores blank cells or text values in Excel.
  • If the Data_Array argument in the frequency formula does not contain any values in an array or range of cells, then Frequency returns an array of zeros.
  • If the Bin_Array argument in the frequency formula does not contain any values in an array or range of cells, then Frequency returns the number of elements in the data_array.

Recommended Articles

This has been a guide to Frequency Formula in Excel. Here we discuss how to use Frequency Formula in Excel, practical examples, and a downloadable Excel template. You can also go through our other suggested articles –

  1. Excel Frequency Distribution
  2. FREQUENCY Excel Function
  3. VLOOKUP Function in Excel
  4. Excel PRICE Function
ADVERTISEMENT
All in One Excel VBA Bundle
500+ Hours of HD Videos
15 Learning Paths
120+ Courses
Verifiable Certificate of Completion
Lifetime Access
ADVERTISEMENT
Financial Analyst Masters Training Program
2000+ Hours of HD Videos
43 Learning Paths
550+ Courses
Verifiable Certificate of Completion
Lifetime Access
ADVERTISEMENT
All in One Data Science Bundle
2000+ Hour of HD Videos
80 Learning Paths
400+ Courses
Verifiable Certificate of Completion
Lifetime Access
ADVERTISEMENT
All in One Software Development Bundle
5000+ Hours of HD Videos
149 Learning Paths
1050+ Courses
Verifiable Certificate of Completion
Lifetime Access
Primary Sidebar
Footer
About Us
  • Blog
  • Who is EDUCBA?
  • Sign Up
  • Live Classes
  • Certificate from Top Institutions
  • Contact Us
  • Verifiable Certificate
  • Reviews
  • Terms and Conditions
  • Privacy Policy
  •  
Apps
  • iPhone & iPad
  • Android
Resources
  • Free Courses
  • Excel Charts
  • Excel Tips
  • All Tutorials
Certification Courses
  • All Courses
  • Excel VBA Course - All in One Bundle
  • VBA Course
  • Excel Data Analysis Course
  • Excel for Marketing Course
  • Excel for Finance Course
  • Excel for HR Training

ISO 10004:2018 & ISO 9001:2015 Certified

© 2023 - EDUCBA. ALL RIGHTS RESERVED. THE CERTIFICATION NAMES ARE THE TRADEMARKS OF THEIR RESPECTIVE OWNERS.

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you

EDUCBA

Download Frequency Formula Excel Template

Let’s Get Started

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA
Free Excel Course

Excel functions, formula, charts, formatting creating excel dashboard & others

By continuing above step, you agree to our Terms of Use and Privacy Policy.
*Please provide your correct email id. Login details for this Free course will be emailed to you

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA Login

Forgot Password?

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA

Download Frequency Formula Excel Template

EDUCBA

डाउनलोड Frequency Formula Excel Template

This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy

Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more

🚀 Extended Cyber Monday Price Drop! All in One Universal Bundle (3700+ Courses) @ 🎁 90% OFF - Ends in ENROLL NOW