Excel formula to find integers and mantissa processing methods

Key points of this article

Displaying it as an integer only changes the appearance. The cell value still contains decimals. Errors will occur when summing and referencing. You need to use formulas to actually round.
ROUND(numeric value, 0) is used for standard rounding, INT is used for rounding down, TRUNC is used for directly truncating decimals, and MROUND is used for specifying multiples.
INT and TRUNC have the same result for positive numbers, but different results for negative numbers: -3 for INT(-2.5) and -2 for TRUNC(-2.5).
If the last digit is set to 0, ROUND(value,-1) can be used; if the last digit is set to 0 or 5, MROUND(value,5) can be used. Note that this will change the value.
Spot-check .5 boundaries, negative numbers, and text numbers after rounding, and check the totals to make sure the results are as expected.

Calculating a series of decimal results in Excel requires integers. This situation is very common: headcount distribution, number of packaging boxes, reimbursement rounding, and price erasure. Many people subconsciously change the cell format to "numeric value, 0 decimal places". The screen indeed changes to an integer, but the value in the cell does not change. When summing or quoting later, it will still involve decimals in the calculation, and the total will not match the number you see. If you want to turn the value itself into an integer, you need to use a formula.

This article talks about several ways to find integers using excel formulas, and how to set the last number to 0 or 5. After reading, you can decide which function to use, write the corresponding formula, and check whether the results really change as expected.

Excel formula finds integers, how to choose ROUND, INT or TRUNC? One sentence conclusion

First, it depends on whether you want to "round up", "all down" or "just remove decimals". ROUND(numeric value, 0) is used for standard rounding, INT is used for rounding down, TRUNC is used for directly truncating decimals, and MROUND is used when a certain multiple is required. The table below gives options for common situations.

your situation	Recommended practices	reason
Round according to normal rules, e.g. 2.5 becomes 3	=ROUND(value,0)	Standard rounding, symmetrical processing of positive and negative numbers
As long as the integer part of the positive number, such as the number of pieces, the number of units	=INT(numeric value) or =TRUNC(numeric value)	Positive numbers have the same result, negative numbers have different results.
No matter how big the decimal is, just remove the decimal	=TRUNC(numeric value)	Decimals are truncated towards zero, and the rules for positive and negative numbers are the same.
The result is required to be a multiple of 5, such as rounding to 5 units	=MROUND(value,5)	Get the nearest specified multiple
The decimal part is carried as long as it exists.	=ROUNDUP(value,0)	carry away from zero

Before you start: Confirm these points first

Before writing the formula, make some judgments about the following items, otherwise the results may be unexpected.

The data are not real numbers. Numbers copied from web pages or other systems, sometimes in text format, appear to be 12.6 and do not actually participate in the calculation. Text numbers are usually left-justified, and formulas may return an error or remain unchanged. Confirm first and then round up.
Are there any negative numbers in the data? INT and TRUNC have the same result for positive numbers, but different results for negative numbers. INT(-3.7) gets -4, TRUNC(-3.7) gets -3. Pay special attention to data such as financial balance, temperature, and profit and loss.
Do you want display or numerical value? Setting the number of decimal places only affects the display and does not change the value. If you need to participate in subsequent calculations or export it to others, you should use formulas to generate new values.
Which digit to round to. The ones digit uses ROUND(value, 0), the tens digit uses ROUND(value, -1), and the multiples of 5 use MROUND(value, 5). The digits parameter determines the precision of the result.
The number that is exactly .5. ROUND's treatment of .5 is a carry away from zero, 2.5 is 3, and -2.5 is -3. The decimals generated during calculations may sometimes deviate slightly from .5 due to floating point errors, and the results may differ from hand calculations.

Steps to round a column of data uniformly in Excel

The following takes the original data in column B and starting from row 2 as an example. The operation steps are applicable to any column of data.

Leave a blank column on the right side of the data as the result column to retain the original data for easy comparison and rollback.
Determine the rule: round, round down, truncate decimals directly, or take multiples.
Enter the corresponding formula in the first row of the result column. For example, if the original data is in B2, enter =ROUND(B2,0).
Press Enter to confirm and see if the result meets your expectations for this line.
Drag the fill handle in the lower right corner of the cell to copy the formula downward, covering the entire column of data.
If you want to replace the original data with the result, first select the result column and copy it, and then paste it back to the original position in the form of "numeric value" to avoid invalidating the formula reference after deleting the original data.
Check a few rows against the original data, focusing on numbers that are exactly .5 and negative numbers.

How to write the formula to set the last number to 0 or 5

Some scenarios do not require ordinary rounding, but require fixed numbers at the end, such as numbers, codes, and quotations rounded up to ten or ending in 5. The following writing methods can be directly applied.

