Excel's Rounding Functions: ROUND, MROUND, CEILING and the Penny That Never Adds Up

Here is an invoice with eight lines on it. Every line was calculated by the same formula, every line is rounded to the penny, and every line is correct.

The lines add up to 1,747.26. The same eight jobs, priced without rounding each line first, come to 1,747.25.

Nobody has made a mistake. Nobody has typed anything wrong. There is no missing transaction and no broken formula, and if you go looking for one you will spend an afternoon and find nothing — because the penny was not lost, it was created, by the act of rounding eight numbers before adding them instead of after.

That is the whole subject. Rounding in Excel is eleven functions and one checkbox, and almost all of the difficulty is not in the functions — it is in knowing when in a calculation the rounding happens, and who has to agree with the answer afterwards.

What you need. ROUND, ROUNDUP, ROUNDDOWN, INT, TRUNC, MROUND, EVEN and ODD are in every version of Excel still in use, and in Google Sheets. CEILING.MATH and FLOOR.MATH arrived in Excel 2013 — the older CEILING and FLOOR still work and behave differently with negative numbers, which is section 8. Everything here works in Excel for the web and on the Mac.

1) Two Rounding Systems, and Only One of Them Changes the Number

This is the distinction the rest of the article rests on, and it is the one people skip.

A number format changes what the cell shows. Select the cells, set two decimal places, and 1.004 appears as 1.00. The cell still holds 1.004. Every formula that reads it still reads 1.004.

ROUND changes what the cell is. =ROUND(A1, 2) returns 1.00 as a value. Anything downstream sees 1, because that is now the number.

Put three cells holding 1.004 next to each other, format them all to two decimals, and total them:

Three cells that each say 1.00 and a total that says 3.01. Every cell on that screen is displaying its own value correctly. This is the single most common "Excel is broken" support ticket in existence, and the answer is always the same: the format is a costume, not a number.

Which one you want depends entirely on who reads the answer:

• Format when the underlying precision is real and you are only tidying the screen — measurements, averages, model outputs, anything that will be calculated with again.
• ROUND when the rounded number is the fact — an amount somebody pays, a quantity somebody picks, a figure that appears on a document and must agree with the arithmetic printed beside it.

An invoice is the second kind. You cannot charge a customer 222.625, so the penny amount is not a display convenience, it is the number. Round it.

2) ROUND, ROUNDUP, ROUNDDOWN, and the Second Argument

All three take the same two arguments and differ only in direction:

=ROUND(number, num_digits)       nearest
=ROUNDUP(number, num_digits)     away from zero, always
=ROUNDDOWN(number, num_digits)   toward zero, always

The second argument is a count of decimal places, and it is the part worth learning properly, because it does not stop at zero:

Negative digits round to the left of the decimal point, and almost nobody uses them. They are the honest way to build a summary in thousands: =ROUND(SUM(F2:F9), -3) gives you a number that genuinely is a multiple of a thousand, as opposed to a custom format like #,##0,"k" which merely draws one. Use the format when the reader is a human and the precision underneath still matters. Use the negative digits when the rounded figure is what gets carried forward.

Note also what ROUNDUP and ROUNDDOWN actually mean. They are away from zero and toward zero, not "up" and "down" on the number line. =ROUNDUP(-2.1, 0) is -3. =ROUNDDOWN(-2.1, 0) is -2. If your data has negatives in it — refunds, credits, variances — say the direction out loud before you pick the function, because the words are lying to you.

3) INT, TRUNC, and the Negative Number That Separates Them

Two functions that appear to do the same job and stop agreeing the moment a minus sign turns up.

INT rounds down, toward negative infinity. TRUNC cuts the decimals off, toward zero.

For positive numbers they are interchangeable, which is exactly why the bug survives: the sheet works for a year and then somebody enters a credit note.

Two things worth knowing beyond that. TRUNC takes an optional second argument, so =TRUNC(1747.269, 2) is 1747.26 — a chop, not a round. And INT is the standard way to split a date-time into its parts: =INT(A1) is the date, =A1-INT(A1) is the time, because Excel stores the pair as one number with the clock in the decimals.

While we are here: =EVEN(3.1) is 4 and =ODD(3.1) is 5. Both round away from zero to the next integer of that parity. They exist for packaging and pairing problems, and you will use them roughly once.

4) The Tie Rule: Half Away From Zero, Always

When a number sits exactly halfway, Excel rounds away from zero. =ROUND(2.5, 0) is 3. =ROUND(-2.5, 0) is -3. There is no option, no setting and no alternative function.

That matters because a lot of the world does not round that way. Banker's rounding — round-half-to-even — sends 2.5 to 2 and 3.5 to 4, so that a long column of ties does not drift upward. It is the default in Python, in C#'s Math.Round, in the IEEE 754 standard, and in a great many accounting and statistical packages. Excel does not implement it anywhere, which means an Excel total and a total from the system that feeds it can differ by a penny per tie and both be right by their own rules.

