EDUCBA

EDUCBA

MENUMENU
  • Free Tutorials
  • Certification Courses
  • 120+ Courses All in One Bundle
  • Login

Excel Frequency Formula

By Pradeep SPradeep S

Home » Excel » Blog » Statistical Functions in Excel » Excel Frequency Formula

FREQUENCY Formula in Excel

Excel Frequency Formula (Table of Contents)

  • Frequency Formula in Excel
  • How to Use Frequency Formula in Excel?

Frequency Formula in Excel

FREQUENCY formula is a prebuilt integrated function which is categorized under a STATISTICAL group of formula 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 the count how many times a specific value fall within a specific range, e.g. Suppose if a table contains multiple age group 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 that we supply.
  • The frequency formula counts the values in a range of cells or array that fall within a range or bin in excel.

The syntax for the Frequency formula in Microsoft Excel is:

Frequency Formula

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

  • Data Array: It is an actual range or array value where you need to find out Dataset’s frequency distribution.
  • Bins Array: It is 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 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 Frequency Formula in Excel with some examples.

Popular Course in this category
Sale
Excel Advanced Training (16 Courses, 23+ Projects)16 Online Courses | 23 Hands-on Projects | 140+ Hours | Verifiable Certificate of Completion | Lifetime Access
4.8 (11,383 ratings)
Course Price

View Course

Related Courses
Excel for Marketing Training (8 Courses, 13+ Projects)
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 find out 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 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 needs 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 that select the column range. i.e. A2:A18.

Frequency Formula Example 1-4

  • Bins Array: It is 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 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 frequency excel formula found 2 values or member 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 data range

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

  • Now, Let’s apply the frequency formula in cell E2. Select the 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, 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 needs 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: It is 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 of the cells are blank.

To obtain the other values, select a cell range from E2 to E5. Once it is selected, Click inside the dataset of the frequency formula bar. Now, you can see, all the three range of values get selected in three different colors; now, 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 about Frequency Formula in Excel

  • 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 along with 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

All in One Excel VBA Bundle (120+ Courses, 30+ Projects)

120+ Online Courses

30+ Projects

500+ Hours

Verifiable Certificates

Lifetime Access

Learn More

0 Shares
Share
Tweet
Share
Primary Sidebar
Excel Functions
  • Statistical Functions in Excel
    • COUNTIF Formula in Excel
    • COUNTIF Multiple Conditions
    • COUNTIFS with Multiple Criteria
    • Statistics in Excel
    • Confidence Interval in Excel
    • Interpolate in Excel
    • Linear Programming in Excel
    • COUNTIFS in Excel
    • Excel Variance
    • Normal Distribution Formula in Excel
    • Count Cells with Text in Excel
    • Excel Formula For Rank
    • Linear Regression in Excel
    • COUNTA Function in Excel
    • MAX Formula in Excel
    • Excel NORMSINV
    • Count Names in Excel
    • FORECAST Formula in Excel
    • NPER in Excel
    • Excel Median Formula
    • Lognormal Distribution in Excel
    • Excel Chi Square Test
    • Count Formula in Excel
    • COUNTIF Examples in Excel
    • Excel P-Value
    • COUNTIF Not Blank in Excel
    • Excel Standard Deviation Formula
    • Excel GROWTH Formula
    • Excel Percentile Formula
    • Excel Frequency Formula
    • Excel Average Formula
    • Excel Correlation Matrix
    • Excel Z Score
    • Excel MAX IF Function
    • Z TEST in Excel
    • Excel Trendline
    • Excel F-Test
    • Excel STDEV Function
    • Excel Frequency Distribution
    • DCOUNT Function in Excel
    • Excel MIN Function
    • Excel Forecast Function
    • FREQUENCY Excel Function
    • COUNTIF with Multiple Criteria
    • Standard Deviation in Excel
    • MAX Excel Function
    • Excel QUARTILE Function
    • Excel T.Test Function
    • Excel PERCENTILE Function
    • MODE Excel Function
    • SLOPE Excel Function
    • Excel Median Function
    • Excel TREND Function
    • Excel Count Function
    • Excel LARGE Function
    • SMALL Excel Function
    • COUNTIF Excel Function
    • Excel AVERAGE Function
    • Excel CORREL Function
  • Excel Functions (12+)
  • Excel Tools (114+)
  • Financial Functions in Excel (17+)
  • Logical Functions in Excel (14+)
  • Lookup Reference Functions in Excel (34+)
  • Maths Function in Excel (47+)
  • TEXT and String Functions in Excel (26+)
  • Date and Time Function in Excel (23+)
  • Information Functions in Excel (4+)
  • Excel Charts (56+)
  • Excel Tips (222+)
  • Workplace Productivity (4+)
  • Microsoft Office Tools (15+)
  • Excel Formula and Functions (21+)
  • MS Excel Shortcuts (4+)
Excel Functions Courses
  • EXCEL ADVANCED Training Certification
  • Online Excel for Marketing Course
Footer
About Us
  • Blog
  • Who is EDUCBA?
  • Sign Up
  • Live Classes
  • Corporate Training
  • 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

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

EDUCBA
Free Excel Course

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

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

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

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

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

EDUCBA Login

Forgot Password?

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

Let’s Get Started

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

EDUCBA

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

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

Special Offer - EXCEL ADVANCED Training Certification Learn More