Excel ROUND, MOD, RANK and AVERAGE Functions

Math and statistics functions in Excel do the arithmetic that sits behind almost every report: totals, rounding, remainders, averages, rankings, and measures of how spread out a set of numbers is. SUM, ROUND, MOD, AVERAGE, MEDIAN, RANK and STDEV are the ones you will meet most. They are simple to call, and they are also where quiet mistakes happen: a total that includes hidden rows, an average that counts zeros, a rank that skips a number, or a rounding function that goes the “wrong” way for negative numbers.

This guide explains each group, shows the results side by side, and points out where the functions behave in a way that surprises people. Every result below was produced by running the formula in Microsoft Excel.

Operators and the order of calculation

Excel calculates brackets first, then powers (^), then multiplication and division from left to right, then addition and subtraction from left to right, and & (joining text) comes after the arithmetic. Two results here differ from what maths textbooks give, so they are worth knowing:

MOD, QUOTIENT and other basics

MOD(number, divisor) is the remainder after division, and QUOTIENT(number, divisor) is the whole-number part of the division. MOD is the tool for “every 5th row”, odd or even tests, and cycling numbers. A1 holds 17 and A2 holds -7:

Rounding functions

There are several ways to round, and they differ mostly for negative numbers and for numbers exactly halfway. Each column below is a number, each row is a function:

Look at the -2.5 column. INT gives -3 but TRUNC gives -2. CEILING(-2.5, 5) is 0, because it rounds up towards positive numbers, and FLOOR(-2.5, 5) is -5. MROUND(-2.5, 5) is #NUM!, because MROUND refuses a negative number combined with a positive multiple. Check negative inputs before you rely on any of these.

Rounding at the half point can look odd when the number is stored in binary. Excel rounds to the 15 digits it keeps, so it gives the answer most people expect:

Counting and averaging when cells are blank, zero or text

Excel’s counting and averaging functions each treat blanks, text and zeros differently. A1:A5 hold 10, the text 20, an empty cell, 0 and 30:

The practical point: a blank cell is not a zero. If blank means “no data yet”, AVERAGE is right. If blank should count as zero, type the zero.

Mean, median and mode

The mean (AVERAGE) adds everything and divides by the count. The median (MEDIAN) is the middle value once the numbers are sorted. The mode (MODE.SNGL) is the most frequent value. When a few values are extreme, the mean is dragged towards them and the median is not. Five employees earn 20, 25, 30, 35 and 500 thousand:

Weighted average

When items count for different amounts, use SUMPRODUCT(values, weights) / SUM(weights). A plain average treats Maths, Science and English equally; the weighted one respects the weights in column C:

What the sheet shows

What is inside the cells (Show Formulas)

Ranking and percentiles

RANK.EQ(value, list, [order]) gives the position of a value in a list: 1 is the largest unless you pass 1 as the order. Equal values share a rank and the next rank is skipped. RANK.AVG gives tied values the average of their ranks:

Two students scored 92 and both are rank 1, so 85 is rank 3, not 2. LARGE and SMALL return the nth biggest or smallest value instead of a position, and QUARTILE.INC and PERCENTILE.INC cut the sorted list at a proportion:

Spread: standard deviation and variance

The standard deviation tells you how far values typically sit from the mean. Variance is the standard deviation squared. Excel has two versions, and the choice matters: use the .S versions (sample) when your numbers are a sample of a bigger group, and the .P versions (population) when they are the whole group. The list 2, 4, 4, 4, 5, 5, 7, 9 has a mean of 5:

Hidden rows, filters and errors

SUM adds every cell in the range, including cells in hidden rows. SUBTOTAL and AGGREGATE can skip them. In SUBTOTAL, function 9 is SUM and 109 is SUM that also ignores rows you hid by hand (both ignore rows removed by a filter). In AGGREGATE(function, options, range), option 5 ignores hidden rows and option 6 ignores errors. Here A1:A4 hold 10, 20, 30 and 40 and row 2 is hidden:

Errors spread: one error inside a SUM range makes the whole total an error. AGGREGATE with option 6 steps over them:

Why 0.1 + 0.2 is not quite 0.3