Set the last digit to 0 and round to the nearest ten digits: =ROUND(B2,-1). For example, 137 gets 140, 132 gets 130. Writing -1 for the number of digits means rounding to tens digits.
Set the last digit to 0 and go straight down to the tens digit: =ROUNDDOWN(B2,-1). For example, 137 gets 130, and 139 gets 130.
Set the last digit to 0 or 5, take the nearest multiple of 5: =MROUND(B2,5). For example, 137 gets 135, 138 gets 140. The result must end in 0 or 5.
The last digit is uniformly set to 5, and the tens digit is reserved: =ROUNDDOWN(B2,-1)+5. For example, 137 gets 135, and 132 gets 135. This way of writing will change the size of the value and is suitable for numbers or codes, but not for real amounts.

Note: Setting the last bit to 5 will change the actual size of the data. When used for formal amounts or statistics, first confirm whether such processing complies with business rules, and retain the original data for traceability.

📊 Core comparison

The following table uses two inputs of 2.5 and -2.5 to compare the results of several commonly used functions so that the difference can be seen at a glance.

Function	Enter the result of 2.5	Enter the result of -2.5	Explanation
ROUND(x,0)	3	-3	Standards are rounded, and .5 is far from zero
INT(x)	2	-3	Round down, round in the direction of negative infinity
TRUNC(x)	2	-2	Simply cut off the decimal and move toward zero

The parameters of MROUND require multiples to match the numerical symbols. For example, MROUND(-7,5) will return an error; switching to MROUND(-7,-5) will yield -5.

🎯 Which method suits you?

Invoice amounts and quotation rounding: Usually use ROUND, rounding digits according to the financial or contractual agreement, and confirm the rounding rules with the other party.
Counting data such as number of entries and people that only take integer parts: When all data are positive, INT and TRUNC have the same results, so you can choose whichever you want; If the data contains negative numbers, first determine which direction the negative number should round into.
Make unified numbering, code, and satisfice: Use MROUND or ROUNDDOWN with fixed numbers, and note the rules next to the result column for easier understanding.
Rounding is required instead of rounding: Use ROUNDUP, for example, counting the number of boxes needed by box, and add one box if there is a remainder.

After completion, check each item one by one

After rounding up, check the following points item by item to confirm that the results are correct.

Inspection items	How to see it	What should you do if there's a problem?
Are the results all integers?	For the result column, use =MOD(result cell, 1), returning 0 to represent an integer	If it returns a non-zero, check whether the formula references the correct cell or if padding is missing
Whether the negative number is rounded in the expected direction	Pick out a few lines of negative numbers and count them by hand	If INT and TRUNC do not match the results, switch to a function that matches the rules
It is exactly a number of .5	Find the row with the decimal part .5 and check if the carry matches the convention	If floating-point error is suspected, you can first use ROUND(value, 6) to handle it, then apply the rounding formula
Whether text numbers are recognized	Check if the result returns an error or matches the original data	Convert text numbers into values and then recalculate
Is the total consistent?	Compare the difference between the original data sum and the result sum	Differences from rounding are considered normal; if the difference is abnormal, check for missing rows

❓ Frequently Asked Questions (FAQ)

1. If you set the cell format to zero decimal places, why does summing still have a decimal difference?

Because the display format only changes the appearance, the values in the cells retain decimals. The sum uses the true value, so the total may differ from the displayed integer. Use ROUND or TRUNC to generate new integer values, then sum them up to match the results.

2. ROUND(2.5,0) gets 3. Why is the number .5 sometimes incorrect?

ROUND is fixed for 0.5 away from zero carry. The deviation is mostly because the numbers have a very small floating-point error after calculation, appearing to be 2.5 but actually being 2.49999999. First, use ROUND(value, 6) to remove the error, then round the result; usually, you get the expected value.

3. Can INT replace TRUNC?

If all data are positive, it can be done with the same result. As long as the data contains negative numbers, the two are different: INT rounds toward negative infinity, TRUNC truncates the decimal toward zero. Before choosing, confirm which rules you need.

4. MROUND error #NUM!, what should I do?

MROUND requires the value to match the multiple symbol. For example, taking a multiple of -7 by 5 will cause an error; just change the multiple to -5. If the data is mixed with positive and negative, it is recommended to split it into positive and negative columns for separate processing, then merge the results and mark the rules used in the table header.

📝 Summary

In Excel, to truly turn values into integers, you rely on formulas like ROUND, INT, TRUNC, or MROUND, which simply can't be changed in display format. When selecting a function, first check whether the rule is rounded, rounded down, or rounded out, then use a checklist to check negative numbers, .5, and sum. By mastering these points, you can use Excel formulas for integer and masseter settings stably in daily spreadsheets.

Open OmniDoc and process your table file