Ties are rarer than they look, because a decimal like 0.145 is not stored as exactly 0.145 — but they are not rare in money, because halves of a penny land on values a binary computer can store exactly: .125, .375, .625, .875. The invoice in this article has three of them.

If you genuinely need banker's rounding — usually because something downstream demands it — this is it, for positive amounts, to two decimals:

=LET(n, A1*100,
     f, n-0.5,
     IF(MOD(n,1)=0.5,
        IF(ISEVEN(f), f, f+1)/100,
        ROUND(A1, 2)))

Read it as: scale to pennies, and if what is left is exactly half a penny, take the nearest even penny; otherwise round normally. Test it on 2.225, 2.235 and 2.245 before you trust it, and do not reach for it unless a specification actually asks — an invoice that disagrees with a customer's expectation by a penny is a phone call, and "we round to even" is a hard sentence to say on that call.

5) The Fifteen Digits Underneath

Excel stores numbers as 64-bit binary floating point and keeps 15 significant digits. Both halves of that sentence cause real problems.

The 15 digits. Type a 16-digit number — a card number, a long reference, an IBAN fragment — and the last digit becomes a zero, silently and permanently. Nothing warns you. Anything that is an identifier rather than a quantity belongs in a text column, formatted as Text before the paste, not after.

The binary. Most decimal fractions have no exact binary form, the same way a third has no exact decimal form. Excel hides this well, and then occasionally does not:

=(43.1-43.2)+1     shows 0.9, and is not 0.9

Widen the column to fifteen decimals and you get 0.899999999999999. Which is fine until you write =IF((43.1-43.2)+1=0.9, "match", "no") and get no from a spreadsheet that is displaying 0.9 in both cells.

The fix is not to be cleverer about floating point. It is to round before you compare, or compare with a tolerance:

=IF(ROUND(A1, 10)=ROUND(B1, 10), "match", "no")
=IF(ABS(A1-B1)<0.0000001, "match", "no")

This is also why a SUMIF occasionally misses a row that looks identical to the criteria, and why a reconciliation shows a variance of 0.00 that is not zero. If a difference should be zero and is not, wrap it in ROUND before you go looking for the missing transaction.

6) Billing to the Next Quarter Hour

🎯 Scenario: Eight jobs, a timer that records to the hundredth of an hour, and a contract that bills in quarter-hour blocks with money to the penny.

The grid below is the month. Raw hours in C, rate in D, and E and F empty because the billing rule fills them.

Two formulas do the whole job:

E2:  =CEILING.MATH(C2, 0.25)
F2:  =ROUND(E2*D2, 2)

CEILING.MATH(number, significance) rounds up to the next multiple of whatever you give it. A quarter of an hour is 0.25 when hours are stored as a decimal. Fill both down and the month reads:

Three rows are teaching something.

J-2205 and J-2208 did not move. 2.50 and 2.25 are already exact multiples of a quarter, and CEILING.MATH leaves an exact multiple alone. It rounds up to the next multiple or stays put — it does not always jump. People expect a "ceiling" to always increase and write a bug report when it does not.

J-2203 ran 7.8 minutes and is billed for fifteen. The rate makes that 23.81 instead of 12.38 — the client pays 92% more than the work took. That is not Excel being wrong, it is the contract, and it is why minimum billing increments are negotiated rather than assumed.

J-2206 ran 1.2 minutes past six hours and is billed for six and a quarter. Seventy-two seconds of overrun bought 13.8 minutes of billing, worth 21.91. Every quarter-hour rule has this cliff at the top of each block, and everyone who has ever been billed by one has noticed.

7) MROUND, and the #NUM! That Means Your Signs Disagree

🎯 Scenario: A customer wants 1,180 units of something that only ships in cases of 24.

CEILING.MATH always goes up. FLOOR.MATH always goes down. MROUND goes to the nearest multiple:

=MROUND(1180, 24)        1176   — 49 cases, nearest
=CEILING.MATH(1180, 24)  1200   — 50 cases, enough to cover the order
=FLOOR.MATH(1180, 24)    1176   — 49 cases, never over

An order of 1,180 units in cases of 24 is 49.17 cases. Which of those three numbers is right depends on a question that is not arithmetic: may you ship short? If not, CEILING.MATH is the only correct answer and the extra 20 units are the cost of the case pack.

MROUND breaks ties the same way ROUND does — away from zero, when the remainder is exactly half the multiple. =MROUND(7.5, 5) is 10.

And it has one trap that CEILING.MATH and FLOOR.MATH do not: MROUND returns #NUM! if the number and the multiple have different signs. =MROUND(-23, 5) is an error, not -25. You want =MROUND(-23, -5), or, more usefully in a formula that has to survive both signs:

