Excel can cover multiple data types such as numbers, text, decimals, negative or positive numbers, etc. Decimals are not useful enough in most cases that’s why users tend to make them round to the whole number. It makes the data easier to understand.
Today, you will get to know how to round to the nearest whole number in Excel. Let’s see how it happens:
Excel Functions Used for Round Numbers:
With Excel round functions, you can simply change the real value in a cell. Below is a list of functions used for different kinds of rounding Excel:
ROUND: It helps in rounding the number to the specified number of digits.
ROUNDUP: It helps in rounding the number upward to the specified number of digits.
ROUNDDOWN: It uses to round the downward number to the specified number of digits.
MROUND: It helps in rounding the number upward or downward to the specific digits.
FLOOR: It helps in rounding the number down to the specific multiple.
CEILING: It uses to round the number up to the specific multiple.
TRUNC: It uses to truncate the number to a specific number of decimal places.
INT: It helps in rounding the number down to the nearest digit.
EVEN: It uses to round the number up to the nearest even digit.
ODD: It uses to round the number up to the nearest odd digit.
How to Round to the Nearest Whole Number in Excel with Formulas?
Let’s see how the ROUND function can help you in rounding numbers down to the nearest digit.
ROUND Function
As the name shows, the ROUND function is used to round numbers down to the nearest integer. For this, you just have to add the number of digits. The number will round to the nearest digit only if the value is 0.
- Select cell D5 and put in the following code:
=ROUND(B5, 0)
- You can see the D5 cell has the rounded number in the B5 cell after adding the code.
- The number rounds up from 973.5 to 974.
- Use the Fill Handle option in cell D5 to cell D12.
- You will instantly notice the cell range D5 to D10 has now been filled with the rounded-up numbers from the range of cells B5:B11.
EVEN Formula
For this function, let’s follow the steps:
- Select the cell in which you need the output.
- Open the Formula tab and enter the formula:
=EVEN(A1)
- A1 is the number that you need to round.
- Press ENTER key from the keyboard.
Isn’t it simple?
INT Function
In this section, you will learn how the INT function can help you to convert fractional values to integer values. With the help of the INT function, you can strip away the numbers from the decimal fractional and rounds them down.
- Select the D5 cell and add the following code:
=INT(B5)
- Cell D5 has now the rounded number in cell B5 after entering the above code. The number is now rounded from 973.5 to 973.
- As you see the number is rounded down and the INT function can now round down the number to its nearest integer.
- Use the Fill Handle option from cell D5 to cell D12.
- The cell range D5 to D10 is now filled with the rounded-down numbers from the cell range B5:B11.
TRUNC Function
With the help of the TRUNC function, you will understand how to round the nearest whole number in Excel. You will see the TRUNC function as limiting the decimal fraction and rounding the number.
[num_digits]=0
The above formula is set in the argument value to convert the number to an integer. You may find it similar to the INT function, however, the TRUNC function eliminates the fractional part only by rounding up or down the number.
Below you can see simple steps:
- Select cell D5 and add the following code:
=TRUNC(B5, 0)
- Cell D5 now has the rounded number in cell B5 after you enter the above code. You will notice the number is truncated from 973.5 to 973.
- Use the Fill Handle from cell D5 to cell D12.
- Now, the cell range D5 to D10 has been filled with rounded numbers from the range of cells B5:B11.
Summary:
To sum it up, the query “How to round to the nearest whole number in Excel is answered thoroughly by using multiple functions used in Excel. Each function is useful to perform this query that’s why you can try all of them.
