Excel Math Functions: MIN, MAX, ROUND, MOD, ABS, LARGE, SMALL

Excel's math and statistical functions go well beyond SUM and AVERAGE. This guide covers the functions you will use every time you build financial models, clean data, or rank results: MIN, MAX, ROUND, MOD, ABS, LARGE, SMALL, RANK, CEILING, and FLOOR.

MIN and MAX

MIN returns the smallest number in a range. MAX returns the largest. Syntax:

=MIN(number1, [number2], ...)
=MAX(number1, [number2], ...)

Examples:

=MIN(A1:A10) returns the lowest value in A1:A10
=MAX(B1:B100) returns the highest value in B1:B100
=MIN(5, 12, 3, 8) returns 3
=MAX(A1:A10, B1:B10) returns the maximum across both ranges

Note: MIN and MAX ignore empty cells and text. They do not ignore zeros — if your data contains zero, MIN may return zero even if that is not meaningful. Use =MINIFS(A1:A10, A1:A10, ">0") to find the minimum value greater than zero. Practical use: =MAX(A1:A10) - MIN(A1:A10) calculates the range (spread) of a dataset in one formula.

ROUND, ROUNDUP, ROUNDDOWN

These functions control how numbers are rounded.

ROUND

=ROUND(number, num_digits) rounds to the nearest value at the specified number of decimal places.

Positive num_digits: round to that many decimal places
0: round to the nearest integer
Negative num_digits: round to the left of the decimal (tens, hundreds, etc.)

Examples:

=ROUND(3.567, 2) returns 3.57
=ROUND(3.543, 2) returns 3.54
=ROUND(3.5, 0) returns 4 (rounds .5 up)
=ROUND(1234, -2) returns 1200 (rounds to nearest hundred)

ROUNDUP

=ROUNDUP(number, num_digits) always rounds away from zero, regardless of the digit value. Examples:

=ROUNDUP(3.001, 2) returns 3.01 (rounds up even though the digit is 1)
=ROUNDUP(-3.1, 0) returns -4 (away from zero for negatives)

Use case: Calculating ceiling prices, ceiling quantities, or any situation where you must always round upward.

ROUNDDOWN

=ROUNDDOWN(number, num_digits) always rounds toward zero. Examples:

=ROUNDDOWN(3.999, 2) returns 3.99
=ROUNDDOWN(9.9, 0) returns 9

Use case: Calculating how many complete units fit in a given space, or truncating time values to the nearest hour.

INT — Floor to Integer

INT rounds a number down to the nearest integer (toward negative infinity). Syntax: =INT(number) Examples:

=INT(3.9) returns 3
=INT(-3.1) returns -4 (rounds toward negative infinity, not toward zero)
=INT(3.0) returns 3

INT vs. ROUNDDOWN: =ROUNDDOWN(3.9, 0) also returns 3. But =ROUNDDOWN(-3.1, 0) returns -3 (toward zero), while =INT(-3.1) returns -4 (toward negative infinity). For positive numbers, they behave identically. Practical use: Extract the hour portion from a decimal time value. If A1 is 9.75 (representing 9 hours 45 minutes), =INT(A1) returns 9.

MOD — Remainder

MOD returns the remainder after dividing one number by another. The result has the same sign as the divisor. Syntax: =MOD(number, divisor) Examples:

=MOD(10, 3) returns 1 (10 ÷ 3 = 3 remainder 1)
=MOD(15, 5) returns 0 (15 divides evenly by 5)
=MOD(7, 2) returns 1 (7 is odd)

Practical uses:

Identify even and odd rows: =MOD(ROW(), 2) returns 0 for even rows and 1 for odd rows. Use this in conditional formatting to shade alternating rows.

Check divisibility: =IF(MOD(A1, 2) = 0, "Even", "Odd").

Extract minutes from a decimal time: If A1 is 9.75 hours, =MOD(A1, 1) * 60 returns 45 minutes.

ABS — Absolute Value

ABS returns the absolute value of a number — the distance from zero, always positive. Syntax: =ABS(number) Examples:

=ABS(-5) returns 5
=ABS(5) returns 5
=ABS(A1 - B1) returns the difference between A1 and B1 regardless of which is larger

Practical uses:

Calculate variance or deviation: =ABS(Actual - Budget) gives the absolute difference regardless of direction.

Normalize negative values: If imported data has some values accidentally negative, =ABS(A1) corrects them.

LARGE and SMALL

LARGE returns the k-th largest value in a dataset. SMALL returns the k-th smallest. Syntax:

=LARGE(array, k) — k=1 is largest, k=2 is second largest, etc.
=SMALL(array, k) — k=1 is smallest, k=2 is second smallest, etc.

Examples:

=LARGE(A1:A10, 1) — same as MAX
=LARGE(A1:A10, 2) — second largest value
=LARGE(A1:A10, 3) — third largest value
=SMALL(A1:A10, 1) — same as MIN
=SMALL(A1:A10, 2) — second smallest value

Practical use: Find the top-3 sales figures. Put =LARGE($A$1:$A$100, ROW(A1)) in a cell and drag down — each row returns the next rank. Note: If there are duplicate values, LARGE and SMALL count each duplicate separately. =LARGE({5, 5, 3}, 2) returns 5, not 3.

RANK — Rank a Value in a List

RANK returns the rank of a number within a list. Syntax: =RANK(number, ref, [order])

number — the value to rank
ref — the range to rank within
order — 0 (or omitted) = descending (largest = rank 1), 1 = ascending (smallest = rank 1)

Examples:

=RANK(A1, A$1:A$10, 0) — rank of A1 in A1:A10, highest first
=RANK(85, A1:A10, 0) — rank of score 85 in the list

Note: If two values are tied, they receive the same rank, and the next rank is skipped. Two values tied at rank 3 are both rank 3; there is no rank 4. RANK.EQ vs. RANK: RANK.EQ is the updated version (Excel 2010+) and behaves identically to RANK. There is also RANK.AVG, which assigns the average rank to tied values (e.g., two values tied at ranks 3 and 4 both get rank 3.5).

CEILING and FLOOR

These functions round numbers to the nearest specified multiple.

CEILING

=CEILING(number, significance) rounds a number up to the nearest multiple of significance. Examples:

=CEILING(4.3, 1) returns 5 (next whole number up)
=CEILING(4.3, 0.5) returns 4.5
=CEILING(23, 5) returns 25 (next multiple of 5)
=CEILING(60, 15) returns 60 (already a multiple of 15)

Use case: Calculate how many boxes are needed to pack items. If each box holds 12 and you have 50 items: =CEILING(50, 12) / 12 returns 5 boxes.

FLOOR

=FLOOR(number, significance) rounds a number down to the nearest multiple of significance. Examples:

=FLOOR(4.7, 1) returns 4
=FLOOR(4.7, 0.5) returns 4.5
=FLOOR(23, 5) returns 20 (nearest multiple of 5 below 23)

Use case: Round time to the nearest 15 minutes: =FLOOR(A1, TIME(0, 15, 0)) rounds a time value down to the last 15-minute mark.

Frequently Asked Questions

What is the difference between ROUND and INT? ROUND(number, 0) rounds to the nearest integer — values of 0.5 or higher round up, below 0.5 round down. INT always rounds down to the nearest integer (toward negative infinity). For positive numbers, INT(3.9) and ROUNDDOWN(3.9, 0) both give 3. For negative numbers: INT(-3.1) gives -4, but ROUND(-3.1, 0) gives -3. How does MOD work with negative numbers? MOD returns a result with the same sign as the divisor. =MOD(-7, 3) returns 2 (not -1), because Excel calculates it as -7 - (3 × FLOOR(-7/3, 1)) = -7 - (3 × -3) = 2. This is consistent with mathematical modulo but may differ from other programming languages. Can LARGE return a position instead of a value?

No — LARGE returns the value, not the position. To find the row number where the largest value appears, use =MATCH(MAX(A1:A10), A1:A10, 0) or =MATCH(LARGE(A1:A10, 2), A1:A10, 0) for the second largest.

What is the difference between RANK and LARGE?

LARGE gives you the value at a given rank position: =LARGE(A1:A10, 3) gives the 3rd largest value. RANK goes the other direction: given a value, it tells you its rank: =RANK(85, A1:A10, 0) tells you what rank 85 holds. Use LARGE to build ranked lists; use RANK to add a rank column to existing data.

Why does CEILING return a #NUM! error?

A #NUM! error from CEILING usually means you mixed signs — a positive number with a negative significance, or vice versa. =CEILING(-4, 1) works (returns -4), but =CEILING(4, -1) returns #NUM!. Make sure the significance has the same sign as the number being rounded.