Excel formula library

Excel Tutorial

Every Excel formula in one place — search, filter by category or level, then open syntax, samples, and pitfalls. New formulas added to the library appear here automatically.

Start with first formula Browse formulas

127 Formulas

11 Categories

3 Skill levels

Live Search & filters

Formula list

Find any Excel formula

Filter by category — Math, Lookup, FILTER and other Dynamic Arrays, Text, Date, and more — then open a formula for syntax and examples.

Chapter 2

Statistical Analysis

13 AVERAGE =AVERAGE(number1, [number2], ...) Calculates the arithmetic mean of a range of numbers. Divides the sum of numeric values by the count of numbers present. Statistical Analysis Beginner Classic
Syntax
```
=AVERAGE(number1, [number2], ...)
```
Arguments

number1

First number, cell reference, or continuous range.

[number2, ...]

Additional numbers or ranges (up to 255 arguments).
Sample
```
=AVERAGE(C2:C5)
```
Result 362.5
Sample data
Download

A B C D
2 E-101 HR 350 Good
3 E-102 IT 400 Very Good
4 E-103 Sales 450 Excellent
5 E-104 Marketing 250 Average

Pro tip
Cells containing 0 ARE counted in the average, while truly blank empty cells are safely excluded.

Common pitfall
If a cell accidentally contains a literal "0", AVERAGE includes it and lowers the mean drastically.
14 AVERAGEA =AVERAGEA(value1, [value2], ...) Calculates the average of values in a list. Text and FALSE evaluate as 0; TRUE evaluates as 1. Empty cells in ranges are ignored. Statistical Analysis Intermediate Classic
Syntax
```
=AVERAGEA(value1, [value2], ...)
```
Arguments

value1

First argument or range containing mixed types.
Sample
```
=AVERAGEA(C2:C5)
```
Result 290
Sample data
Download

A B C D
2 Rohan Math 400 Passed
3 Kabir Math 360 Passed
4 Meena Math Absent Counted as 0 in AVERAGEA
5 Priya Math 400 Passed

Pro tip
If your dataset contains text strings like "N/A" or "Absent", AVERAGE ignores them, but AVERAGEA penalizes them as 0.

Common pitfall
Using AVERAGEA unintentionally on text column descriptions will severely deflate your computed average.
15 MIN =MIN(number1, [number2], ...) Finds the smallest numeric value across cells. Empty cells, text strings, and boolean values in ranges are ignored. Statistical Analysis Beginner Classic
Syntax
```
=MIN(number1, [number2], ...)
```
Arguments

number1, [number2, ...]

1 to 255 numbers, ranges, or cell references to find minimum.
Sample
```
=MIN(C2:C5)
```
Result 250
Sample data
Download

A B C D
2 E-101 HR 350 Standard
3 E-102 IT 400 Standard
4 E-103 Sales 450 Top
5 E-104 Logistics 250 Minimum

Pro tip
To find the minimum value that meets specific criteria, use MINIFS (available in Excel 2019+ and Office 365).

Common pitfall
If the referenced range contains only text strings, MIN returns 0.
16 MAX =MAX(number1, [number2], ...) Returns the maximum numeric figure in a range or argument array. Ignores empty cells and text strings. Statistical Analysis Beginner Classic
Syntax
```
=MAX(number1, [number2], ...)
```
Arguments

number1, [number2, ...]

Up to 255 numeric arguments or ranges to scan for maximum.
Sample
```
=MAX(C2:C5)
```
Result 450
Sample data
Download

A B C D
2 E-101 HR 350 Regular
3 E-102 IT 400 Regular
4 E-103 Sales 450 Highest Value
5 E-104 Marketing 250 Regular

Pro tip
Combine with MATCH and INDEX to pull the employee name associated with maximum sales: =INDEX(A2:A5, MATCH(MAX(C2:C5), C2:C5, 0)).

Common pitfall
MAX ignores numbers stored as text strings (e.g. "'500" will not be recognized as 500).
17 COUNT =COUNT(value1, [value2], ...) Counts cells containing numbers and dates. Text cells, booleans, and blank spaces are ignored. Statistical Analysis Beginner Classic
Syntax
```
=COUNT(value1, [value2], ...)
```
Arguments

