How to easily round to tens, hundreds, thousands, ... in Excel ?

ROUND function to decimal places

In its standard form, the ROUND function rounds a value to the nearest decimal place with a precision of 1, 2, or 3 decimal places.

Note precision has no impact on the display of numbers. In D6, the expected precision is 2 decimal places, but since the source value has only 1, only XNUMX decimal place is displayed.

ROUND function to tens, hundreds, ...

The ROUND function also allows you to round to tens, hundreds, thousands.

To achieve this, just put a negative value, as precision in the function.

• -1 to round to tens.
• -2 to round to hundreds.
• -3 to round to thousands.

For example, to round 15358,91 to the nearest hundred, you write the following formula:

=ROUNDED(15358,91;-2) => 15400

And to round the same number to the tenth

=ROUNDED(15358,91;-1) => 15360

The following table traces these different results.

Round down to the nearest ten or hundred

The ROUND function, rounded to the nearest ten or hundred, such as 778,6 was rounded to 800.

Now if you want to round down, you just need to use the function ROUND.INF with the same parameters.

And of course, to force rounding to the next digit, this is the function ROUNDUP which should be used.

Exercise: Ranking by bracket

There are several techniques for making classifications by bracket. You will find a another example here using Power Query.

In the following workbook you have the number of entries counted for all days in July and August.

To make analysis easier, we will add a column that will round the number of entries to the nearest ten.

We then use the UNIQUE function to only keep the values ​​once.

Then, using the COUNTIF function, it is easy to obtain attendance statistics by bracket.

It should be noted that the same work is obtained in a single step with the FREQUENCY function

You will find additional information on the ROUND function on the Microsoft site.