Excel Frequency Function Example

January 25, 2019 by exceloffice Leave a Comment

The FREQUENCY function in Excel calculates how often values occur within the ranges you specify in a bin table. You can also use the COUNTIFS function to create a frequency distribution.

1. First, enter the bin numbers (upper levels) in the range C4:C8.

2. Select the range D4:D9 (extra cell), enter the FREQUENCY function shown below and finish by pressing CTRL + SHIFT + ENTER.

Note: the formula bar indicates that this is an array formula by enclosing it in curly braces {}. Do not type these yourself. To delete this array formula, select the range D4:D9 and press Delete.

Also See: COUNTIFS function: Description, Usage, Syntax, Examples and Explanation

3. Hide the column with the bin numbers (upper levels) and insert a column with proper bin labels.

Explanation: 1 value is less than or equal to 20, 8 values are greater than or equal to 21 and less than or equal to 25, etc. At step 2, we selected the range D4:D9 (instead of the range D4:D8). As a result, the FREQUENCY function also counts the number of values that are greater than 40.

4. You can also use the Analysis Toolpak to create a histogram.

Also See: Count cells between two numbers in Excel

5. Change the bin numbers. Select the range D4:D9 (no extra cell), enter the FREQUENCY function shown below and finish by pressing CTRL + SHIFT + ENTER.

Explanation: the last bin number is greater than or equal to the maximum value (52). As a result, we don’t need an extra cell to count the number of values that are greater than 60.

6. You can also use the COUNTIFS function to create a frequency distribution.

Explanation: the COUNTIFS function in Excel counts cells based on two or more criteria. The COUNTIFS function shown above has 2 range/criteria pairs. We used the & operator to concatenate “>=” with the value in cell C4 and “<=” with the value in cell D4. You can easily copy this formula to the other cells.

Also See: IF with wildcards in Excel

Related Posts:

Leave a Reply Cancel reply

A -Z Excel Functions and Formulas Example

ACCRINT function

ACCRINTM function

ADDRESS function

AGGREGATE function

AMORDEGRC function

AMORLINC function

Analysis ToolPak

ARABIC function

AVEDEV function

AVERAGE function

AVERAGEA function

AVERAGEIF function

AVERAGEIFS function

BAHTTEXT function

BESSELI function

BESSELJ function

BESSELK function

BESSELY function

BETA.DIST function

BETA.INV function

BIN2DEC function

BIN2HEX function

BIN2OCT function

BINOM.DIST function

BINOM.DIST.RANGE function

BINOM.INV function

BITAND function

BITLSHIFT function

BITRSHIFT function

BITXOR function

CEILING function

CEILING.MATH function

CEILING.PRECISE function

Cell Ranges & References

CHISQ.DIST function

CHISQ.DIST.RT function

CHISQ.INV function

CHISQ.INV.RT function

CHISQ.TEST function

CHOOSE function

Clustered Column

COLUMN function

COLUMNS function

COMBIN function

COMBINA function

COMPLEX function

Compound Annual Growth Rate

CONCAT function

CONCATENATE function

Conditional Formatting

CONFIDENCE.NORM function

CONFIDENCE.T function

CONVERT function

CORREL function

COUNTA function

COUNTBLANK function

COUNTIF function

COUNTIFS function

COUPDAYBS function

COUPDAYS function

COUPDAYSNC function

COUPNCD function

COUPNUM function

COUPPCD function

COVARIANCE.P function

COVARIANCE.S function

CUBEKPIMEMBER function

CUBEMEMBER function

CUBEMEMBERPROPERTY function

CUBERANKEDMEMBER function

CUBESET function

CUBESETCOUNT function

CUBEVALUE function

CUMIPMT function

CUMPRINC function

Custom Number Format

DATEDIF function

DATEVALUE function

DAVERAGE function

DAYS360 function

DCOUNT function

DCOUNTA function

DEC2BIN function

DEC2HEX function

DEC2OCT function

DECIMAL function

DEGREES function

DOLLAR function

DOLLARDE function

DOLLARFR function

DPRODUCT function

DSTDEV function

