We use numbers in Excel in different cases. In those cases, we may get numbers in long decimals. But in most real cases, those long decimals are not that significant. We often curtail those numbers to make those numbers round to the whole number. To become more accessible and easier to understand. In this article, we will discuss how you can round decimals to the nearest whole number in Excel.
We are going to use the below dataset for the demonstration reason. On the left side is the numbers that are not rounded, and on the right column, the Rounded number will have the number rounded as shown. How we manage to do this will discuss here with adequate examples.
1. Using ROUND Function to Round to Nearest Whole Number in Excel
The ROUND function is an effective function to round numbers down to the nearest integer.
In this function, we need to enter the number of digits to which our number argument will round. The number will round to the nearest integer if the value is 0.
Steps
- At first, select cell D5 and enter the following code.
=ROUND(B5,0)- After entering the code, you will notice that cell D5 now contains the rounded number in cell B5. The number now rounds up from 973.5 to 974.
- Now, drag the Fill Handle button in the corner of cell D5 to cell D12.
- After that, you will notice that the range of cells D5 to D10 is now filled with the rounded-up numbers from the range of cells B5:B11.
2. Applying EVEN and ODD Functions for Rounding to Nearest Whole Number
Here the ODD and EVEN functions will round the number to the closest even or odd integer number depending on the value of the original number.
Steps
- At first, select cell D5 and enter the following code.
=EVEN(B5)- After entering the code, you will notice that cell D5 now contains the rounded number in cell B5. The number is rounded up from 973.5 to 974.
- Now select cell D6 and enter the following formula.
=ODD(B6)We enter the odd function instead of the EVEN Function because of rounding the number to its nearest whole number. The number in cell D5 is close to an even number in the upward direction, more than an odd number. This is why we choose the Even function to make it 974.
- Instead, the ODD function will round the number to 975, which is not the nearest integer of 973.5 in the upward direction.
- On the other hand, function ODD used to make 102.5 rounded to 103 as 102.5 is closer to the odd number 103 in the upward direction.
- Now enter the appropriate function in the range of cells D5:D12. If the number is closer to the odd number in the upward direction, use the ODD function. Otherwise, use the EVEN function.
Read More: How to Round Down to Nearest Whole Number in Excel
3. Inserting TRUNC Function to Round in Excel
In this method, the TRUNC function will remove the decimal part of the fraction and round that number. In the argument value, We set [num_digits]=0 to convert the number to an integer. Stripping only the fractional part. There will be no rounding up or down in this case.it works similarly to the INT function. But the TRUNC function just removes the fractional part; it won’t round up or down any number.
Steps
- At first, select cell D5 and enter the following code.
=TRUNC(B5,0)- After entering the code, you will notice that cell D5 now contains the rounded number in cell B5. The number is now truncated from 973.5 to 973.
- Now, drag the Fill Handle button in the corner of cell D5 to cell D12.
- After that, you will notice that the range of cells D5 to D10 is now filled with the rounded numbers from the range of cells B5:B11. All of them are stripped of their fractional part.
Read More: Round to Nearest 5 or 9 in Excel
4. Rounding to Nearest Whole Number Using INT Function
We will use the INT function to convert fractional values to Integer values. The INT function essentially makes the number stripped away from the decimal part and rounds down, permanently.
Steps
- At first, select cell D5 and enter the following code.
=INT(B5)- After entering the code, you will notice that cell D5 now contains the rounded number in cell B5. The number is rounded from 973.5 to 973.
- In this case, the number is rounded down, and in all of the cases, INT Function will round down the number to its nearest integer.
- Now, drag the Fill Handle button in the corner of cell D5 to cell D12.
- After that, you will notice that the range of cells D5 to D10 is now fille with the rounded down numbers from the range of cells B5:B11 to their closest integer number.
Read More: How to Round Numbers to the Nearest Multiple of 5 in Excel
5. Applying MROUND Function for Rounding in Excel
With the MROUND function, we can round values to the multipliers. Hence this method provides better freedom compared to the other methods. The second argument of this function is multiple, to whose multiple we are going to round up the original number.
Steps
- At first, select cell D5 and enter the following code.
=MROUND(B5,1)- After entering the code, you will notice that cell D5 now contains the rounded number in cell B5. The number is now rounded from 973.5 to 973.
- The Multiple here is chosen as 1, so the parent number will be rounded to a multiple of 1 in every case. The numbers will round closer to the nearest integer value in every case.
- Now, drag the Fill Handle icon in the corner of cell D5 to cell D12.
- After that, you will notice that the range of cells D5 to D10 is now filled with the rounded to the closest integer numbers from the range of cells B5:B11 to their closest integer number.
Note:
The Number argument and the Multiple arguments must have the same sign; otherwise, there would be a #NUMÂ error.
Read More: Excel VBA: Round to Nearest 5
6. Inserting FLOOR Function to Round to Nearest Whole Number
Using the FLOOR function, we can round down the number according to the multiplier set in the function.
Steps
- At first, select cell D5 and enter the following code.
=FLOOR(B5,1)- After entering the code, you will notice that cell D5 now contains the rounded-down number in cell B5. The number is rounded from 973.5 to 973.
- The Multiple here is chosen as 1, so the parent number will be rounded down to a multiple of 1 in every case. The numbers will be rounded closer to the nearest integer value in every case.
- Now, drag the Fill Handle button in the corner of cell D5 to cell D12.
- After that, you will notice that the range of cells D5 to D10 is now filled with the rounded down integer numbers from the range of cells B5:B11 to their nearest integer number.
Note:
1. If any of the arguments in the FLOOR function is not numeric, then the FLOOR function will return the #VALUE! error.
2. If the number value is negative, the rounding will happen towards 0. If the number is positive, then the number will round away from 0.
3. Caution must be taken while choosing the significance value. It should always be set to 1 if the intention is to make a number round to an integer. If the Significance number has fractions or
decimal parts, the rounding will not result in an integer number.
4. You can use the FLOOR.MATH function to automatically round the number down to an integer. The significance value is one by default.
Read More: Round Down to Nearest 10 in Excel
7. Using CEILING Function to Round to Nearest Whole Number in Excel
The CEILING function works just like the FLOOR function. But in this case, the CEILINGÂ function will round up the number instead of round down. And the round-up will be done according to the multiplier set in the function.
Steps
- At first, select cell D5 and enter the following code.
=CEILING(B5,1)- After entering the code, you will notice that cell D5 now contains the rounded-down number in cell B5. The number is rounded from 973.5 to 973.
- The significance here is chosen as 1, so the parent number will be rounded down to a multiple of 1 in every case. The numbers will be rounded closer to the nearest integer value in every case.
- After that, you will notice that the range of cells D5 to D10 is now filled with the round-down integer numbers from the range of cells B5:B11 to their nearest whole number in Excel.
Note:
You can use the CEILING.MATH function to round the numbers directly; significance=1 is by default in this function.
Read More: How to Round to Nearest 100 in Excel
8. Applying Decrease Decimal Command to Round in Excel
We can decide the no of decimal of the number to show in the worksheet directly in the ribbon menu. We can even make numbers with decimals reduced to integers, stripping the fractional part from the original number.
Steps
- In the beginning. Copy the data from the range of cells B5:B12 to the range of cells C5:C12.
- Then select the cells and from the ribbon menu, click twice on the Decrease Decimal command from the Number group in the Home tab.
- Then you will notice the numbers in the range of cells C5:C12 is now free from the fractional part.
- Here, you need to press the command two times in this case because some numbers carry two decimal places.
- After clicking the Decrease Decimal, you will notice that the numbers in the range of cells C5:C12 are now free from fractions. And they round up to their nearest whole number in Excel.
Read More: How to Round Numbers to Nearest 10000 in Excel
9. Rounding to Nearest Whole Number Through Built-in Number Format
Using the built-in number option, we can change the decimal value of the number and can truncate the value from the fractional part.
Steps
- In the beginning, copy the cells from the range of cells B5:B12 and paste them to the range of cells C5:C12.
- Then select the range of cells D5:D12, right-click on the mouse and click on the Format Cells.
- After that, a new window will open, and from that window, click on the Number from the Number tab.
- In the Number tab, set the decimal place to 0, as shown in the image.
- Click OK after this.
- After clicking OK, you will notice that the numbers in the range of cells are now rounded up to the nearest whole number.
By this method, you can round a decimal to the nearest whole number in Excel.
Read More: How to Round to Nearest 1000 in Excel
Download Practice Workbook
Download this practice workbook below.
Conclusion
To sum it up, the question “how to round decimals to the nearest whole number” is answered here elaborately in nine separate ways. Using functions like ROUND, TRUNC, MROUND, EVEN and ODD, etc. Additionally, rounded by using the ribbon menu and the number formatting option.
A workbook is available for download to practice these examples for this problem.
Feel free to ask any questions or feedback through the comment section. Any suggestion for the betterment of the Exceldemy community will be highly appreciated.
<< Go Back to Rounding in Excel | Number Format | Learn Excel
Get FREE Advanced Excel Exercises with Solutions!