Computers store most decimals in binary, where 0.1 and 0.2 cannot be written exactly. The tiny error is usually invisible because Excel shows 15 digits and cleans up the last steps, but it can show through in comparisons:

The habit that protects you: when you compare the results of calculations with decimals, wrap them in ROUND(..., 10) (or a suitable number of places) first.

Common mistakes

Mistake 1: averaging averages

The average of two group averages is only right if the groups are the same size. Use SUMPRODUCT with the counts (a weighted average), or average the raw values.

Mistake 2: relying on the display for rounding

A cell formatted to two decimals still holds all the digits. Use ROUND when the stored value must change.

Mistake 3: expecting SUM to skip hidden rows

It does not. Use SUBTOTAL(109, ...) or AGGREGATE(9, 5, ...) for a total that follows what is visible.

Mistake 4: a blank that should be zero

AVERAGE ignores blank cells, so a missing zero raises the average. Type the 0 when the value is really zero.

Mistake 5: ranking ties

Tied values share a rank and the following rank is skipped. If you need unique ranks, add a tie-breaker such as +COUNTIF($A$2:A2, A2) - 1.

Cheat sheet

Try it yourself

Work out each answer first, then open the solution.

1. A1 holds 47.32. Give the value rounded up to a whole number, rounded to one decimal, floored to a multiple of 10, and rounded to the nearest 5.

Show solution

A1 holds 47.32

Formula	Excel shows
=ROUNDUP(A1,0)	48
=ROUND(A1,1)	47.3
=FLOOR(A1,10)	40
=MROUND(A1,5)	45
=INT(A1)	47

2. Return the word even or odd for the numbers 14, 15 and -4.

Show solution

A1 = 14, A2 = 15, A3 = -4

Formula	Excel shows
=IF(MOD(A1,2)=0,"even","odd")	even
=IF(MOD(A2,2)=0,"even","odd")	odd
=IF(MOD(A3,2)=0,"even","odd")	even

The test MOD(n, 2) = 0 works for negative numbers too, because MOD(-4, 2) is 0.

3. Salaries are 20, 25, 30, 35 and 500. Which measure, the average or the median, describes a typical salary better?

Show solution

Five salaries

Formula	Excel shows
=AVERAGE(A1:A5)	122
=MEDIAN(A1:A5)	30

The median (30). The average of 122 is higher than four of the five people earn, because of the single 500.

4. In the scores 85, 92, 78, 92 and 60, what rank does 85 get, and how could you get the same rank without RANK.EQ?

Show solution

Ranking 85 and 92

Formula	Excel shows	Why
=RANK.EQ(85,A1:A5)	3	two scores are higher, so rank 3
=RANK.EQ(92,A1:A5)	1
=COUNTIF(A1:A5,">"&85)+1	3	count how many values are larger, then add one

Frequently asked questions

How does Excel’s order of operations work?

Brackets first, then powers, then multiplication and division, then addition and subtraction, each left to right. One exception to remember: Excel applies a leading minus before a power, so =-2^2 gives 4.

What is the difference between ROUND, ROUNDUP, ROUNDDOWN and INT?

ROUND goes to the nearest value, ROUNDUP away from zero, ROUNDDOWN towards zero, and INT down to the next lower whole number, which differs from ROUNDDOWN for negative numbers.

How do I round to the nearest 5 or 10 in Excel?

=MROUND(A1, 5) for the nearest multiple, =CEILING(A1, 5) to round up to a multiple and =FLOOR(A1, 5) to round down.

What does MOD do in Excel?

MOD(number, divisor) returns the remainder after division. It is used for even and odd tests, every nth row, and wrapping numbers around.

What is the difference between AVERAGE and MEDIAN?

AVERAGE is the sum divided by the count. MEDIAN is the middle value of the sorted list. The median is less affected by very large or very small values.

Does AVERAGE include zeros and blank cells?

Zeros are included. Blank cells and text are ignored. AVERAGEA counts text as zero.

What is the difference between STDEV.S and STDEV.P?

STDEV.S is for a sample of a larger group and divides by n – 1. STDEV.P is for a whole population and divides by n.

How do I sum only visible cells after filtering?

Use SUBTOTAL(109, range) or AGGREGATE(9, 5, range). Plain SUM also adds the hidden and filtered-out rows.