value1, [value2, ...]

Values, cell references, or ranges to count numeric values.
Sample
```
=COUNT(C2:C5)
```
Result 4
Sample data
Download

A B C D
2 1 Rohan Sharma 88 Numeric
3 2 Kabir Verma 92 Numeric
4 3 Meena Gupta 75 Numeric
5 4 Priya Singh 85 Numeric

Pro tip
Dates and times in Excel are internally stored as serial numbers, so COUNT recognizes dates as valid numbers.

Common pitfall
Numbers stored formatted as text with leading apostrophes will NOT be counted by COUNT.
18 COUNTA =COUNTA(value1, [value2], ...) Counts all non-empty cells in a range. Counts numbers, text strings, formulas returning empty strings, and booleans. Statistical Analysis Beginner Classic
Syntax
```
=COUNTA(value1, [value2], ...)
```
Arguments

value1, [value2, ...]

Cells or ranges to check for any present content.
Sample
```
=COUNTA(B2:B5)
```
Result 4
Sample data
Download

A B C D
2 1 Rohan Sharma Delhi 98111...
3 2 Kabir Verma Mumbai 98222...
4 3 Meena Gupta Pune 98333...
5 4 Priya Singh Bangalore 98444...

Pro tip
Cells with formulas returning empty string "" are treated as non-empty by COUNTA.

Common pitfall
A cell containing an invisible whitespace character " " is counted as non-empty by COUNTA.
19 COUNTBLANK =COUNTBLANK(range) Counts blank cells in a range. Also counts cells with formulas that return an empty text string "". Statistical Analysis Beginner Classic
Syntax
```
=COUNTBLANK(range)
```
Arguments

range

The range from which you want to count blank cells.
Sample
```
=COUNTBLANK(C2:C5)
```
Result 2
Sample data
Download

A B C D
2 101 Rohan Sharma 9876543210 Complete
3 102 Kabir Verma Missing (Blank)
4 103 Meena Gupta 9812345678 Complete
5 104 Priya Singh Missing (Blank)

Pro tip
COUNTBLANK counts formula cells evaluating to "" as blank, making it ideal for checking unfulfilled lookups.