DSTDEVP function

DURATION function

EFFECT function

ENCODEURL function

EOMONTH function

ERFC.PRECISE function

ERROR.TYPE function

EXPON.DIST function

F.DIST function

F.DIST.RT function

F.TEST function

FACTDOUBLE function

FILTERXML function

FISHER function

FISHERINV function

FLOOR.MATH function

FLOOR.PRECISE function

FORECAST function

Formulas and Functions

FORMULATEXT function

FREQUENCY function

FVSCHEDULE function

GAMMA.DIST function

GAMMA.INV function

GAMMALN function

GAMMALN.PRECISE function

GEOMEAN function

GESTEP function

GETPIVOTDATA function

GROWTH function

HARMEAN function

HEX2BIN function

HEX2DEC function

HEX2OCT function

Histogram Chart

HLOOKUP function

HYPERLINK function

HYPGEOM.DIST function

IFERROR function

IMAGINARY function

IMARGUMENT function

IMCONJUGATE function

IMCOSH function

IMCSCH function

IMLOG10 function

IMLOG2 function

IMPOWER function

IMPRODUCT function

IMREAL function

IMSECH function

IMSINH function

IMSQRT function

INDIRECT function

INTERCEPT function

INTRATE function

ISBLANK function

ISERROR function

ISEVEN function

ISFORMULA function

ISLOGICAL function

ISNONTEXT function

ISNUMBER function

ISO.CEILING function

ISOWEEKNUM function

ISTEXT function

LINEST function

LOGEST function

LOGNORM.DIST function

LOGNORM.INV function

LOOKUP function

MAXIFS function

MDETERM function

MDURATION function

MEDIAN function

MINIFS function

MINUTE function

MINVERSE function

MODE.MULT function

MODE.SNGL function

MROUND function

MULTINOMIAL function

NEGBINOM.DIST function

Net Present Value

NETWORKDAYS function

NETWORKDAYS.INTL function

NOMINAL function

NORM.DIST function

NORM.INV function

NORM.S.DIST function

NORM.S.INV function

NUMBERVALUE function

OCT2BIN function

OCT2DEC function

OCT2HEX function

ODDFPRICE function

ODDFYIELD function

ODDLPRICE function

ODDLYIELD function

OFFSET function

Operator Precedence

PDURATION function

PEARSON function

PERCENTILE function

PERCENTILE.EXC function

PERCENTILE.INC function

PERCENTRANK.EXC function

PERCENTRANK.INC function

PERMUT function

PERMUTATIONA function

PHONETIC function

POISSON.DIST function

PRICEDISC function

PRICEMAT function

PRODUCT function

PROPER function

QUARTILE function

QUARTILE.EXC function

QUARTILE.INC function

QUOTIENT function

RADIANS function

RANDBETWEEN function

RANK.AVG function

RANK.EQ function

RECEIVED function

REPLACE function

REPLACEB function

RIGHTB function

ROUNDDOWN function

ROUNDUP function

SEARCH function

SEARCHB function

SECOND function

SEQUENCE function

SERIESSUM function

SHEETS function

SKEW.P function

Sort and Filter

SQRTPI function

STANDARDIZE function

STDEV.S function

STDEVA function

STDEVPA function

SUBSTITUTE function

SUBTOTAL function

SUMIFS function

SUMPRODUCT function

SUMX2MY2 function

SUMX2PY2 function

SUMXMY2 function

SWITCH function

T.DIST function

T.DIST.2T function

T.DIST.RT function

T.INV.2T function

T.TEST function

TBILLEQ function

TBILLPRICE function

TBILLYIELD function

TEXTJOIN function

Thermometer Chart

TIMEVALUE function

TRANSPOSE function

TRIMMEAN function

UNICHAR function

UNICODE function

Unix Time Stamp

VLOOKUP function

WEBSERVICE function

WEEKDAY function

WEEKDAY.INTL function

WEEKNUM function

WEIBULL.DIST function

What-If Analysis:

WORKDAY function

WORKDAY.INTL function

YEARFRAC function

YIELDDISC function

YIELDMAT function

Z.TEST function