INT Function in Excel
The INT function rounds numbers down to the nearest integer by removing decimals. Learn syntax, practical examples, and error solutions.
Excel
Google SheetsExcelGoogle Sheets=INT(number)Quick Answer
INT function INT function is a mathematical function in Excel and Google Sheets that rounds numbers down to the nearest integer by removing the decimal portion. Unlike other rounding functions, INT always rounds toward negative infinity, making it ideal for calculations requiring whole numbers without any fractional components.
Practical INT Examples
Basic Number Rounding
Round positive and negative numbers to integers
Calculate Age from Birthdate
Determine someone's age in complete years
Inventory Quantity Calculation
Calculate how many complete products can be made from materials
Create Price Tiers
Group prices into integer-based pricing tiers
Calculate Payment Periods
Determine number of complete payment periods from months
Time Conversion
Convert decimal hours to complete hours
Data Grouping for Analysis
Create integer-based age groups for demographic analysis
Common INT Errors and Solutions
INT returns #VALUE! error
The input is not a numeric value. This occurs when you try to use INT with text, dates formatted as text, or cell references containing non-numeric data.
1. Check that your input cell contains a number, not text 2. Use VALUE() to convert text numbers: =INT(VALUE(A2)) 3. Verify dates are actual date values, not text strings 4. Use ISNUMBER() to test: =IF(ISNUMBER(A2), INT(A2), "Invalid")
Always validate input data types before applying INT. Use data validation to restrict cell entries to numbers only, or add error checking with ISNUMBER or IFERROR.
Example:
Excel shows #NAME? error
The function name is misspelled or not recognized. Common typos include 'INTEGER', 'INTEG', or 'IN'.
1. Check spelling - it must be exactly 'INT' in uppercase or lowercase 2. Verify there are no extra spaces: ' INT' or 'INT ' 3. Ensure you're using the = sign: =INT(A2) 4. Confirm you're not in a region that uses different function names
Use Excel's formula autocomplete feature by typing '=in' and selecting INT from the dropdown list. This ensures correct spelling and syntax.
Example:
INT rounds negative numbers in unexpected direction
Users expect INT to work like TRUNC, simply removing decimals. However, INT always rounds toward negative infinity, so INT(-5.5) returns -6, not -5.
1. If you want to remove decimals without rounding down negatives, use TRUNC: =TRUNC(-5.5) returns -5 2. If you want to round toward zero, use: =IF(A2<0, ROUNDUP(A2,0), INT(A2)) 3. For always rounding down (INT's behavior), understand this is correct functionality
Choose the right function for your needs: INT rounds down toward negative infinity, TRUNC removes decimals toward zero, ROUNDDOWN rounds away from zero. Document which function you use and why in complex formulas.
Example:
INT returns #NUM! error
The number is too large for Excel to handle. Excel can only process numbers between approximately -1E+308 and 1E+308.
1. Check if your calculation creates extremely large numbers 2. Break down complex formulas into smaller steps 3. Use scientific notation if needed 4. Consider if you need such large numbers - there may be a formula error
Review your data sources and calculations. Extremely large numbers often indicate multiplication or exponentiation errors. Add boundary checks to flag unrealistic values.
Example:
Circular reference detected when using INT in self-referencing formula
The formula refers back to its own cell, creating an infinite loop. This can happen when INT is part of a formula that updates itself.
1. Identify the circular reference using Excel's circular reference toolbar 2. Restructure your formula to reference a different cell 3. Break the calculation into multiple steps across different cells 4. Enable iterative calculations if intentional (rare cases only)
Carefully design your formula logic to avoid self-references. Use helper columns for intermediate calculations rather than trying to do everything in one cell.
Example:
Best Practices and Pro Tips
Combine with MOD for Complete Division
When dividing quantities, use INT for the quotient and MOD for the remainder. For example, if you have 23 items in boxes of 5, INT(23/5) gives you 4 complete boxes, and MOD(23,5) tells you there are 3 items left over.
INT vs TRUNC: Know the Difference
INT and TRUNC appear similar but behave differently with negative numbers. INT(-5.8) returns -6 (rounds down toward negative infinity), while TRUNC(-5.8) returns -5 (cuts off decimal toward zero). Choose INT when you need consistent downward rounding, TRUNC when you want to remove decimals regardless of sign.
Performance Optimization for Large Datasets
INT is one of Excel's fastest functions because it performs a simple mathematical operation. For large datasets (50,000+ rows), INT outperforms more complex rounding functions. However, avoid nesting INT within multiple complex formulas - break calculations into helper columns for better performance and easier troubleshooting.
Date Calculations Pitfall
When using INT with dates, remember that Excel stores dates as numbers (days since January 1, 1900). INT(TODAY()) gives you today's date serial number, which might not be what you want. For date manipulation, use date-specific functions like YEAR, MONTH, and DAY instead.
Error Handling with IFERROR
Always wrap INT in IFERROR when working with user input or external data. This prevents #VALUE! errors from breaking your spreadsheet and provides user-friendly error messages. A formula like =IFERROR(INT(A2), "Please enter a valid number") makes your spreadsheets more robust and professional.
Create Custom Rounding Intervals
Use INT with division and multiplication to round to any interval. For example, to round down to the nearest 5: =INT(A2/5)*5. This works for any interval: nearest 25, 100, or 0.5. This technique is invaluable for pricing tiers, inventory management, and data grouping.
Platform Compatibility
INT works identically in Excel (all versions), Google Sheets, LibreOffice Calc, and most other spreadsheet applications. This makes it excellent for shared workbooks or templates that need to work across platforms. Unlike some newer functions, INT has no compatibility concerns.
Combining INT with Array Formulas
In Excel 365 and Google Sheets, INT works with dynamic arrays. You can apply INT to an entire range at once: =INT(A2:A100) automatically applies INT to all values. This eliminates the need to copy formulas down and improves calculation efficiency.
Need Help with INT Function in Excel?
Stop struggling with formula syntax. Use AskFormulas to generate validated formulas instantly with our AI-powered tool.
Example Excel formula:
Related Formulas
The MOD function returns the remainder from division. Perfect for determining even/odd numbers, creating patterns, and cycling sequences.
ExcelSheetsMaster the ROUND function to round numbers to specified decimal places with practical examples and error solutions for Excel and Google Sheets.
ExcelSheetsMaster ROUNDDOWN to truncate numbers to specified decimal places. Learn syntax, examples, and error solutions for Excel and Google Sheets.
ExcelSheetsMaster the ROUNDUP function to always round numbers up in Excel and Google Sheets. Learn syntax, examples, and avoid common errors with our comprehensive guide.
ExcelSheets