=MROUND(A1, SIGN(A1)*5)

Two more rounding-to-a-multiple jobs you will actually meet:

=MROUND(A1, 0.05)              cash rounding to the nearest 5 cents
=FLOOR.MATH(A1, 1) - 0.05      the next .95 price point below A1
=MROUND(A1, 1/24)              the nearest whole hour, in a date-time

8) The Mode Argument, and the Two Old Functions

CEILING.MATH and FLOOR.MATH each take an optional third argument, and it does nothing at all for positive numbers. For negatives it is the whole story.

=CEILING.MATH(number, [significance], [mode])
=FLOOR.MATH(number, [significance], [mode])

With mode omitted or zero, CEILING.MATH rounds a negative number toward zero (-4.1 → -4) and FLOOR.MATH rounds it away from zero (-4.1 → -5). Give mode any non-zero value and each one flips.

The way to keep it straight is to stop thinking "up" and "down" and think about direction on the number line versus distance from zero. If your data has negatives, write the mode argument explicitly even when it matches the default — the person reading your formula next year should not have to look it up.

The older CEILING and FLOOR are still in Excel and still work. They handle negatives differently, and in some builds a number and significance with opposite signs give you #NUM! instead of an answer. There is no reason to write a new formula with them. If you inherit one, and the column can contain a negative, test it before you trust it.

9) Time Is a Fraction of a Day

Excel stores a time as the fraction of a day it represents: 6:00 is 0.25, noon is 0.5, 18:00 is 0.75. Which means every function in this article works on time, as long as you give it a multiple written the same way.

=MROUND(A2, TIME(0,15,0))         nearest quarter hour
=CEILING.MATH(A2, TIME(0,15,0))   next quarter hour
=FLOOR.MATH(A2, TIME(1,0,0))      the hour it started in
=MROUND(A2, "0:15")               the same thing, if you prefer text

A clock-in at 08:07 becomes 08:15 with CEILING.MATH and 08:00 with MROUND. Format the result as time — the value is a fraction, and an unformatted cell shows you 0.34375, which is correct and unhelpful.

The trap here is durations over 24 hours. A total of 26 hours is 1.0833, and a normal time format shows it as 02:00 because it wraps at midnight. The format [h]:mm — square brackets on the hours — is what stops the wrap. That is a formatting fix, not a rounding one, but it is where a rounded timesheet total usually goes wrong.

10) The Penny That Never Adds Up

Back to the invoice, and the difference we started with.

• Add the exact line values and round once: 1,747.25
• Round each line and add the rounded values: 1,747.26

Both are defensible. Only one of them can go on the document, and the rule is not a matter of taste:

The number the customer pays is the sum of the lines they can see. An invoice that prints eight amounts and a total that is not their sum is wrong, whatever the underlying arithmetic says. So round at the line, total the rounded lines, and let the total be a penny away from the theoretical figure. The alternative — a correct total with lines that do not add to it — is the version that gets queried.

The same applies once more to the tax:

A penny again, in the same direction, for the same reason. Which one is correct is a question for your tax authority rather than for Excel — many jurisdictions specify it explicitly — and the only bad answer is doing it one way in the sheet and the other way in the system that files the return.

And there is a third answer nobody expects. Under banker's rounding, the three exact ties in this invoice — 222.625, 325.375 and 154.125 — go to 222.62, 325.38 and 154.12, and the invoice totals 1,747.24. Three plausible totals, from one set of eight correct lines, and a penny between each of them.

The lesson is not that rounding is dangerous. It is that where you round is a decision with an owner, and if nobody makes it explicitly, the sheet makes it for you.

11) The Pie Chart That Adds to 99%

🎯 Scenario: The same eight jobs, split by client, on a slide that says 100% underneath it.

Same invoice, grouped by client, expressed as a share of the total:

Four correctly rounded percentages that total 99. At one decimal place it is no better: 23.0 + 14.1 + 35.4 + 27.4 = 99.9.

You cannot fix this by rounding differently, because it is not a rounding error — four numbers that each lose a fraction cannot add to what they added to before. What you can do is choose who absorbs it. The standard method is largest remainder: round everything down, count the shortfall, and hand the missing units to the rows with the biggest discarded fractions.

Here, rounding down gives 23 + 14 + 35 + 27 = 99, one short. The discarded fractions are .0435, .0792, .4338 and .4435 — so Novak Print takes the point and becomes 28%. The four shares now read 23, 14, 35, 28 and total exactly 100.

In a sheet, the pragmatic version is to compute the largest row as a residual:

=IF(B2=MAX($B$2:$B$5),
    100 - SUM(ROUND($B$3:$B$5/$B$6*100, 0)),
    ROUND(B2/$B$6*100, 0))

