How to Round a Number to Nearest 1000, 100, and 10 in Excel (Formula)

- Written by Puneet

excelchamps-free-courses-puneet-gogia

If you are one of those persons who work with statistical data or analyze data a lot, then this formula might be useful for you in so many ways. Here we will learn to round a number to the nearest 1000, 100, and 10.

Round a Number to the Nearest 1000

In Excel, there’s a function called ROUND that you can use to round a number to the nearest 1000. Here I have the number “123456789”, and I want to round this number.

cell-with-numeric-value

You can use the following steps.

  1. First, edit cell B1, and start entering the ROUND function “=ROUND(“.
  2. Now, refer to cell A1 where we have the number that you want to round.
  3. Next, in the second argument, enter -3.
  4. In the end, enter the closing parenthesis and hit enter to get the result.
round-function-to-nearest-1000
=ROUND(A1,-3)

The moment you hit enter it returns 123457000 which is then rounded to the 1000 version of 123456789. As you have the last four numbers 6789, it has rounded to 7000.

round-function-result

But if you change that number to 123456489 where you have 6489 in the end, the same function rounds it downwards to 123456000.

round-function-result-types

The ROUND function is quite smart to identify on which side the number needs to be rounded. But there might be a situation where you need to ROUND down or up a number to the nearest 1000.

=ROUNDDOWN(123456789,-3) = 123456000
=ROUNDUP(123456489,-3) = 123457000

ROUND a Number to the Nearest 100

In the same way, you can use the ROUND function to round a number to the nearest 100. For this, you just need to use the -2 in the num_digit argument.

=ROUND(A1,-2)
round-number-to-nearest-100

ROUND a Number to the Nearest 10

And if you want to round a number to the nearest 10, then you use the ROUND function in the following way.

round-a-number-to-nearest-10
=ROUND(A1,-1)

Round a Number to Nearest Nth Value

Let me ask if you want to write a formula that allows you to round to the nearest Nth value. Isn’t this great?
Here’s a formula that rounds a number to the nearest specified multiple using Excel’s MROUND function.

For example, if you want to round 123456789 to the nearest 100, you would enter the following formula in a cell:

=MROUND(123456789, 100)

This returns 23456800. And to round to the nearest 1000, you would use:

=MROUND(123456789, 1000)

Different Approach with ROUND and Basic Formula

You can also use basic arithmetic with the MROUND to write a formula that allows you to use any multiple to get the number.

=ROUND(number / multiple, 0) * multiple
=ROUND(123456789 / 100, 0) * 100

This formula is perfect if MROUND isn’t suitable (e.g., it’s not available in your version of Excel).

Other Methods to Round a Number to the Nearest 1000

There are a few more ways that you can use it:

  • FLOOR Function: FLOOR(123456789,1000) = 123457000
    • In this function, you need to use a positive 1000 to round a positive number and a negative -1000 to round a negative number.
  • CEILING Function: CEILING(C1,1000) = 123457000
    • In this function, you need to use a positive 1000 to round a positive number and a negative -1000 to round a negative number.

Get the Excel File

Last Updated: May 01, 2024