Common pitfall
A cell containing a space (" ") looks blank to the human eye, but COUNTBLANK will NOT count it as blank.
20 COUNTIF =COUNTIF(range, criteria) Counts cells within a range matching a single condition. Supports comparison operators (>, Statistical Analysis Intermediate Classic
Syntax
```
=COUNTIF(range, criteria)
```
Arguments

range

The continuous range of cells to count from.

criteria

The condition that must be met to increment the count.
Sample
```
=COUNTIF(B2:B5, "HR")
```
Result 2
Sample data
Download

A B C D
2 E-101 HR 350 Match 1
3 E-102 IT 400 No match
4 E-103 HR 450 Match 2
5 E-104 Sales 250 No match

Pro tip
COUNTIF is case-insensitive: "hr", "HR", and "Hr" will all match equally.

Common pitfall
When using comparison operators with numbers, enclose the operator in quotes: COUNTIF(C2:C5, ">300").
21 COUNTIFS =COUNTIFS(criteria_range1, criteria1, [criteria_range2, criteria2], ...) Applies criteria to cells across multiple ranges and counts the number of times all criteria are met. Statistical Analysis Intermediate Classic
Syntax
```
=COUNTIFS(criteria_range1, criteria1, [criteria_range2, criteria2], ...)
```
Arguments

criteria_range1

First range in which to evaluate the associated criteria.

criteria1

Condition 1 defining which cells are counted.

[criteria_range2, criteria2]

Additional ranges and corresponding conditions (up to 127 pairs).
Sample
```
=COUNTIFS(B2:B5, "HR", D2:D5, "01-Jan")
```
Result 1
Sample data
Download

A B C D
2 E-101 HR 350 01-Jan
3 E-102 IT 400 01-Jan
4 E-103 Sales 450 02-Jan
5 E-104 HR 300 02-Jan

Pro tip
Up to 127 range/criteria pairs can be chained in a single COUNTIFS statement.

Common pitfall
Every criteria range must have identical dimensions; otherwise a #VALUE! error occurs.
22 SUBTOTAL =SUBTOTAL(function_num, ref1, [ref2], ...) Returns a subtotal in a list or database. Can compute SUM, AVERAGE, COUNT, MAX, MIN, and standard deviation while excluding rows hidden by AutoFilter. Statistical Analysis Intermediate Classic
Syntax
```
=SUBTOTAL(function_num, ref1, [ref2], ...)
```
Arguments

function_num

Number 1-11 (includes manually hidden rows) or 101-111 (ignores manually hidden rows). 9 or 109 = SUM.

ref1, [ref2, ...]

One or more ranges to subtotal.
Sample
```
=SUBTOTAL(9, C2:C5)
```
Result 1450
Sample data
Download

A B C D
2 E-101 HR 350 Visible
3 E-102 IT 400 Visible
4 E-103 Sales 450 Visible
5 E-104 Operations 250 Visible

Pro tip
Use function_num 109 to ignore rows hidden manually by right-clicking "Hide"; function_num 9 respects AutoFilter only.

Common pitfall
SUBTOTAL automatically ignores any other nested SUBTOTAL formulas inside its range, avoiding double-counting.
23 LARGE =LARGE(array, k) Returns the k-th largest value in a dataset. Selects values based on their relative standing, such as 1st, 2nd, or 3rd place in rankings. Statistical Analysis Intermediate Classic
Syntax
```
=LARGE(array, k)
```
Arguments

array

The array or range of data for which you want to determine the k-th largest value.

k

The position (from the largest) in the array or cell range of data to return.
Sample
```
=LARGE(C2:C5, 2)
```
Result 400
Sample data
Download

A B C D
2 101 Rohan Sharma 350 400
3 102 Kabir Verma 400 ? Match (k=2)
4 103 Meena Gupta 450 Top 1 (k=1)
5 104 Priya Singh 250 Lowest

Pro tip
To sum the top 3 items in one formula: =SUM(LARGE(C2:C100, {1,2,3})).

Common pitfall
If k is greater than the total count of numbers in array, LARGE returns a #NUM! error.
24 SMALL =SMALL(array, k) Returns the k-th smallest value in a dataset. Useful for extracting lowest bids, runner-up costs, or earliest response times. Statistical Analysis Intermediate Classic
Syntax
```
=SMALL(array, k)
```
Arguments

array

An array or range of numerical data.

k

The position (from the smallest) to return.
Sample
```
=SMALL(C2:C5, 1)
```
Result 250
Sample data
Download

A B C D
2 101 Rohan Sharma 350 250
3 102 Kabir Verma 400 Middle
4 103 Meena Gupta 450 Highest
5 104 Priya Singh 250 ? Match (k=1)

Pro tip
Useful in legacy Excel for building dynamic ascending sorting formulas before the modern SORT function.

Common pitfall
Empty cells in the array are ignored; if the array contains no numbers, #NUM! is returned.
25 RANK =RANK(number, ref, [order]) Returns the rank of a number within a list. Order 0 or omitted ranks descending (highest = 1); order 1 ranks ascending (lowest = 1). Statistical Analysis Intermediate Classic
Syntax
```
=RANK(number, ref, [order])
```
Arguments

number

The number whose rank you want to find.

ref

An array of, or a reference to, a list of numbers.

[order]

0 or omitted for descending rank (largest is 1), non-zero for ascending.
Sample
```
=RANK(C2, $C$2:$C$5, 0)
```
Result 3
Sample data
Download

A B C D
2 101 Rohan Sharma 350 3
3 102 Kabir Verma 400 2
4 103 Meena Gupta 450 1 (Top)
5 104 Priya Singh 250 4

Pro tip
Always lock the reference range with absolute dollar signs ($C$2:$C$5) so dragging down does not shift the comparison list.

Common pitfall
Duplicate numbers receive the exact same rank and skip subsequent rank numbers (e.g., 1, 2, 2, 4).
26 RANK.EQ =RANK.EQ(number, ref, [order]) Modern successor to RANK introduced in Excel 2010. If more than one value has the same rank, the top rank of that set of values is returned. Statistical Analysis Intermediate Classic
Syntax
```
=RANK.EQ(number, ref, [order])
```
Arguments

number

The number whose rank you need.

ref

A reference to the list of numbers.

[order]

0 for descending, 1 for ascending.
Sample
```
=RANK.EQ(C2, $C$2:$C$5)
```
Result 2
Sample data
Download

A B C D
2 1 Rohan 90 2
3 2 Kabir 90 2 (Tied)
4 3 Meena 95 1
5 4 Priya 80 4

Pro tip
Preferred over legacy RANK for long-term compatibility and modern Excel standards.

Common pitfall
Ties cause subsequent rank numbers to be skipped (in 1, 2, 2, the next candidate gets rank 4, not 3).
27 RANK.AVG =RANK.AVG(number, ref, [order]) Returns the rank of a number in a list of numbers. If multiple values have the same rank, the average fractional rank is assigned to each. Statistical Analysis Intermediate Classic
Syntax
```
=RANK.AVG(number, ref, [order])
```
Arguments

number

The number to evaluate.

ref

The list of numbers against which to rank.

[order]

Direction of rank ordering (0 descending, 1 ascending).
Sample
```
=RANK.AVG(C2, $C$2:$C$5)
```
Result 2.5
Sample data
Download

A B C D
2 1 Rohan 90 2.5 (Split)
3 2 Kabir 90 2.5 (Split)
4 3 Meena 95 1.0
5 4 Priya 80 4.0

Pro tip
Use RANK.AVG when tied candidates must split prize pool portions or average percentile standings equally.

Common pitfall
Result can be a fractional decimal (e.g. 2.5 or 3.5), which may confuse report readers expecting strict integers.

	A	B	C	D
2	E-101	HR	350	Good
3	E-102	IT	400	Very Good
4	E-103	Sales	450	Excellent
5	E-104	Marketing	250	Average

	A	B	C	D
2	Rohan	Math	400	Passed
3	Kabir	Math	360	Passed
4	Meena	Math	Absent	Counted as 0 in AVERAGEA
5	Priya	Math	400	Passed

	A	B	C	D
2	E-101	HR	350	Standard
3	E-102	IT	400	Standard
4	E-103	Sales	450	Top
5	E-104	Logistics	250	Minimum

	A	B	C	D
2	E-101	HR	350	Regular
3	E-102	IT	400	Regular
4	E-103	Sales	450	Highest Value
5	E-104	Marketing	250	Regular

	A	B	C	D
2	1	Rohan Sharma	88	Numeric
3	2	Kabir Verma	92	Numeric
4	3	Meena Gupta	75	Numeric
5	4	Priya Singh	85	Numeric

	A	B	C	D
2	1	Rohan Sharma	Delhi	98111...
3	2	Kabir Verma	Mumbai	98222...
4	3	Meena Gupta	Pune	98333...
5	4	Priya Singh	Bangalore	98444...

	A	B	C	D
2	101	Rohan Sharma	9876543210	Complete
3	102	Kabir Verma		Missing (Blank)
4	103	Meena Gupta	9812345678	Complete
5	104	Priya Singh		Missing (Blank)

	A	B	C	D
2	E-101	HR	350	Match 1
3	E-102	IT	400	No match
4	E-103	HR	450	Match 2
5	E-104	Sales	250	No match

	A	B	C	D
2	E-101	HR	350	01-Jan
3	E-102	IT	400	01-Jan
4	E-103	Sales	450	02-Jan
5	E-104	HR	300	02-Jan

	A	B	C	D
2	E-101	HR	350	Visible
3	E-102	IT	400	Visible
4	E-103	Sales	450	Visible
5	E-104	Operations	250	Visible

	A	B	C	D
2	1	Rohan	90	2.5 (Split)
3	2	Kabir	90	2.5 (Split)
4	3	Meena	95	1.0
5	4	Priya	80	4.0

	A	B	C	D
2	101	Rohan Sharma	350	400
3	102	Kabir Verma	400	? Match (k=2)
4	103	Meena Gupta	450	Top 1 (k=1)
5	104	Priya Singh	250	Lowest

	A	B	C	D
2	101	Rohan Sharma	350	250
3	102	Kabir Verma	400	Middle
4	103	Meena Gupta	450	Highest
5	104	Priya Singh	250	? Match (k=1)