OmniDocHow to write Excel rounding formula: usage of ROUND and INT functions
Organizes the common ways of writing rounding formulas in Excel tables, explains the differences between ROUND, INT, ROUNDUP, ROUNDDOWN and other functions, and provides judgment methods and verification steps to help you choose the right rounding method as needed.
Key points of this article
- Setting the number of decimal places in a cell only changes the display, not the value. To truly round, you must use a formula.
- ROUND rounds to an integer, INT rounds down to an integer not greater than the original number, and the two have different results for negative numbers.
- Use ROUNDUP if you want to advance without going back, ROUNDDOWN or TRUNC if you want to round without rounding, and MROUND if you need to round to a fixed unit.
- After writing the formula, check the positive numbers, negative numbers, and values that are exactly .5 against the original data.
When many people deal with amounts, quantities or achievements in Excel spreadsheets, they subconsciously change the decimal places in cells to 0. The numbers on the screen have indeed become neater, but the original decimals involved in summation, comparison and citation are still the same. The result is that the total does not match what is seen in the table, and it is easy to make mistakes during financial reconciliation or performance statistics. The root of this type of problem is that the display format and the value itself are two different things.
This article is written for readers who need to perform rounding operations in Excel tables, such as sorting out reimbursement forms, calculating working hours, and counting inventory. After reading, you should be able to distinguish the rules of ROUND, INT, ROUNDUP, ROUNDDOWN, TRUNC and MROUND, know which one to use under what circumstances, and be able to use a set of examples to check whether the results are correct.
Should I use a formula for rounding? One sentence conclusion
As long as the results need to be involved in calculations or handed over to others for verification, the formula should be used to actually round the results, rather than just changing the display format. The table below can help you choose quickly.
| your situation | Recommended practices | reason |
|---|---|---|
| Round off according to normal rules, if the amount is kept in whole yuan | ROUND(value, 0) | In line with daily calculation habits, when .5, carry in the direction of the larger absolute value. |
| As long as the integer part, regardless of positive or negative, take it down | INT (numeric value) | The result is a large integer that is no larger than the original number. Negative numbers will be taken in the smaller direction. |
| Just go forward and don’t go back, such as the number of express delivery items and the number of material boxes | ROUNDUP(value, 0) | No matter how big the decimal is, it is rounded in the direction of increasing absolute value. |
| Only rounding is not performed, such as truncating the decimals to look at the integer part | ROUNDDOWN(numeric value, 0) or TRUNC(numeric value) | Directly truncate decimals, positive numbers are consistent with INT results, and negative numbers are taken in the direction of zero. |
| Rounded according to fixed unit, such as per bag, per box | MROUND(value, unit) | The result is an integer multiple of the unit, close to the original number |
Before you start: Confirm these points first
Confirm the purpose of rounding first. Reconciliation, invoicing, and billing often require rounding, while inventory, packaging, and scheduling often require forwards and no retreats. Different purposes require different functions. If you choose the wrong function, the calculated number will be systematically larger or smaller.
Confirm whether there are any negative numbers in the data. The results of INT on negative numbers can be surprising. For example, INT(-2.3) returns -3, not -2. If your data contains refunds, losses, or reverse adjustments, you should use ROUND or TRUNC instead of the default of INT.
Confirm if you want to keep the original data. The rounding formula will generate a new result, with the original number still in the source cell. It is recommended to put the results in new columns and keep the original columns for easy verification later.
Confirm the rounding number. The number of digits in the parameter determines which digit is accurate. ROUND (value, 2) retains two decimal places, ROUND (value, 0) is rounded to the ones digit, and ROUND (value, -1) is rounded to the tens digit. If the number of digits is written incorrectly, the result will be incorrect.
Confirm calculation chain. If multiple formulas are connected one after another, such as rounding first and then summing, the rounding errors in the middle will accumulate. You should generally keep the exact value first and round as a final step, unless the business rules explicitly require rounding down.
Operation steps: Use formulas to round and check the results in OmniDoc
If you don't want to look for another tool outside of native Excel, you can use the UniCell table in the OmniDoc desktop client to write and check rounding formulas. The following is an example of a reimbursement detail. The amount column needs to be kept in whole yuan.
- Visit download.omnidoc.top, download and install the desktop client for your system.
- Open the client, use UniCell table to open the existing data file, or create a new worksheet and enter sample data.
- In the blank column next to the data, select the first cell that needs to be rounded and enter the corresponding formula, such as =ROUND(B2, 0).
- Press Enter to confirm and check whether the value displayed in the cell is as expected.
- Copy the formula down the entire column, covering all rows of data. Pay attention to check whether the reference changes from row to row to avoid all rows pointing to the same cell.
- Use the summation formula in another blank space to summarize the rounded column, and compare it with the total of the original column to see if the difference is within the acceptable range.
- If you need to give the results to others for review, you can copy the table contents to a PDF or Word file, and then use OmniDoc's PDF editing or export function to organize the layout.
- Save the file and retain the original data columns for future traceability.
If your content is a table in a Word or PDF document, you can also sort the numbers into the table tool for calculation, and then write the results back to the document.
Check each item after completion
After the formula is written and copied down, do not trust the results directly. The following items can help you spot common mistakes.
| Check items | How to see | What to do if there is a problem |
|---|---|---|
| Positive example | Select a few lines of positive numbers and calculate them manually. For example, 2.4 should be rounded to 2, and 2.5 should be rounded to 3. | If the results do not match, check whether the function name and number of parameters are written correctly. |
| Negative number example | Select a few rows of negative numbers and compare the results of ROUND and INT | If you need to round to an absolute value, use ROUND instead of INT |
| A value that is exactly .5 | Find values like 2.5, -2.5 and observe the carry direction | When inconsistent with business rules, use ROUNDUP, ROUNDDOWN or TRUNC instead |
| formula reference | Click on a few cells at random to see if the referenced rows are correct. | Correct relative and absolute references and copy the formula again |
| Total comparison | Compare the rounded total to the original total | When the difference is too large, check whether rounding is repeated in the intermediate steps |
| Display and numerical values | View the actual contents of the cell in the edit bar | If the display is correct but the numerical value is incorrect, it means that only the format has been changed and needs to be replaced with a formula. |
For table data recognized by machine translation or OCR, the source of the numbers must also be checked one by one to avoid bringing recognition errors into the calculation results.
📊 Core comparison: How to distinguish between ROUND, INT, TRUNC, ROUNDUP and other functions
The core differences between several rounding functions lie in two points: which direction to take, and how to deal with negative numbers.
| function | Rounding rules | Typical uses |
|---|---|---|
| ROUND | Round by digits, moving away from zero when .5 is encountered | Regular rounding of amounts, scores, etc. |
| INT | Get down to an integer no larger than the original number, negative numbers will get smaller | Scenarios where only the integer part is required and the data is negative |
| TRUNC | Directly truncate the part after the specified number of digits and take it in the direction of zero. | Data that needs to be truncated rather than carried |
| ROUNDDOWN | Take it in the direction of zero, you can specify the number of digits | Only calculations without rounding and variable digits |
| ROUNDUP | Take it in the direction away from zero. If there are decimals, carry to the side with the larger absolute value. | The quantity can only advance but not retreat, such as the number of boxes and pieces |
| MROUND | Get an integer multiple of the specified unit, and the result is close to the original number | Pricing based on fixed unit or piece rate |
In the case of positive numbers, the results of INT and TRUNC are the same; in the case of negative numbers, there will be differences, which should be especially noted in finance and statistics.
🎯 Which method is suitable for you?
- Readers who do financial accounting or performance statistics: usually use ROUND and indicate the rounding number in the report to ensure that it is consistent with others.
- Readers who do inventory, materials or scheduling: ROUNDUP is commonly used to ensure that the quantity will not be short due to rounding.
- Handling readers containing refund or reverse data: Direct use of INT should be avoided, favor ROUND or TRUNC, and check negative rows specifically.
- Readers who need to price or piece-rate based on fixed units: MROUND is more direct and allows the result to fall on the specified unit.
❓ Frequently Asked Questions (FAQ)
1. Set the cell format to integer, why does the total still not match?
The cell format only affects the display, and the original decimals involved in the sum are still used. If you want the total to be consistent with the display, you need to use functions such as ROUND to generate rounded values and then summarize them.
2. Are the results of ROUND(2.5, 0) and ROUND(-2.5, 0) the same?
no the same. ROUND treats .5 away from zero, so the former gets 3 and the latter gets -3. If your business requires that negative numbers be rounded as well, ROUND fits the bill.
3. How to choose between INT and TRUNC in excel table rounding formula?
When the number is positive, the two results are the same, and the decimal part is removed. If there are negative numbers in the data, INT will be taken in the direction of smaller values, and TRUNC will be truncated in the direction of zero. When you need to maintain intuitive truncation results, it is safer to choose TRUNC.
4. If I want to round up by fixed units (such as each bag, each box), is there a direct method?
You can use the MROUND function, passing the unit as the second argument. For example, to round up to 2 pieces per bag, you can write MROUND(value, 2), and the result will fall on an integer multiple of 2, which is suitable for fixed packaging or piece counting rules.
5. The result calculated by the formula is different from that of the calculator. What is the problem?
First check whether the value of the cell is the value you think it is. You can check it in the edit bar. Secondly, confirm whether the rounding number and function name are correct. If multi-step calculations are involved, it also depends on whether there is rounding in advance.
📝 Summary
The key to rounding formulas in Excel tables is to first think clearly about the purpose of rounding and then choose the right function. ROUND is suitable for regular rounding, INT is suitable for rounding down but be aware of negative numbers, ROUNDUP and ROUNDDOWN handle only rounding and rounding respectively, and MROUND is used for fixed units. Once you've written your formula, you can avoid most mistakes by checking it with examples of positive numbers, negative numbers, and endings in .5.
