Master the INT Function: Complete Guide to Rounding Down Numbers in Excel
=INT(number)The INT function is one of Excel's most fundamental mathematical tools, designed to round numbers down to the nearest integer by removing all decimal places. Whether you're working with financial data, statistical analysis, or simple data cleanup, the INT function provides a straightforward way to convert decimal values into whole numbers. This function is particularly valuable in business scenarios where you need consistent integer values for reporting, inventory calculations, or data standardization. Unlike the ROUND function which considers decimal values to determine rounding direction, INT always rounds downward regardless of the decimal portion. This predictable behavior makes it essential for calculations where you need floor values—such as determining how many complete units can be produced from available materials, or calculating full years of service from date calculations. Available across all Excel versions from 2007 through 365, the INT function remains a cornerstone of Excel's mathematical capabilities.
Syntax & Parameters
The INT function uses a simple syntax structure: =INT(number), where the number parameter represents the value you want to truncate to an integer. This required parameter can be a direct numeric value, a cell reference, a formula result, or any expression that evaluates to a number. The function works by removing all decimal places and returning only the integer portion, effectively rounding down to the nearest whole number on the number line. For positive numbers, INT removes decimals straightforwardly: INT(5.9) returns 5, and INT(10.1) returns 10. However, for negative numbers, INT rounds toward the lower value on the number line: INT(-5.2) returns -6, not -5. This distinction is crucial when working with datasets containing negative values. The function accepts values from approximately -2.147 billion to 2.147 billion within standard Excel limitations. You can nest INT within other formulas for complex calculations, combine it with cell ranges, or use it with calculated expressions to create powerful data processing workflows.
numberPractical Examples
Calculating Complete Production Units
=INT(47.8/5)The formula divides total material by material per unit, then uses INT to remove the decimal portion, showing only complete units that can be produced without partial items.
Converting Working Hours to Complete Days
=INT(382.5/8)Dividing total hours by hours per day and applying INT gives the complete workdays without considering partial day hours, useful for standardized payroll calculations.
Extracting Years from Age Calculation
=INT(A2)Applied to a cell containing 23.7, INT simply removes the decimal portion to provide the complete year value needed for age-based analysis and reporting.
Key Takeaways
- INT removes all decimal places and rounds down to the nearest integer, with special behavior for negative numbers (rounds toward negative infinity, not zero).
- Unlike ROUND or ROUNDDOWN, INT requires no decimal place parameter and provides the simplest syntax for converting any number to an integer value.
- INT works seamlessly across Excel 2007 through 365, Google Sheets, and LibreOffice, making it universally applicable for spreadsheet work across platforms.
- Combine INT with other functions like MOD, DATEDIF, or SUMPRODUCT to solve complex business problems from inventory calculations to age determinations.
- For negative numbers requiring truncation toward zero rather than floor behavior, use TRUNC function instead of INT to avoid unexpected results.
Pro Tips
Use INT(A1/B1)*B1 to round down to the nearest multiple of any number. For example, INT(47/10)*10 rounds 47 down to 40, useful for rounding to nearest 10, 100, or custom increments.
Impact : Eliminates need for separate FLOOR function in many scenarios; creates more flexible rounding logic for custom business rules.
Combine INT with IF to create conditional integer conversion: =IF(ISNUMBER(A1),INT(A1),A1). This safely handles mixed data types without #VALUE! errors.
Impact : Prevents formula failures when datasets contain text mixed with numbers; improves data robustness and reduces troubleshooting time.
Remember INT rounds toward negative infinity, not toward zero. For consistent truncation behavior with negative numbers, use TRUNC or =INT(ABS(A1))*SIGN(A1).
Impact : Prevents unexpected results in financial calculations and statistical analysis involving negative values; ensures formula accuracy across all data ranges.
In Excel 365, use INT with BYROW or BYCOL for dynamic array processing: =BYROW(A1:A100, LAMBDA(x,INT(x))). This processes entire columns without dragging formulas down.
Impact : Dramatically improves efficiency with large datasets; reduces manual formula management and enables real-time recalculation as data changes.
Useful Combinations
INT with MOD for Extracting Decimal Portion
=A1-INT(A1)Subtracting INT result from original value isolates the decimal portion. For 23.7, this returns 0.7. Useful for separating whole numbers from fractional parts in data processing workflows.
INT with DATEDIF for Complete Years of Service
=INT(DATEDIF(B2,TODAY(),"D")/365.25)Combines date calculations with INT to convert total days between dates into complete years, accounting for leap years. Ideal for employee tenure calculations and age determinations.
INT with SUMPRODUCT for Conditional Aggregation
=SUMPRODUCT(INT(A1:A10/5))Processes entire ranges by dividing each value by 5, truncating decimals, then summing results. Enables efficient batch calculations on large datasets without helper columns.
Common Errors
Cause: The parameter passed to INT contains text, empty cells, or non-numeric values that Excel cannot interpret as numbers.
Solution: Verify the cell reference contains actual numeric data. Use =INT(VALUE(A1)) to convert text numbers, or check for leading/trailing spaces using TRIM function: =INT(VALUE(TRIM(A1)))
Cause: The function name is misspelled as 'INT' with incorrect syntax, or the formula uses regional language syntax in non-English Excel versions without proper localization.
Solution: Ensure correct spelling of INT. In non-English Excel versions, use the localized function name (e.g., 'ENTIER' in French). Verify your Excel language settings match your formula syntax.
Cause: The formula references a cell that has been deleted or moved, breaking the reference path used by INT.
Solution: Verify all cell references in your INT formula still exist and contain valid data. Use Find & Replace to locate broken references, or rebuild the formula with current cell locations.
Troubleshooting Checklist
- 1.Verify the input cell contains numeric data, not text that looks like numbers. Use =ISNUMBER(A1) to confirm data type.
- 2.Check for unexpected negative numbers that may produce different results than expected due to INT's floor behavior toward negative infinity.
- 3.Ensure cell references haven't been deleted or moved, which would cause #REF! errors. Use Find & Replace to locate broken links.
- 4.Confirm Excel version compatibility—INT works in all versions 2007 and later, but array functionality differs in Excel 365.
- 5.Test formula with sample data to verify results match business logic. Use a helper column to validate INT output before applying to large datasets.
- 6.Check for circular references if INT references its own cell or cells that reference back to the INT formula, which Excel will flag as an error.
Edge Cases
Processing zero: =INT(0)
Behavior: Returns 0 without error. INT handles zero normally as an integer value.
Zero is a valid input and produces expected result; no special handling required.
Very small decimal values: =INT(0.0001)
Behavior: Returns 0. Any positive decimal less than 1 becomes 0; any negative decimal between -1 and 0 becomes -1.
Solution: Use TRUNC if you need to preserve sign direction for values between -1 and 0.
This behavior can cause data loss in precise calculations; consider whether INT or TRUNC better serves your use case.
Scientific notation input: =INT(1.5E+3)
Behavior: Excel interprets 1.5E+3 as 1500 and returns 1500. INT processes scientific notation normally.
Scientific notation is handled transparently; no special formatting required for INT to process these values correctly.
Limitations
- •INT cannot process text values directly—it returns #VALUE! error if given text input. You must convert text to numbers using VALUE() function first: =INT(VALUE(A1))
- •INT always rounds down toward negative infinity, which may not match user expectations for negative numbers. For truncation toward zero, TRUNC function is more appropriate and intuitive.
- •INT operates on single values or cell-by-cell basis in older Excel versions; Excel 365 dynamic arrays improve this, but complex array operations may require SUMPRODUCT or helper columns in older versions.
- •INT precision is limited to Excel's standard numeric range (approximately ±2.147 billion). Numbers beyond this range may lose precision or display in scientific notation, affecting calculation accuracy.
Alternatives
Compatibility
✓ Excel
Since 2007
=INT(number) - Fully supported in all versions including Excel 2007, 2010, 2013, 2016, 2019, and 365 with identical behavior across versions.✓Google Sheets
=INT(number) - Identical syntax and behavior to Excel. Google Sheets treats negative numbers the same way (floor rounding).Works perfectly in Google Sheets with no compatibility issues. Results are consistent between Excel and Google Sheets for equivalent data.
✓LibreOffice
=INT(number) - Fully supported in LibreOffice Calc with identical functionality to Excel versions.