Or, if the chart is the only consumer, do not round at all — let the labels carry one decimal and the discrepancy disappears below what anyone reads. What you must not do is put four numbers next to a "100%" that they do not sum to and hope the meeting moves quickly.

12) The Checkbox That Deletes Your Data

There is a setting in File ▸ Options ▸ Advanced ▸ When calculating this workbook called Set precision as displayed. It sounds like it fixes everything in this article. It does something else.

Turning it on permanently rewrites every stored number in the workbook to the value its format displays. 1.004 shown as 1.00 becomes 1 — in the cell, in the file, forever. Turning the setting back off does not restore anything, because the original values are gone. Undo does not reach it either.

There are legitimate uses — a fixed-precision ledger where the displayed value is by definition the only value — and they are rare enough that the honest advice is: do not turn it on. If a workbook arrives with it already on, the numbers in it have already been altered, and the source data is somewhere else.

ROUND does the same job at the granularity of one formula, visibly, reversibly, and where a reviewer can see it. Use it.

13) Which Function, For Which Job

And one rule that outranks the table: round once, as late as you can, at the point where the number becomes a fact somebody else relies on. Rounding an intermediate result because it looked untidy is how a model ends up 40 out on a figure of 12,000.

14) What the Rounding Rule Is Actually Worth

One last number, because it is the reason this article uses an invoice rather than a maths example.

The eight jobs took 22.15 hours and are billed as 23.00. The work, priced at the raw hours, is worth 1,678.91. The invoice says 1,747.26.

The rounding rule is worth 68.35 — 4.1% on top of what the hours were worth, from a rule that nobody in the room thinks of as a pricing decision. Fifty-one minutes of billing that was not worked.

That is not an accusation — quarter-hour billing is standard, defensible, and cuts the other way when a job runs long and gets written down. The point is that it is a policy, worth 4.1% here, and it lives in one argument of one function in column E. Change 0.25 to 0.1 and the same month bills 22.50 hours instead of 23.00. Change CEILING.MATH to MROUND and it bills 22.25.

Three characters, in a formula nobody reviews, moving 4% of a number the whole business is measured on. Every function in this article is that small and that consequential. Know which one you have written, and know why.

15) Mini Exercises

Copy the grid into a blank sheet starting at A1. Each answer is one formula.

1. Bill the month. Put =CEILING.MATH(C2, 0.25) in E2 and =ROUND(E2*D2, 2) in F2, fill both to row 9, and report the two totals. Then say which two rows did not move in column E, and why.
2. Round late instead. In a spare cell, compute the invoice without rounding the lines: =ROUND(SUMPRODUCT(E2:E9, D2:D9), 2). State the difference from SUM(F2:F9) and which of the two you would print.
3. Prove the format is a costume. Put 1.004 in three cells, format them to two decimals, and total them. Write down what the three cells show, what the total shows, and one sentence explaining it to a colleague.
4. The tie. J-2201 is exactly 222.625 before rounding. Say what ROUND(222.625, 2) returns, what banker's rounding would return, and how many of the eight lines are exact ties.
5. Change the increment. Rebuild column E with 0.1 instead of 0.25, then again with MROUND instead of CEILING.MATH. Report all three billed-hour totals and say which one you would put in a contract.
6. Signs. Enter =MROUND(-23, 5) and describe what you get. Then write a version that returns -25, and a version that works for a column containing both signs.
7. Negatives, properly. Build a small column of 2.7, -2.7, 0.5 and -0.5 and put INT, TRUNC, ROUND, ROUNDUP and ROUNDDOWN beside it. Circle every cell where two functions disagree.
8. The 99% chart. Group the eight jobs by client, compute each share as a whole percentage, and total them. Then apply the largest-remainder fix and say which client gained a point and why it was that one.

Summary

A number format changes what a cell shows; ROUND changes what it is. Only the second one affects a total, and the choice between them is a question about who reads the answer — a screen, or a document somebody pays against.

The functions divide cleanly. ROUND/ROUNDUP/ROUNDDOWN for decimal places, including negative ones for tens, hundreds and thousands. INT and TRUNC for whole numbers, and they disagree on negatives. MROUND, CEILING.MATH and FLOOR.MATH for multiples — quarter hours, case packs, price points, time slots — with the mode argument mattering only when the number can be negative, and MROUND alone returning #NUM! when the signs disagree.

Excel rounds halves away from zero, always, and has no banker's rounding. It keeps 15 significant digits, which loses the last digit of a 16-digit reference and makes (43.1-43.2)+1 refuse to equal 0.9. Round before you compare, or compare with a tolerance.

And the part that costs real money is not any of the functions. It is when the rounding happens. Round each line and this invoice is 1,747.26; round once at the end and it is 1,747.25; use banker's rounding and it is 1,747.24. Somebody has to choose, once, and write it down — because the penny is not an error to be found. It is a decision that was already made, by whoever typed the formula.