When working in Excel, you may want to round numbers to the nearest tenth.
Sometimes, this is done to make your data cleaner and more consistent, and sometimes it has a more practical purpose (Such as working in a time slot of 10 minutes or finding out how many containers you need when the capacity is 10 items per container)
Using Excel, you can easily round numbers to the nearest tenth (or the nearest tenth that is more or less than the given value.
In this article, I have covered multiple ways to round to the nearest tenth in Excel.
Round to the Nearest Tenth in Excel
Sometimes we want to round to the nearest tenth digit based on the value in the one’s place.
This is the rounding rule.
- The number will round up to the nearest tenth if the one-place number is 5 or above.
- The number will round down to the nearest tenth if the one’s place value is 4 or less.
For this, we can use both the ROUND and MROUND functions.
Method 1 – Using the ROUND Function
The ROUND function in Excel is the most commonly used function for rounding numbers and can also be used to round numbers to the nearest tenth.
The table below shows how long it took five applicants to finish a task. We want to round the time to the nearest 10-minute interval.
We can use the below function to round numbers to the nearest tenth.
=ROUND(B2,-1)
The syntax of the ROUND function is ROUND(number, num_digits).
For the first argument, we have to select which number to round. In this case, we wish to round the number in cell B2. So we choose cell B2 as our first argument.
Then, enter the digit to which we want to round the selected value. In this Excel function, the digit in one’s place is treated as the zero digits. We have to move a digit to the left of zero, or the negative side, to round to the tenth place. So, for the second argument, we enter -1.
Also read: Round UP or DOWN to Nearest 5 in Excel
Method 2 – Using the MROUND Function
Even though the ROUND function is more popular among Excel users than the MROUND function, in this scenario, I think the MROUND function is more user-friendly than the ROUND function.
The table below shows the time it took five applicants to finish a task. To the nearest 10-minute interval, we want to round the time.
We can use the below function to round numbers to the nearest tenth.
=MROUND(B2,10)
The syntax of the MROUND function is MROUND(number, multiple). First, we have to select the number that we want to round.
We want to round the number in cell B2. So, we select that cell for the first argument of the MROUND function.
Then, we have to enter the multiple to which we want to round the selected number. The tenth means a multiple of 10. So, we enter 10 for the last argument.
Also read: How to Round to Nearest 100 in Excel?
Round Up to the Nearest Tenth in Excel
When we apply the above two functions to round to the nearest tenth, the number could be either rounded up or rounded down.
However, there are times when we always have to round up to the nearest tenth. When that happens, we need to use some other Excel functions.
If we wish to round numbers in Excel to the nearest tenth, we can do it by using the ROUNDUP function and the CEILING.MATH function.
Method 1 – Using the ROUNDUP Function
We can use the Excel ROUNDUP function to round numbers to the nearest tenth.
The function arguments are the same as those for the ROUND function. However, when we use the ROUNDUP function, the result is always a round-up.
The table below displays how long it took each of the five applicants to do a task. We need to round the time to the nearest 10-minute interval.
We can use the below function to round up numbers to the nearest tenth.
=ROUNDUP(B2,-1)
The syntax of the ROUNDUP function is ROUNDUP(number, num_digits). First, we have to select the number to round up.
In this case, we’d like to round up the number in cell B2. Therefore, we choose cell B2 to be the first argument for this function.
Then, enter the digit to which we want to round up the selected number. The digit in one’s position is treated as a zero digit in this Excel function.
To round up to the tenth place, we need to move one digit to the left or on the negative side of zero. So we enter -1 for the second argument.
Method 2 – Using the CEILING.MATH Function
We can use the Excel CEILING.MATH function also to round up numbers to the nearest tenth.
The below table shows the number of minutes spent by 5 candidates to complete a task. We want to round up the time to the nearest 10 minutes interval.
We can use the below function to round up numbers to the nearest tenth.
=CEILING.MATH(B2,10)
The syntax of the CEILING.MATH function is CEILING.MATH(number, [significance], [mode]).
We have to first select the number that we want to round up. So, we can select cell B2 for the first argument.
Then, the multiple to which the number is to be rounded. As we want to round to the nearest tenth, we have to enter 10 for the second argument. In this case, we can skip the last argument, mode.
Also read: Round Up to the Nearest Whole Number in Excel
Round Down to the Nearest Tenth in Excel
Sometimes we like to always round down our numbers. In such a case, we cannot use any of the functions discussed above.
To round down, we have to use a different set of Excel functions. To round down to the nearest tenth in Excel, we can use the ROUNDDOWN, FLOOR.MATH, and TRIM functions.
Method 1 – Using the ROUNDDOWN function
The use of the Excel ROUNDDOWN function is obvious from its name. We can use this function to round numbers to the nearest tenth.
The table below shows how long it took five candidates to complete a task. We’d like to round the time down to the nearest 10-minute interval.
We can use the below function to round up numbers to the nearest tenth.
=ROUNDDOWN(B2,-1)
The syntax of the ROUNDDOWN function is ROUNDDOWN(number, num_digits).
We have to first select the number that we want to round down. In this example, we want to round down the number in cell B2. So, we select cell B2 as the first argument of this function.
Then, we need to enter to which digit we want to round down the selected number. In this Excel function, the digit in one’s place is considered a zero digit.
As we want to round down to the tenth place, we have to go 1 digit to the left or negative side from zero. So, we enter -1 for the second argument.
Also read: How to Round Numbers in Excel Without Formula?
Method 2 – Using the FLOOR.MATH function
FLOOR.MATH is another basic function in Excel that we can use to round to the nearest tenth.
This function has the same syntax as the CEILING.MATH function. However, Excel always rounds down the number in the FLOOR.MATH function.
The table below displays the time it took five students to finish a task. We’d like to round the time down to the nearest 10-minute interval.
We can use the below function to round up numbers to the nearest tenth.
=FLOOR.MATH(B2,10)
The syntax of the FLOOR.MATH function is FLOOR.MATH(number, [significance], [mode]).
First, we have to choose which number we wish to round down. It is cell B2 in this case.
The significance is the second argument. We need to enter 10 since we want to round down to the tenth. We can skip the function’s final argument.
Method 3 – Use the TRUNC function
The TRUNC function in Excel helps you to remove a given number of digits from a number.
In Excel, we can use this function to round down numbers to the nearest tenth.
The table below shows how long it took five applicants to finish a task. We want to round the time down to the nearest 10-minute period.
We can use the below function to round up numbers to the nearest tenth.
=TRUNC(B2,-1)
The syntax of the TRUNC function is TRUNC(number, [num_digits]). As the first argument, we have to enter the number that we wish to round down.
So we have to select cell B1. Like the ROUND, ROUNDUP, and ROUNDDOWN functions, this function treats the digit in one’s place as a zero digit.
We need to move one digit to the left or the negative side of zero to round down to the tenth place. So, for the second argument, we enter -1.
Our selected number is then rounded down to the nearest tenth. We can use the same formula in the cells below.
This article covered seven functions for rounding to the nearest tenth. Before choosing the function, consider how you want to round the numbers. Then, select the simplest function for you.
Other Excel articles you may also like: