This tutorial will demonstrate how to use all of Excel and Google Sheets’ rounding functions to round numbers.

Round Formula main

Rounding Functions

Excel’s round functions allow you to round numbers to specified number of digits.

ROUND Function

The ROUND Function rounds numbers to a specified number of digits. When rounding to 0, a number rounds to the nearest whole number. Anything below .5 rounds down and anything equal to or above .5 rounds up.

=ROUND(B3,C3)

ROUND

As you can see, the ROUND Function takes two inputs:

  1. The number to round
  2. How many spots to round to the left (negative numbers) or right (positive numbers) of the decimal

ROUNDUP Function

The ROUNDUP Function works the same as the ROUND Function except it always rounds up to the specified number of digits:

=ROUNDUP(B3,C3)

ROUNDUP

ROUNDDOWN Function

The ROUNDDOWN Function works the same as the ROUND and ROUNDUP Functions except it always rounds down to the specified number of digits:

=ROUNDDOWN(B3,C3)

ROUNDDOWN

TRUNC Function

The TRUNC Function “truncates” a number after a certain number of digits.

=TRUNC(B3,C3)

TRUNC

INT Function

The INT Function returns the integer value of a number. It works exactly the same as TRUNC for positive numbers. But will give a different result for negative numbers.

=INT(B3)

TRUNC VS INT

Rounding Functions – Round to Multiples

The above functions will round to a specified number of digits. To round to a specified multiple use the MROUND, FLOOR, and CEILING Functions.

MROUND Function

The MROUND Function works similarly to the ROUND Function. However, instead of defining the number of digits to round to, you define the multiple to which to round. Here are some examples:

=MROUND(B3,C3)

MROUND

FLOOR Function

The FLOOR Function works just like the MROUND Function, except the FLOOR Function always rounds down.

=FLOOR(B3,C3)

FLOOR

CEILING Function

The CEILING Function works just like the MROUND and FLOOR Functions, except it will always round up.

=CEILING(B3,C3)

CEILING

Round Without Formulas

In Excel, you can adjust how numbers are displayed with Number Formatting.  Using Number Formatting you can round how the number is displayed.

ROUND Without Formula

excel round number formatting

Rounding Examples

Round Time

With MROUND, you can round time like this:

=MROUND(B3,"0:15")

Round Time

Round Percentages

You can also round percentages with the ROUND Function, just keep in mind that percentages are stored as decimal values, so you must set num_digits accordingly:

=ROUND(B3,2)

Round Percentages

Round to Nearest Dollar

By setting num_digits to 0, you can round prices to the nearest dollar:

=ROUND(B3,0)

Round Nearest Dollar

Round to Nearest Cent

Or to the nearest cent with num_digits = 2.

=ROUND(B3,2)

Round Nearest Cent

Round Number to n Significant Figures

With some additional calculations, you can use the ROUND Function to round a number to n significant digits:

=ROUND(A2,B2-(1+INT(LOG10(ABS(A2)))))

Round Significant

Round a Sum – Round off Formula

You can place other calculations or functions within the ROUND Function. For example, you can round off a decmal:

=ROUND(SUM(B3:B7),0)

Round sum

Round Two Decimal Places

This example will round to two decimal places:

=ROUND(B3,2)

Round Two Decimal

Round to Thousands

This example will round to the thousands place with a negative num_digits:

=ROUND(B3,-3)

Round Thousands

Round Up to Nearest 100

This example will round up to the nearest 100.

=ROUNDUP(B3,-2)

Round Nearest 100

Round to Nearest 5

The MROUND, FLOOR, and CEILING Functions can round to the nearest 5:

=MROUND(B3,5)

Round Nearest Five

Calculate Decimal Part of Number

The TRUNC Function can be used to calculate the decimal part of a number.

=B3-TRUNC(B3,0)

Calculate Decimal

This works by first using TRUNC to calculate the integer portion of the number:

=TRUNC(B3,0)

Trunc

Round Formulas in Google Sheets

All of the above examples work exactly the same in Google Sheets as in Excel.

Round Formula Google