# How to Round Numbers in Excel & Google Sheets: ROUND, MROUND, CEILING, FLOOR

Excel and Google Sheets have at least eight rounding functions, and they disagree the moment a number goes negative. Here is what ROUND, MROUND, ROUNDUP, ROUNDDOWN, CEILING.MATH, FLOOR.MATH, INT, and TRUNC actually return, why formatting is not rounding, and which one to use for money, time, and quantities.

---

- **Canonical URL:** https://dothecalculation.com/blog/math/how-to-round-numbers-excel-google-sheets
- **Category:** Math
- **Author:** Do The Calculation Team
- **Published:** 2026-09-17
- **Last updated:** 2026-09-17
- **Reading time:** 22 min read
- **Publisher:** Do The Calculation (https://dothecalculation.com)
- **Methodology:** https://dothecalculation.com/methodology

---

Rounding is one of the most common things anyone does in a spreadsheet, and one of the easiest to get subtly wrong. Excel and Google Sheets both offer a whole family of functions for it — ROUND, MROUND, ROUNDUP, ROUNDDOWN, CEILING.MATH, FLOOR.MATH, INT, and TRUNC, plus a few older and more specialised ones — and they all agree on 2.9. They stop agreeing on -2.9, on a value that is exactly halfway, and on anything that has only been formatted to look rounded.

This guide covers the difference between formatting and rounding, what each function does (including its behaviour with negative numbers), when to use which, the places where Excel and Google Sheets differ, eight practice problems, and the mistakes that cause a total to be a cent off. For the underlying rules themselves — half up, half even, truncation — see the [complete guide to rounding rules](/blog/math/rounding-rules-complete-guide).

## Quick Answer

- Number formatting only changes what a cell displays. The full value is still used in every calculation. Rounding functions change the value itself.
- For ordinary rounding use =ROUND(number, digits). Positive digits round to decimal places, 0 rounds to a whole number, negative digits round to tens, hundreds, and so on.
- ROUND breaks ties away from zero: =ROUND(2.5, 0) is 3 and =ROUND(-2.5, 0) is -3. It is not banker's rounding.
- ROUNDUP and ROUNDDOWN move away from and toward zero, not up and down the number line: =ROUNDUP(-2.1, 0) is -3.
- To round to a multiple (nearest 5, nearest 0.05, nearest 15 minutes), use MROUND for nearest, CEILING.MATH to always go up, FLOOR.MATH to always go down.
- INT rounds down toward negative infinity (=INT(-12.345) is -13); TRUNC just drops digits (=TRUNC(-12.345) is -12). They only differ on negative numbers.

**The eight core rounding functions at a glance**
| Function | What it does | Example | Result |
| --- | --- | --- | --- |
| ROUND | Nearest value at a given number of digits | =ROUND(2.5, 0) | 3 |
| MROUND | Nearest multiple of a given factor | =MROUND(1.21, 0.05) | 1.2 |
| ROUNDUP | Away from zero, at a given number of digits | =ROUNDUP(2.1, 0) | 3 |
| ROUNDDOWN | Toward zero, at a given number of digits | =ROUNDDOWN(2.9, 0) | 2 |
| CEILING.MATH | Up to a multiple | =CEILING.MATH(2.3, 1) | 3 |
| FLOOR.MATH | Down to a multiple | =FLOOR.MATH(2.9, 1) | 2 |
| INT | Down to an integer (toward negative infinity) | =INT(-12.345) | -13 |
| TRUNC | Drops digits (toward zero) | =TRUNC(-12.345) | -12 |

Tool: [Check a Result Before You Build the Formula](https://dothecalculation.com/calculators/rounding-calculator) — The rounding calculator rounds to decimal places or significant figures and shows which digit decided the result, which is a quick way to confirm what a spreadsheet formula should return.

## Rounding with Number Formatting vs. Functions

Most rounding confusion in spreadsheets starts here, so it is worth being precise about what each approach does.

### Number formatting changes the display only

In Excel, the Decrease Decimal button on the Home tab (or Format Cells > Number) changes how many decimals a cell shows. Google Sheets has the same button in the toolbar and under Format > Number. Neither touches the stored value. Suppose A1 and A2 both contain 1.5 and are formatted to show no decimals:

**What formatting hides**
| Cell | Stored value | Displayed as | Used in formulas as |
| --- | --- | --- | --- |
| A1 | 1.5 | 2 | 1.5 |
| A2 | 1.5 | 2 | 1.5 |
| A3: =A1+A2 | 3 | 3 | 3 |

The sheet now appears to say 2 + 2 = 3. Nothing is broken: the formula added the real values. But anyone reading a printed report will assume an error, and the same effect is what makes a column of displayed prices fail to add up to the displayed total.

### Rounding functions change the value

A formula such as =ROUND(A1, 0) returns 2, and 2 is what any later formula referencing that cell will use. If A3 were =ROUND(A1, 0)+ROUND(A2, 0), it would return 4. The rule of thumb: format for presentation, use a function when the rounded number is the number you mean.

### The third option: "Set precision as displayed"

Excel has a workbook-wide setting that makes formatting permanent: File > Options > Advanced, then under "When calculating this workbook", tick "Set precision as displayed". Every constant is then permanently cut to the precision its format shows. It cannot be undone for values already changed, and it affects the whole workbook, so it is a blunt tool. Google Sheets has no equivalent setting. In both programs, explicit ROUND formulas are the safer, more auditable choice.

_[Figure: Three ways to "round" in a spreadsheet — Only one of them is both reversible and visible in the formula bar.]_

## The ROUND Function (Regular Rounding)

ROUND does the rounding most people learned at school: look at the digit after the last one you are keeping; 5 or more rounds up, 4 or less rounds down.

**ROUND syntax**

```
Excel:          =ROUND(number, num_digits)
Google Sheets:  =ROUND(value, [places])
```
- In Excel, num_digits is required. In Google Sheets, places is optional and defaults to 0.
- Positive digits round to the right of the decimal point, 0 rounds to a whole number, negative digits round to the left (tens, hundreds, thousands).
- An exact tie rounds away from zero, so 2.5 becomes 3 and -2.5 becomes -3.

**ROUND examples**
| Formula | Result | Explanation |
| --- | --- | --- |
| =ROUND(123.4567, 2) | 123.46 | Two decimal places; the third decimal (6) rounds up |
| =ROUND(123.4567, 0) | 123 | Nearest whole number; the first decimal (4) rounds down |
| =ROUND(123.4567, -1) | 120 | Nearest ten |
| =ROUND(123.4567, -2) | 100 | Nearest hundred |
| =ROUND(2.5, 0) | 3 | Tie rounds away from zero |
| =ROUND(3.5, 0) | 4 | Tie rounds away from zero |
| =ROUND(-2.5, 0) | -3 | Tie rounds away from zero, which is downward for negatives |

_[Figure: One number, five num_digits values — What =ROUND(123.4567, n) returns as n moves from 2 to -2.]_

> **ROUND is not banker's rounding** — Some accounting and scientific rules round an exact tie to the nearest even digit (2.5 to 2, 3.5 to 4) to avoid a slight upward bias over many values. Neither Excel's nor Google Sheets' ROUND does this. If a policy requires half-to-even, it has to be built with a longer formula — the [banker's rounding vs. normal rounding guide](/blog/math/bankers-rounding-vs-normal-rounding) explains when that matters.

## The MROUND Function (Rounding to Multiples)

MROUND rounds to the nearest multiple of any number you choose, rather than to a number of digits. That makes it the natural function for nickels, quarter-hours, and pack sizes.

**MROUND syntax**

```
Excel:          =MROUND(number, multiple)
Google Sheets:  =MROUND(value, factor)
```
- Both arguments are required, and they must have the same sign. In Excel, =MROUND(5, -2) returns #NUM!, while =MROUND(-10, -3) returns -9.
- A value exactly halfway between two multiples rounds away from zero: =MROUND(7.5, 5) is 10.

**MROUND examples**
| Formula | Result | Explanation |
| --- | --- | --- |
| =MROUND(1.21, 0.05) | 1.2 | 1.21 is closer to 1.20 than to 1.25 (displays as 1.20 with two-decimal formatting) |
| =MROUND(1.23, 0.05) | 1.25 | 1.23 is closer to 1.25 |
| =MROUND(17, 5) | 15 | 17 is 2 from 15 and 3 from 20 |
| =MROUND(18, 5) | 20 | 18 is 3 from 15 and 2 from 20 |
| =MROUND(1234, 10) | 1230 | Nearest ten |
| =MROUND(-10, -3) | -9 | Negative value with a negative multiple |

Typical uses are rounding cash prices to the nearest 0.05, rounding clock times to the nearest 15 minutes, and rounding quantities to a case or pack size. MROUND always goes to the nearest multiple, which can be down; when the answer must never be lower than the input, use CEILING.MATH instead.

> **Decimal multiples and exact midpoints** — Spreadsheets store numbers in binary, and a decimal such as 6.05 cannot be stored exactly. Microsoft's own documentation notes that with a decimal multiple the direction is undefined at midpoints: =MROUND(6.05, 0.1) returns 6.0 while =MROUND(7.05, 0.1) returns 7.1. If exact ties matter, convert to whole units first: =MROUND(ROUND(A2*100, 0), 10)/100 turns 6.05 into the integer 605, rounds it to 610, and returns 6.1.

## Rounding Up: ROUNDUP and CEILING.MATH

### ROUNDUP

ROUNDUP works exactly like ROUND except that it never rounds toward zero. Any discarded digit other than zero pushes the result further from zero, however small it is.

**ROUNDUP syntax**

```
Excel:          =ROUNDUP(number, num_digits)
Google Sheets:  =ROUNDUP(value, [places])
```
- num_digits is required in Excel; places defaults to 0 in Google Sheets.
- "Up" means away from zero. For a negative number the result is more negative.

**ROUNDUP examples**
| Formula | Result | Explanation |
| --- | --- | --- |
| =ROUNDUP(123.4567, 2) | 123.46 | Rounds up at two decimal places |
| =ROUNDUP(123.4567, 0) | 124 | Any fraction pushes to the next whole number |
| =ROUNDUP(2.1, 0) | 3 | Even a small fraction rounds up |
| =ROUNDUP(1234, -2) | 1300 | Up to the next hundred |
| =ROUNDUP(-123.4567, 0) | -124 | Away from zero, so more negative |

Use ROUNDUP whenever falling short is the costly mistake: the number of boxes needed to ship 38 items at 12 per box is =ROUNDUP(38/12, 0), which is 4. Plain =ROUND(38/12, 0) returns 3, because 3.17 is closer to 3, and leaves two items without a box. Material estimates, staffing, and minimum order quantities follow the same logic.

### CEILING.MATH

CEILING.MATH rounds up to a multiple, the way ROUNDUP rounds up to a number of digits. It is available in Excel 2013 and later and in Google Sheets.

**CEILING.MATH syntax**

```
=CEILING.MATH(number, [significance], [mode])
```
- significance is the multiple to round to; it defaults to 1 and its sign is ignored.
- mode only affects negative numbers. By default (omitted or 0) negatives round up toward zero: =CEILING.MATH(-8.1, 2) is -8. Any non-zero mode rounds them away from zero instead: =CEILING.MATH(-5.5, 2, -1) is -6.

**CEILING.MATH examples**
| Formula | Result | Explanation |
| --- | --- | --- |
| =CEILING.MATH(2.3, 1) | 3 | Up to the next whole number |
| =CEILING.MATH(1.21, 0.05) | 1.25 | Up to the next 0.05 |
| =CEILING.MATH(24.3, 5) | 25 | Up to the next multiple of 5 |
| =CEILING.MATH(-2.3) | -2 | Default mode: negatives go toward zero |
| =CEILING.MATH(-2.3, 1, -1) | -3 | Non-zero mode: negatives go away from zero |
| =CEILING.MATH(A2, TIME(0,30,0)) with A2 = 1:20 PM | 1:30 PM | Up to the next half-hour (format the cell as a time) |

Excel also has CEILING.PRECISE (and the equivalent ISO.CEILING), which always rounds up toward positive infinity regardless of the sign of the number or the significance, and has no mode argument. It behaves like CEILING.MATH with the default mode.

## Rounding Down: ROUNDDOWN, INT, FLOOR.MATH, and TRUNC

### ROUNDDOWN

ROUNDDOWN is ROUND that never rounds away from zero. It simply discards digits beyond the requested place.

**ROUNDDOWN syntax**

```
Excel:          =ROUNDDOWN(number, num_digits)
Google Sheets:  =ROUNDDOWN(value, [places])
```
- "Down" means toward zero, so for a negative number the result is less negative — numerically higher.

**ROUNDDOWN examples**
| Formula | Result | Explanation |
| --- | --- | --- |
| =ROUNDDOWN(456.78, 2) | 456.78 | Already has two decimals, so nothing changes |
| =ROUNDDOWN(456.78, 0) | 456 | The fraction is discarded |
| =ROUNDDOWN(2.9, 0) | 2 | Even .9 is discarded |
| =ROUNDDOWN(-123.4567, 0) | -123 | Toward zero, so less negative |

ROUNDDOWN suits any count of completed units: full boxes packed from 47 items at 12 per box (3), whole years of service, or a commission paid only on complete sales bands. It is also how you cap a figure so it never exceeds its true value at the chosen precision.

### INT

INT takes a single argument and returns the largest integer less than or equal to it. For positive numbers that is the same as dropping the decimals. For negative numbers it is not.

**INT examples**
| Formula | Result | Explanation |
| --- | --- | --- |
| =INT(12.345) | 12 | Down to the integer below |
| =INT(2.9) | 2 | Down to the integer below |
| =INT(-12.345) | -13 | Down the number line, which is away from zero |
| =INT(-2.5) | -3 | Down the number line |

### FLOOR.MATH

FLOOR.MATH is the mirror image of CEILING.MATH: it rounds down to a multiple.

**FLOOR.MATH syntax**

```
=FLOOR.MATH(number, [significance], [mode])
```
- significance defaults to 1 and its sign is ignored.
- By default negatives round down, away from zero: =FLOOR.MATH(-8.1, 2) is -10. Any non-zero mode rounds them toward zero instead: =FLOOR.MATH(-5.5, 2, -1) is -4.

**FLOOR.MATH examples**
| Formula | Result | Explanation |
| --- | --- | --- |
| =FLOOR.MATH(2.9, 1) | 2 | Down to the whole number below |
| =FLOOR.MATH(1.23, 0.05) | 1.2 | Down to the 0.05 below |
| =FLOOR.MATH(-2.3) | -3 | Default mode: negatives go away from zero |
| =FLOOR.MATH(-2.3, 1, 1) | -2 | Non-zero mode: negatives go toward zero |

### TRUNC

TRUNC cuts a number off at a given number of digits without rounding anything. Its second argument is optional in both programs and defaults to 0.

**TRUNC examples**
| Formula | Result | Explanation |
| --- | --- | --- |
| =TRUNC(12.345) | 12 | Decimals removed |
| =TRUNC(12.345, 2) | 12.34 | Keeps two decimals; the 5 is dropped, not rounded |
| =TRUNC(-12.345) | -12 | Toward zero |

TRUNC and ROUNDDOWN return identical results for the same arguments; TRUNC just lets you leave the digits out. The difference that matters is TRUNC versus INT, and it only appears on negative numbers: =TRUNC(-12.345) is -12, =INT(-12.345) is -13.

_[Figure: The same input, -12.345, through three "drop the decimals" functions — On positive numbers all three return 12. On negatives, one of them disagrees.]_

## Legacy CEILING and FLOOR, EVEN, and ODD

Older workbooks often use plain CEILING and FLOOR, which predate the .MATH versions. In Excel both arguments are required and the sign of the significance matters: =CEILING(-2.5, 2) is -2, =CEILING(-2.5, -2) is -4, and a positive number with a negative significance, such as =CEILING(2.5, -2), returns #NUM!. FLOOR follows the same pattern: =FLOOR(-2.5, 2) is -4 and =FLOOR(2.5, -2) is #NUM!. In Google Sheets the factor is optional (default 1), a positive value must also have a positive factor, and a negative value can take either sign. For new work, CEILING.MATH and FLOOR.MATH are easier to reason about in both programs.

EVEN and ODD round to the next even or odd integer, always away from zero: =EVEN(1.5) is 2, =EVEN(3) is 4, =EVEN(-1) is -2, =ODD(1.5) is 3, =ODD(2) is 3, and =ODD(0) is 1. They are occasionally useful for pairing items or laying out grids, and they are not banker's rounding.

## Comparison Table: All Rounding Functions

**Direction and negative-number behaviour**
| Function | Direction | Rounds to | Negative numbers | -2.5 becomes |
| --- | --- | --- | --- | --- |
| ROUND | Nearest; ties away from zero | Digits | Mirror of positives | -3 (digits 0) |
| MROUND | Nearest; ties away from zero | Multiple | Multiple must also be negative | -3 (multiple -1) |
| ROUNDUP | Away from zero | Digits | More negative | -3 (digits 0) |
| ROUNDDOWN | Toward zero | Digits | Less negative | -2 (digits 0) |
| CEILING.MATH | Up (toward +∞) by default | Multiple | Mode switches to away from zero | -2 |
| FLOOR.MATH | Down (toward -∞) by default | Multiple | Mode switches to toward zero | -3 |
| INT | Down (toward -∞) | Integer | Away from zero | -3 |
| TRUNC | Toward zero | Digits (default 0) | Less negative | -2 |

## When to Use Each Function

_[Figure: Picking a rounding function — Answer three questions in order.]_

**Common situations**
| Situation | Function | Example formula |
| --- | --- | --- |
| Money to the cent | ROUND | =ROUND(B2*C2, 2) |
| Cash price to the nearest 5 cents | MROUND | =MROUND(B2, 0.05) |
| Report in thousands | ROUND with negative digits | =ROUND(B2, -3) |
| Boxes or packs needed | ROUNDUP | =ROUNDUP(B2/12, 0) |
| Full boxes filled | ROUNDDOWN or INT | =ROUNDDOWN(B2/12, 0) |
| Clock time to the nearest 15 minutes | MROUND | =MROUND(B2, TIME(0,15,0)) |
| Billable time up to the next half-hour | CEILING.MATH | =CEILING.MATH(B2, TIME(0,30,0)) |
| Quantity down to full 6-packs | FLOOR.MATH | =FLOOR.MATH(B2, 6) |
| Whole part of a number, keeping the sign | TRUNC | =TRUNC(B2) |

Time works with MROUND, CEILING.MATH, and FLOOR.MATH because both programs store a time as a fraction of a day, and TIME(0,15,0) is exactly a quarter-hour of that day. With A2 holding 1:44 PM, =MROUND(A2, TIME(0,15,0)) returns 1:45 PM; 8:07 AM returns 8:00 AM. Format the result cell as a time or you will see a decimal. The [Excel timesheet guide](/blog/templates/excel-timesheet-with-overtime) uses this for clock-in rounding and explains why FLOOR is the wrong choice there.

## Excel vs. Google Sheets: What Differs

All eight core functions exist in both programs with the same names and the same results for ordinary inputs, so a formula written in one will almost always work in the other. The differences are at the edges:

**Behaviour differences worth knowing**
| Point | Excel | Google Sheets |
| --- | --- | --- |
| Digits argument in ROUND, ROUNDUP, ROUNDDOWN | Required | Optional, defaults to 0 |
| Factor in CEILING and FLOOR | Required | Optional, defaults to 1 |
| CEILING.MATH and FLOOR.MATH | Excel 2013 and later | Available |
| Set precision as displayed | Available (File > Options > Advanced) | No equivalent |
| Tie-breaking in ROUND and MROUND | Away from zero | Away from zero |
| MROUND with mixed signs | #NUM! | Value and factor must share a sign |

The practical consequence of the first row: =ROUND(A2) works in Google Sheets but is an error in Excel. If a sheet may be opened in both, always write the digits argument explicitly.

## Practice Problems (With Answers)

Work each one out before reading the answer. Every answer below was checked against the documented function behaviour.

### Problem 1: =ROUND(456.789, 2)

Answer: 456.79. The third decimal is 9, so the second decimal (8) rounds up to 9.

### Problem 2: =MROUND(2.33, 0.1)

Answer: 2.3. 2.33 is 0.03 from 2.3 and 0.07 from 2.4. (It displays as 2.30 only if the cell is formatted to two decimals.)

### Problem 3: =ROUNDUP(123.4567, 1)

Answer: 123.5. Any digits after the first decimal push it up.

### Problem 4: =ROUNDDOWN(-123.4567, 0)

Answer: -123. ROUNDDOWN goes toward zero.

### Problem 5: =CEILING.MATH(2.1, 0.5)

Answer: 2.5. The multiples of 0.5 around 2.1 are 2.0 and 2.5; CEILING.MATH takes the higher one.

### Problem 6: =FLOOR.MATH(2.9, 1)

Answer: 2.

### Problem 7: =INT(-12.345)

Answer: -13. INT moves down the number line.

### Problem 8: =ROUNDUP(-2.1, 0)

Answer: -3. ROUNDUP moves away from zero, which for a negative number means more negative.

**Answer key**
| Problem | Formula | Answer |
| --- | --- | --- |
| 1 | =ROUND(456.789, 2) | 456.79 |
| 2 | =MROUND(2.33, 0.1) | 2.3 |
| 3 | =ROUNDUP(123.4567, 1) | 123.5 |
| 4 | =ROUNDDOWN(-123.4567, 0) | -123 |
| 5 | =CEILING.MATH(2.1, 0.5) | 2.5 |
| 6 | =FLOOR.MATH(2.9, 1) | 2 |
| 7 | =INT(-12.345) | -13 |
| 8 | =ROUNDUP(-2.1, 0) | -3 |

## Common Mistakes (And How to Avoid Them)

### Mistake 1: Treating formatting as rounding

Hiding decimals with the format leaves full-precision values in play, so displayed rows do not add up to the displayed total. Wrap the calculation in ROUND wherever the rounded figure is the one that should flow downstream.

### Mistake 2: Assuming "up" and "down" mean the number line

ROUNDUP and ROUNDDOWN are defined relative to zero, CEILING.MATH and INT relative to the number line. On a column of refunds or losses, =ROUNDUP(-2.1, 0) gives -3 and =CEILING.MATH(-2.1) gives -2. Test at least one negative value before copying a formula down.

### Mistake 3: Using INT to drop decimals from negative numbers

INT(-12.345) is -13, not -12. If you want the whole-number part with the sign kept, use TRUNC.

### Mistake 4: Forgetting negative digits

There is no need to divide by 1,000, round, and multiply back. =ROUND(1234, -2) returns 1200 directly, and =ROUNDUP(1234, -2) returns 1300.

### Mistake 5: Using MROUND when the result must never be lower

MROUND goes to the nearest multiple, so =MROUND(17, 5) is 15. For minimum quantities, billable increments, or anything that must cover the full amount, use =CEILING.MATH(17, 5), which is 20.

### Mistake 6: Rounding too early

Rounding intermediate results compounds small errors. Three invoice lines of 1.126 each, rounded to 1.13 and then summed, total 3.39; summing first and rounding once gives 3.38. Neither is wrong in itself — tax rules sometimes require line-by-line rounding — but the choice should be deliberate. The [sales tax and VAT in Excel guide](/blog/templates/excel-sales-tax-and-vat) shows where ROUND belongs in a tax calculation.

**Round each line, or round the total?**
| Method | Formula | Result |
| --- | --- | --- |
| Round each line, then sum | =ROUND(1.126,2)*3 | 3.39 |
| Sum, then round once | =ROUND(1.126*3, 2) | 3.38 |

### Mistake 7: Ignoring version and program differences

A formula like =ROUND(A2) that works in Google Sheets fails in Excel, and CEILING.MATH does not exist in Excel 2010 or earlier. Write every argument explicitly and prefer the .MATH functions over legacy CEILING and FLOOR unless the file must open in very old versions.

### Mistake 8: Confusing significance with decimal places

The second argument of MROUND, CEILING.MATH, and FLOOR.MATH is the multiple itself, not a count of digits. =CEILING.MATH(1.21, 2) returns 2, not 1.21. For nickels use 0.05, for whole numbers 1, for dozens 12. If you need significant figures rather than a fixed multiple, the [significant figures guide](/blog/math/significant-figures-guide) covers the rules.

## Rounding Cheat Sheet

**Every function with a positive and a negative example**
| Function | Positive example | Negative example |
| --- | --- | --- |
| ROUND | =ROUND(2.5, 0) → 3 | =ROUND(-2.5, 0) → -3 |
| MROUND | =MROUND(17, 5) → 15 | =MROUND(-10, -3) → -9 |
| ROUNDUP | =ROUNDUP(2.1, 0) → 3 | =ROUNDUP(-2.1, 0) → -3 |
| ROUNDDOWN | =ROUNDDOWN(2.9, 0) → 2 | =ROUNDDOWN(-2.9, 0) → -2 |
| CEILING.MATH | =CEILING.MATH(2.3, 1) → 3 | =CEILING.MATH(-2.3) → -2 |
| FLOOR.MATH | =FLOOR.MATH(2.9, 1) → 2 | =FLOOR.MATH(-2.3) → -3 |
| INT | =INT(2.9) → 2 | =INT(-12.345) → -13 |
| TRUNC | =TRUNC(12.345, 2) → 12.34 | =TRUNC(-12.345) → -12 |
| EVEN / ODD | =EVEN(1.5) → 2, =ODD(1.5) → 3 | =EVEN(-1) → -2, =ODD(-1) → -1 |

MROUND is also handy for measurements that need to be usable: the [recipe scaling in Excel guide](/blog/templates/excel-recipe-scaling-and-unit-conversion) rounds scaled quantities to the nearest eighth with =MROUND(value, 0.125).

## Sources to Verify or Cite

- Microsoft Support, ROUND function: https://support.microsoft.com/en-us/office/round-function-c018c5d8-40fb-4053-90b1-b3e7f61a213c
- Microsoft Support, MROUND function (same-sign requirement and the 6.05 / 7.05 midpoint example): https://support.microsoft.com/en-us/office/mround-function-c299c3b0-15a5-426d-aa4b-d2d5b3baf427
- Microsoft Support, ROUNDUP function: https://support.microsoft.com/en-us/office/roundup-function-f8bc9b23-e795-47db-8703-db171d0c42a7
- Microsoft Support, CEILING.MATH function (mode argument): https://support.microsoft.com/en-us/office/ceiling-math-function-80f95d2f-b499-4eee-9f16-f795a8e306c8
- Microsoft Support, Set rounding precision ("Set precision as displayed"): https://support.microsoft.com/en-us/office/set-rounding-precision-e5d707e3-07a8-4df2-810c-218c531eb06a
- Google Docs Editors Help, ROUND: https://support.google.com/docs/answer/3093440
- Google Docs Editors Help, MROUND: https://support.google.com/docs/answer/3093426
- Google Docs Editors Help, ROUNDUP: https://support.google.com/docs/answer/3093443
- Google Docs Editors Help, CEILING: https://support.google.com/docs/answer/3093471
- Google Docs Editors Help, CEILING.MATH: https://support.google.com/docs/answer/9061515
- Google Docs Editors Help, FLOOR.MATH: https://support.google.com/docs/answer/9061444
- Google Docs Editors Help, INT: https://support.google.com/docs/answer/3093490

## Frequently Asked Questions

**How do I round numbers in Excel?**

Use =ROUND(number, num_digits). =ROUND(A2, 2) rounds to two decimal places, =ROUND(A2, 0) to a whole number, and =ROUND(A2, -2) to the nearest hundred. The digits argument is required in Excel.

**How do I round numbers in Google Sheets?**

The same functions work with the same results. =ROUND(A2, 2) rounds to two decimals. Google Sheets lets you omit the second argument (=ROUND(A2) rounds to a whole number), but Excel does not, so include it if the file will be shared.

**What is the difference between ROUND and MROUND?**

ROUND rounds to a number of digits (two decimals, nearest ten). MROUND rounds to the nearest multiple of any value, such as 0.05, 5, or 15 minutes. =ROUND(17, -1) is 20, while =MROUND(17, 5) is 15.

**What is the difference between ROUNDUP and CEILING.MATH?**

ROUNDUP rounds away from zero at a number of digits; CEILING.MATH rounds up to a multiple. They also treat negatives differently: =ROUNDUP(-2.3, 0) is -3, while =CEILING.MATH(-2.3) is -2 unless you set a non-zero mode.

**What is the difference between ROUNDDOWN and FLOOR.MATH?**

ROUNDDOWN rounds toward zero at a number of digits; FLOOR.MATH rounds down to a multiple. On negatives, =ROUNDDOWN(-2.3, 0) is -2 while =FLOOR.MATH(-2.3) is -3.

**What is the difference between INT and TRUNC?**

INT rounds down toward negative infinity; TRUNC removes digits toward zero. They agree on positive numbers and differ on negatives: =INT(-4.3) is -5 and =TRUNC(-4.3) is -4. TRUNC also accepts a digits argument.

**Does Excel's ROUND use banker's rounding?**

No. Excel and Google Sheets ROUND both round an exact half away from zero, so 0.5 becomes 1, 2.5 becomes 3, and -2.5 becomes -3. Half-to-even rounding needs a custom formula.

**How do I round to the nearest 5 or the nearest 0.05?**

Use MROUND: =MROUND(A2, 5) or =MROUND(A2, 0.05). To always round up to the next 5 use =CEILING.MATH(A2, 5), and to always round down use =FLOOR.MATH(A2, 5).

**How do I round a time to the nearest 15 minutes or half-hour?**

For the nearest quarter-hour use =MROUND(A2, TIME(0,15,0)); for the nearest half-hour use TIME(0,30,0). To always round up, as with billable time, use =CEILING.MATH(A2, TIME(0,30,0)). Format the result as a time.

**Why does MROUND sometimes round a midpoint the wrong way?**

Decimal values such as 6.05 cannot be stored exactly in binary, so a value that looks like an exact midpoint may be fractionally below it. Microsoft documents that =MROUND(6.05, 0.1) returns 6.0 while =MROUND(7.05, 0.1) returns 7.1. Working in whole units, such as cents, avoids the problem.

**Does changing the number format change the value?**

No. Formatting changes only what is displayed; formulas still use the full stored value. The exception is Excel's "Set precision as displayed" option, which permanently cuts values to their displayed precision across the whole workbook.

**Why does my SUM not match the rounded numbers I can see?**

The cells are formatted, not rounded, so SUM adds the hidden decimals. Either round each value with ROUND so the column adds up exactly, or round the total and accept that the visible lines may differ from it by a cent or two.

## Final Summary

Spreadsheet rounding comes down to two decisions and one check. First, decide whether you need a rounded value or just a rounded display. Second, decide whether you are rounding to digits or to a multiple, and in which direction. Then test a negative number, because that is where ROUNDUP, CEILING.MATH, INT, and TRUNC quietly part ways.

- Formatting changes the display; ROUND and its relatives change the value.
- ROUND rounds ties away from zero: 2.5 → 3, -2.5 → -3.
- Negative digits round to tens, hundreds, and thousands: =ROUND(1234, -2) → 1200.
- MROUND for the nearest multiple; CEILING.MATH for always up; FLOOR.MATH for always down.
- ROUNDUP and ROUNDDOWN are relative to zero: =ROUNDUP(-2.1, 0) → -3.
- INT goes down the number line (-12.345 → -13); TRUNC goes toward zero (-12.345 → -12).
- Write the digits argument explicitly so formulas work in both Excel and Google Sheets.

---

_Source: [Do The Calculation](https://dothecalculation.com/blog/math/how-to-round-numbers-excel-google-sheets). Quote freely with attribution and a link to this page._